PL/SQL知识点总结

一、基本语法

1.变量的声明和赋值

1）单个或多个变量用分号；隔开。赋值使用select ...into...语句，需要与表中选取的数据一一对应。

2）使用record记录类型，定义多个变量，中间用逗号，隔开。变量名定义建议带_record标记下。注意分号的位置。

3）可以使用%type动态获取变量的类型

v_name  employees.last_name%type;   

4)使用%rowtype声明一个记录类型的变量

declare
--声明一个记录类型的变量
  v_emp_record employees%rowtype;
begin
  --通过 select ... into ... 语句为变量赋值
 select * into v_emp_record
 from employees
 where employee_id = 186;
 
 -- 打印变量的值
 dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||  
                                        v_emp_record.salary || ', ' ||  v_emp_record.job_id  || ', ' ||  
                                        v_emp_record.hire_date);
end;

5）通过变量实现insert,delete,update等操作

declare
  v_emp_id employees.employee_id%type;

begin
  v_emp_id := 109;
  delete from employees
  where employee_id = v_emp_id;
  --commit;
end; 

二、流程控制

1）条件判断（分支）

①if...then...elsif...then  ... end if;

②case...when...then...else...end;

declare
   v_sal employees.salary%type;

--定义变量接收case then语句的返回值
   v_temp varchar2(20);
begin
       select salary into v_sal from employees where employee_id = 150;
v_temp := 
       case trunc(v_sal/5000) when 2 then  'salary >= 10000'
                              when 1 then '5000<= salary < 10000' 
                              else 'salary < 5000' end;
dbms_output.put_line(v_temp);
end;

2）循环

①loop  循环体  迭代条件 exit when  循环条件   end loop;

②while  循环条件  loop  循环体 迭代条件  end loop;

③for 变量 in   循环范围  loop  循环体  end loop;

  --1-100的打印

for i in 1..100 loop
dbms_output.put_line(i);

end loop;
end;

--1-100的质数

declare
 v_i number(10):= 2;
 v_j number(10):= 2;
 v_flag number := 1;
begin
 while v_i <= 100 loop
      while v_j <= sqrt(v_i) loop
            if (mod(v_i,v_j) = 0) then
               v_flag := 0;
            end if;
            v_j := v_j + 1;
            end loop;
       if (v_flag = 1) then dbms_output.put_line(v_i);
       end if;
       v_flag := 1;
       v_j := 2;
       v_i := v_i + 1;
 end loop;
end;

declare 
v_flag number(1):= 1;
begin
for i in 2..100 loop
    for j in 2..sqrt(i) loop
        if(mod(i,j) = 0) then v_flag := 0;
        end if;
    end loop;
    if(v_flag = 1) then dbms_output.put_line(i);
    end if;
    v_flag := 1;
end loop;
end;

也可以使用到 goto ... label 语句 


	<<label>.
	....

三、游标的使用cursor
输出多条语句需要使用游标。
1）for循环
declare
cursor sal_cursor is select salary from employees where department_id = 80;
begin
for i in sal_cursor loop  	//这里的i 是个record  存放多个变量
dbms_output.put_line('salary '||i.salary);
end loop;
end;
2）while循环

声明游标

cursor .. is select..from...
打开游标
open 
获取游标
fetch..into 某个变量
循环遍历%found
while()
四、异常的处理exception
1）预定义异常
2）非预定义异常
3）自定义异常



declare
  --自定义异常                                   
  no_result exception;   
begin
  update employees set salary = salary + 100 where employee_id = 1001;

  --使用隐式游标, 抛出自定义异常 raise
  if sql%notfound then
     raise no_result;
  end if;  

exception

  --处理程序抛出的异常
  when no_result then
     dbms_output.put_line('更新失败');
end;




五、存储函数function与存储过程procedure
1)存储函数：有返回值return 使用select from dual语句可查询
2）存储过程
六、触发器trigger

create  trigger...
after/before ...update/delete/insert操作等...
for each row  行级触发，不写为表级触发
create table my_emp as select employee_id id, last_name name, salary sal from employees
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2

create or replace trigger bak_emp_trigger
       before delete on my_emp
       for each row
       
begin
       insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;