Oracle procedure--存储过程

创建存储过程

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(a in number, b number, ret out number, temp in out number) is  begin   if (a>b) then     ret := a;   else     ret := b;   end if;   temp := 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;/

注:当遇到编译错误时,使用"show error"命令显示错误信息