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"命令显示错误信息