SQL> create or replace procedure sp_pro5(id_in varchar2) is 2 v_sal mytest.salary%type; 3 begin 4 select salary into v_sal from mytest where id=id_in; 5 if v_sal<130 then 6 update mytest set salary=1.1*v_sal where id=id_in; 7 end if; 8 end; 9 / Procedure created SQL> exec sp_pro5(4); PL/SQL procedure successfully completed SQL> select * from mytest; ID NAME PASSWD SALARY ----- -------------------- -------------------- -------------------------------------------------------------------------------- 1 123 123 150 2 gaodingle!!!! 123 150 3 gagaga 123 150 4 125555 passwd 55
SQL> create or replace procedure sp_pro6(id_in varchar2) is 2 v_bonus number(6,2); 3 begin 4 select bonus into v_bonus from mytest where id=id_in; 5 if v_bonus=0 then 6 update mytest set bonus=100 where id=id_in; 7 else 8 update mytest set bonus=v_bonus+300 where id=id_in; 9 end if; 10 end; 11 / Procedure created SQL> select * from mytest; ID NAME PASSWD SALARY BONUS ----- -------------------- -------------------- -------- -------- 1 123 123 120.00 0.00 2 gaodingle!!!! 123 120.00 0.00 3 gagaga 123 120.00 100.00 4 125555 passwd 120.00 100.00 SQL> exec sp_pro6(1); PL/SQL procedure successfully completed SQL> select * from mytest; ID NAME PASSWD SALARY BONUS ----- -------------------- -------------------- -------- -------- 1 123 123 120.00 100.00 2 gaodingle!!!! 123 120.00 0.00 3 gagaga 123 120.00 100.00 4 125555 passwd 120.00 100.00
SQL> create or replace procedure sp_pro7 is 2 i number:=0; 3 begin 4 loop 5 insert into users values(i,‘test‘||i); 6 exit when i=3; 7 i:=i+1; 8 end loop; 9 end; 10 / Procedure created
原文：http://www.cnblogs.com/lingyejun/p/7096689.html