Sql> Create or Replace procedureSp_pro5 (id_invarchar2) is 2V_sal mytest.salary%type; 3 begin 4 SelectSalary intoV_sal fromMyTestwhereId=id_in; 5 ifV_sal< the Then 6 UpdateMyTestSetSalary=1.1*V_salwhereId=id_in; 7 End if; 8 End; 9 / Procedurecreated SQL> execSp_pro5 (4); PL/Sqlproceduresuccessfully completed SQL> Select * frommytest; ID NAME PASSWD SALARY----- -------------------- -------------------- ---------------------------------------------------------------- ----------------1 123 123 Max2Gaodingle!!!!123 Max3Gagaga123 Max4 125555passwd -
Sql> Create or Replace procedureSp_pro6 (id_invarchar2) is 2V_bonus Number(6,2); 3 begin 4 SelectBonus intoV_bonus fromMyTestwhereId=id_in; 5 ifV_bonus=0 Then 6 UpdateMyTestSetBonus= - whereId=id_in; 7 Else 8 UpdateMyTestSetBonus=V_bonus+ - whereId=id_in; 9 End if; Ten End; One / Procedurecreated SQL> Select * frommytest; ID NAME PASSWD SALARY BONUS----- -------------------- -------------------- -------- --------1 123 123 120.00 0.002Gaodingle!!!!123 120.00 0.003Gagaga123 120.00 100.004 125555passwd120.00 100.00SQL> execSp_pro6 (1); PL/Sqlproceduresuccessfully completed SQL> Select * frommytest; ID NAME PASSWD SALARY BONUS----- -------------------- -------------------- -------- --------1 123 123 120.00 100.002Gaodingle!!!!123 120.00 0.003Gagaga123 120.00 100.004 125555passwd120.00 100.00
Sql> Create or Replace procedureSp_pro7 is 2I Number:=0; 3 begin 4Loop5 Insert intoUsersValuesI'Test'||i); 6 Exit whenI=3; 7I=I+1; 8 EndLoop; 9 End; Ten / ProcedureCreated
PL/SQL Programming-control statements