PL/SQL基础学习笔记(2)-游标、存储过程、函数、触发器

–1、游标
–cursor游标,游标是指向一个select语句结果集的指针

declare
  cursor c
  is select * from emp;
  v_emp c%rowtype;
begin
  open c;
  fetch c into v_emp;
    dbms_output.put_line(v_emp.ename);
  close c;
end;
/


说明:
1)首先声明游标指向一个结果集。
2)定义变量v_emp是游标所指向的结果集的记录类型。
3)open c,打开游标,只有打开游标后,PL/SQL才会执行声明游标的select语句。
4)fatch c into v_emp,把当前游标指向的这条记录放在v_emp变量里面。
fatch执行后游标自动向下移动一行记录。指向下一条记录。
5)用完游标后要close。

–用循环遍历游标(do while循环)

declare
  cursor c
  is select * from emp;
  v_emp c%rowtype;
begin
  open c;
  loop
    fetch c into v_emp;
    exit when (c%notfound);
    dbms_output.put_line(v_emp.ename);
  end loop;
  close c;
end;
/


–游标的属性
%isopen:游标是否打开,当游标打开时返回true,游标关闭时则为false。
%notfound:最近的一次fetch语句没有返回一条记录,返回true,找到记录返回false。
%found:找到记录了返回true,没有找到记录返回false。
%rowcount:当前已经fetch到了多少条记录。

–while循环

declare
  cursor c
  is select * from emp;
  v_emp c%rowtype;
begin
  open c;
  fetch c into v_emp;
  while (c%found) loop
    dbms_output.put_line(v_emp.ename);
    fetch c into v_emp;
  end loop;
  close c;
end;
/


–for循环

declare
  cursor c
  is select * from emp;
begin
  for v_emp in c loop
    dbms_output.put_line(v_emp.ename);
  end loop;
end;
/


说明:
1)for循环里,cursor不需要打开,for循环自动帮你打开。for循环结束时会自动关闭cursor。
2)for循环自动帮你fetch到v_emp中。
3)for循环和游标搭配,简单而且不容易出错。

–带参数的游标

declare
  cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
  is
  select ename, sal from emp where deptno = v_deptno and job = v_job;
  --v_temp c%rowtype;
begin
  for v_temp in c(30, 'CLERK') loop
    dbms_output.put_line(v_temp.ename);
  end loop;
end;
/


说明:
1)带参数游标使用起来,类似于c语言调用函数。

–可更新游标

declare
  cursor c
  is select * from emp2 for update;
  --v_temp c%rowtype;
begin
  for v_temp in c loop
    if (v_temp.sal < 2000) then
      update emp2 set sal = sal * 2 where current of c;
    elsif (v_temp.sal = 5000) then
      delete from emp2 where current of c;
    end if;
  end loop;
  commit;
end;
/


说明:
1)游标最大的作用是用来遍历结果集,用游标来做更改用的不是特别多。

–2、Store Procedure存储过程
–存储过程就是带有名字的PL/SQL的程序块

create or replace procedure p
is
  cursor c is
    select * from emp2 for update;
begin
  for v_emp in c loop
    if (v_emp.deptno = 10) then
      update emp2 set sal = sal + 10 where current of c;
    elsif (v_emp.deptno = 20) then
      update emp2 set sal = sal + 20 where current of c;
    else
      update emp2 set sal = sal  + 50 where current of c;
    end if;
  end loop;
  commit;
end;
/


说明:
1)create or replace如果有覆盖,如果没有创建。
2)上面的程序除了用create or replace procedure p is替代了declare以外,其它的和剩下的PL/SQL程序一模一样。

–启动存储过程的两种方法:
–方法一

exec p;

–方法二

begin
  p;
end;
/


–查看结果

select sal from emp2;

–带参数的存储过程
–先创建一个过程

create or replace procedure p
  (v_a in number, v_b number, v_ret out number, v_temp in out number)
is

begin
  if (v_a > v_b) then
    v_ret := v_a;
  else
    v_ret := v_b;
  end if;
  v_temp := v_temp + 1;
end;
/


说明:
1)在存储过程里面,参数可以分为不同的类型。
2)in表示传入参数,out表示传出参数。
3)存储过程是没有返回值的,所以借用传出参数作为返回值。
4)如果一个参数没写in、out,默认是in。
5)创建存储过程有语法错误,这个过程依然会被创建
用show error命令显示错误。

–调用过程

declare
  v_a number := 3;
  v_b number := 4;
  v_ret number;
  v_temp number :=5;
begin
  p(v_a, v_b, v_ret, v_temp);
  dbms_output.put_line(v_ret);
  dbms_output.put_line(v_temp);
end;
/


–3、函数
–创建函数

create or replace function sal_tax
  (v_sal number)
  return number
is
begin
  if (v_sal < 2000) then
    return 0.10;
  elsif (v_sal < 2750) then
    return 0.15;
  else
    return 0.20;
  end if;
end;
/


说明:
1)v_sal number是参数。
2)return number表示该函数返回值是number类型。

–调用函数
–函数在PL/SQL语句中是做为表达式,在SQL语句中调用,如果是要在PL/SQL语句块调用,要写成存储过程

select sal_tax(sal) from emp;

–4、触发器
–由一个事件触发后执行的一段程序
–触发器不能直接执行,它要依附在某张表上面

–创建一个记录emp2这个表的log的表

create table emp2_log
(
  uname varchar2(20),
  action varchar2(10),
  atime date
);


–创建触发器

create or replace trigger trig
  after insert or delete or update on emp2 for each row
begin
  if inserting then
    insert into emp2_log values(USER, 'insert', sysdate);
  elsif updating then
    insert into emp2_log values(USER, 'update', sysdate);
  elsif deleting then
    insert into emp2_log values(USER, 'delete', sysdate);
  end if;
end;
/


说明:
1)动作有after、before
2)for each row表示每影响1行,触发器执行一次
3)USER是一个关键字,代表当前用户

update emp2 set sal = sal * 2 where deptno = 30;

select * from emp2_log;

–触发器的副作用

update dept set deptno = 99 where deptno = 10;

执行上面这句,oracle会提示违反完整约束条件,因为deptno是emp表的外键。

drop trigger trig;

create or replace trigger trig
  after update on dept
  for each row
begin
  update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
/


说明:
1)一条update语句会有两个状态,更改前的旧状态,更改后的新状态
2):NEW表示新状态,:OLD表示旧状态
3)执行update语句,先触发触发器,再检查完整性约束

update dept set deptno = 99 where deptno = 10;

–提示已更新一行

select deptno from emp;

–deptno原来是10的也更新为99了

rollback;

《PL/SQL基础学习笔记(2)-游标、存储过程、函数、触发器》上的一个想法

评论已关闭。