……………………………………………………………精品资料推荐…………………………………………………

end;

可更新的游标

存储过程

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;

exec p执行存储过程

begin p: end;

带参数的存储过程

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;

21

……………………………………………………………精品资料推荐…………………………………………………

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;

show error返回错误信息

删除存储过程

存储过程中的函数

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.5; else

return 0.20; end if; end; / 触发器

create or replace trigger trig after update on dept for each row

22

……………………………………………………………精品资料推荐…………………………………………………

begin

update emp set deptno=:NEW.deptno where deptno=:OLD.deptno; end; /

23