PL/SQL编程整理3 -- 控制结构

1       If语句

案例1：if ….. then

Declare       V_sal emp.sal%type; Begin       Select sal into v_sal from emp where ename=’SCOTT’;       If v_sal < 2000 then            Update emp set sal*1.1 where ename=’SCOTT’;       End if; End

 

案例2：if …… then ……..else

Declare       V_sal emp.sal%type; Begin       Select sal into v_sal from emp where ename=’SCOTT’;       If v_sal < 2000 then            Update emp set sal*1.2 where ename=’SCOTT’;       Else            Update emp set sal*1.1 where ename=’SCOTT’;       End if; End

 

案例3：if …… then ……..elsif ……else

Declare       V_sal emp.sal%type; Begin       Select sal into v_sal from emp where ename=’SCOTT’;       If v_sal < 2000 then            Update emp set sal*1.3 where ename=’SCOTT’;       Elsif v_sal < 3000       Then            Update emp set sal*1.2 where ename=’SCOTT’;       Else            Update emp set sal*1.1 where ename=’SCOTT’;       End if; End

 

2       循环语句

Loop循环：以loop开头，end loop 结束，至少执行一次,例如：

Declare V_num number :=1; Begin       Loop            Insert into users values(v_num,’测试名称’||v_num);            Exit when v_num = 10;            V_num:=v_num+1;       End loop; End

 

While循环：以while 条件 loop开始，end loop结束，只有条件为true才执行，例如：

Declare V_num number :=1; Begin       While v_num <= 20       Loop            Insert into users values(v_num,’测试名称’||v_num);            V_num:=v_num+1;       End loop; End

 

For 循环

Begin       For i in reverse 1..10       Loop            Insert into users values(i,’测试名称’||i);       End loop; End

 

3       控制语句

Goto语句：用于跳转到特定标号去执行语句。

Declare       i int :=1; Begin       Loop            Dbms_output.put_line(‘输出i=’||i);            If i = 10 then goto end_loop;            end if;            i :=i+1;       End loop;       <<end_loop>> End

 

Null语句：null语句不会执行任何操作，并且会直接将控制传递到下一条语句。

Declare       V_sal emp.sal%type; Begin       Select sal into v_sal from emp where ename=’SCOTT’;       If v_sal < 2000 then            Update emp set sal*1.3 where ename=’SCOTT’;       Elsif v_sal < 3000       Then            null;       Else            Update emp set sal*1.1 where ename=’SCOTT’;       End if; End