PLSQL编程—控制语句

PL/SQL编程—控制语句

 

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