oracle_procedure_function_trigger

存储过程 带名字的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 = 888 where current of c;

       end if;

  end loop;

  commit;

end;

编译出错时，show error; 可显示出错信息

执行过程：exec p; 或者 begin p;end;

删除过程：drop procedure p;

--带参数的存储过程,参数默认是输入参数

create or replace procedure p

(v_a in number, v_bnumber, 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;

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;

 

函数

create or replace function sal_tax

  (v_sal number)

  return number

is

begin

  if(v_sal < 2000) then

       return 0.10;

  elsif(v_sal < 3000) then

       return 0.15;

  else

    return 0.20;

  end if;

end;

select sal_tax(sal)from emp;

删除函数 drop function sal_tax;

 

触发器 trigger 依附于某张表上执行

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;

update emp2 set sal =2000 where deptno = 20;

select * fromemp2_log;

删除触发器 drop trigger trig;

触发器副作用

执行update dept2set deptno = 11 where deptno = 10;时会违反外键约束

create or replace trigger trig

  after insert or delete or update on emp2 for each row

begin

  update emp2 set deptno = :NEW.deptno wheredeptno = :OLD.deptno;

end;

建立触发器后再执行update dept2 set deptno = 11 where deptno = 10;不会出错,因为更新时先执行触发器，再检查约束条件。