Posted on 2010-07-24 16:17
斷點 閱讀(269)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
--創建存儲過程：
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.deptno = 10) then
update emp2 set sal = sal+10 where current of c;
elsif(v_temp.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;
--帶參數的存儲過程，in傳入參數，默認為傳入，out傳出。
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;
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;