ORACLE PL/SQL编程之PL/SQL 流程控制语句

本篇主要内容如下：

　　3.1  条件语句

　　3.2  CASE 表达式

　　3.3  循环

　　3.4  标号和GOTO

　　3.5  NULL 语句

　　介绍PL/SQL的流程控制语句, 包括如下三类: 

　　控制语句: IF 语句 

　　循环语句: LOOP语句, EXIT语句 

　　顺序语句: GOTO语句, NULL语句

3.1  条件语句 

IF<布尔表达式>THEN PL/SQL 和 SQL语句 ENDIF; ----------------------- IF<布尔表达式>THEN PL/SQL 和 SQL语句 ELSE 其它语句 ENDIF; ----------------------- IF<布尔表达式>THEN PL/SQL 和 SQL语句 ELSIF < 其它布尔表达式>THEN 其它语句 ELSIF < 其它布尔表达式>THEN 其它语句 ELSE 其它语句 ENDIF; 提示: ELSIF 不能写成 ELSEIF

例1: 

DECLARE v_empno employees.employee_id%TYPE :=&empno; V_salary employees.salary%TYPE; V_comment VARCHAR2(35); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = v_empno; IF v_salary <1500THEN V_comment:='太少了,加点吧~!'; ELSIF v_salary <3000THEN V_comment:='多了点,少点吧~!'; ELSE V_comment:='没有薪水~!'; ENDIF; DBMS_OUTPUT.PUT_LINE(V_comment); exception when no_data_found then DBMS_OUTPUT.PUT_LINE('没有数据~!'); when others then DBMS_OUTPUT.PUT_LINE(sqlcode ||'---'|| sqlerrm); END;

例2:

DECLARE v_first_name VARCHAR2(20); v_salary NUMBER(7,2); BEGIN SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE employee_id =&emp_id; DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary); IF v_salary <10000THEN DBMS_OUTPUT.PUT_LINE('工资低于10000'); ELSE IF10000<= v_salary AND v_salary <20000THEN DBMS_OUTPUT.PUT_LINE('工资在10000到20000之间'); ELSE DBMS_OUTPUT.PUT_LINE('工资高于20000'); ENDIF; ENDIF; END;

例3:

DECLARE v_first_name VARCHAR2(20); v_hire_date DATE; v_bonus NUMBER(6,2); BEGIN SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees WHERE employee_id =&emp_id; IF v_hire_date > TO_DATE('01-1月-90')THEN v_bonus :=800; ELSIF v_hire_date > TO_DATE('01-1月-88')THEN v_bonus :=1600; ELSE v_bonus :=2400; ENDIF; DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date ||'、奖金是'||v_bonus); END;

3.2  CASE 表达式

---------格式一--------- CASE 条件表达式 WHEN 条件表达式结果1 THEN 语句段1 WHEN 条件表达式结果2 THEN 语句段2 ...... WHEN 条件表达式结果n THEN 语句段n [ELSE 条件表达式结果] END; ---------格式二--------- CASE WHEN 条件表达式1 THEN 语句段1 WHEN 条件表达式2 THEN 语句段2 ...... WHEN 条件表达式n THEN 语句段n [ELSE 语句段] END;

例4: 

DECLARE V_grade char(1) :=UPPER('&p_grade'); V_appraisal VARCHAR2(20); BEGIN V_appraisal := CASE v_grade WHEN'A'THEN'Excellent' WHEN'B'THEN'Very Good' WHEN'C'THEN'Good' ELSE'No such grade' END; DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal); END;

　例5: 

DECLARE v_first_name employees.first_name%TYPE; v_job_id employees.job_id%TYPE; v_salary employees.salary%TYPE; v_sal_raise NUMBER(3,2); BEGIN SELECT first_name, job_id, salary INTO v_first_name, v_job_id, v_salary FROM employees WHERE employee_id =&emp_id; CASE WHEN v_job_id ='PU_CLERK'THEN IF v_salary <3000THEN v_sal_raise := .08; ELSE v_sal_raise := .07; ENDIF; WHEN v_job_id ='SH_CLERK'THEN IF v_salary <4000THEN v_sal_raise := .06; ELSE v_sal_raise := .05; ENDIF; WHEN v_job_id ='ST_CLERK'THEN IF v_salary <3500THEN v_sal_raise := .04; ELSE v_sal_raise := .03; ENDIF; ELSE DBMS_OUTPUT.PUT_LINE('该岗位不涨工资:'||v_job_id); ENDCASE; DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id ||'、的工资是'||v_salary ||'、工资涨幅是'||v_sal_raise); END;

3.3  循环 

　　1.  简单循环

LOOP 要执行的语句; EXITWHEN<条件语句>--条件满足，退出循环语句 END LOOP;

例 6. 

DECLARE intNUMBER(2) :=0; BEGIN LOOP int :=int+1; DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int); EXITWHENint=10; END LOOP; END;

　2.  WHILE 循环

WHILE<布尔表达式> LOOP 要执行的语句; END LOOP;

例7. 

DECLARE x NUMBER :=1; BEGIN WHILE x<=10 LOOP DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x); x:= x+1; END LOOP; END;

3.  数字式循环 

[<<循环标签>>] FOR 循环计数器 IN[ REVERSE ] 下限 .. 上限 LOOP 要执行的语句; END LOOP [循环标签];

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。 

例8.

BEGIN FORintin1..10 LOOP DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int); END LOOP; END;

例 9.

CREATETABLE temp_table(num_col NUMBER); DECLARE V_counter NUMBER :=10; BEGIN INSERTINTO temp_table(num_col) VALUES (v_counter ); FOR v_counter IN20 .. 25 LOOP INSERTINTO temp_table (num_col ) VALUES ( v_counter ); END LOOP; INSERTINTO temp_table(num_col) VALUES (v_counter ); FOR v_counter INREVERSE20 .. 25 LOOP INSERTINTO temp_table (num_col ) VALUES ( v_counter ); END LOOP; END ; DROPTABLE temp_table;

例10:

DECLARE TYPE jobids_varray IS VARRAY(12)OFVARCHAR2(10);--定义一个VARRAY数据类型 v_jobids JOBIDS_VARRAY;--声明一个具有JOBIDS_VARRAY数据类型的变量 v_howmanyNUMBER;--声明一个变量来保存雇员的数量 BEGIN --用某些job_id值初始化数组 v_jobids := jobids_varray('FI_ACCOUNT','FI_MGR','ST_CLERK','ST_MAN'); --用FOR...LOOP...END LOOP循环使用每个数组成员的值 FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP --针对数组中的每个岗位，决定该岗位的雇员的数量 SELECTcount(*)INTO v_howmany FROM employees WHERE job_id = v_jobids(i); DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)|| '总共有'|| TO_CHAR(v_howmany) ||'个雇员'); END LOOP; END;

　例11 在While循环中嵌套loop循环

/*求100至110之间的素数*/ DECLARE v_m NUMBER :=101; v_i NUMBER; v_n NUMBER :=0; BEGIN WHILE v_m <110 LOOP v_i :=2; LOOP IF mod(v_m, v_i) =0THEN v_i :=0; EXIT; ENDIF; v_i := v_i +1; EXITWHEN v_i > v_m -1; END LOOP; IF v_i >0THEN v_n := v_n +1; DBMS_OUTPUT.PUT_LINE('第'|| v_n ||'个素数是'|| v_m); ENDIF; v_m := v_m +2; END LOOP; END;

　4.标号和GOTO 

　　PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：

　　GOTO label;
　　<<label>>　/*标号是用<<　>>括起来的标识符 */

　　注意，在以下地方使用是不合法的，编译时会出错误。

　　跳转到非执行语句前面。

　　跳转到子块中。

　　跳转到循环语句中。

　　跳转到条件语句中。

　　从异常处理部分跳转到执行。

　　从条件语句的一部分跳转到另一部分。

例12:

DECLARE V_counter NUMBER :=1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); V_counter := v_counter +1; IF v_counter >10THEN GOTO labelOffLOOP; ENDIF; END LOOP; <<labelOffLOOP>> DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); END;

例13:

DECLARE v_i NUMBER :=0; v_s NUMBER :=0; BEGIN <<label_1>> v_i := v_i +1; IF v_i <=1000THEN v_s := v_s + v_i; GOTO label_1; ENDIF; DBMS_OUTPUT.PUT_LINE(v_s); END;

3.5  NULL 语句 

　　在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。如：

　　例14:

DECLARE ... BEGIN ... IF v_num ISNULLTHEN GOTO labelPrint; ENDIF; … <<labelPrint>> NULL;--不需要处理任何数据。 END;

　例15:

DECLARE v_emp_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_salary employees.salary%TYPE; v_sal_raise NUMBER(3,2); BEGIN v_emp_id :=&emp_id; SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE employee_id = v_emp_id; IF v_salary <=3000THEN v_sal_raise := .10; DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary ||'、工资涨幅是'||v_sal_raise); ELSE NULL; ENDIF; END;

ORALCE 练习SQL:

-- 查看库中所有的表 SELECT * FROM tabs ; /*NVL2 的用法 ：如果前面的值为空就显示后面的*/ SELECT LA.APPL_CFM_DATE,        NVL2(TO_CHAR(LA.APPL_CFM_DATE),             TO_CHAR(LA.APPL_CFM_DATE, 'yyyy') || '年' ||             TO_CHAR(LA.APPL_CFM_DATE, 'MM') || '月' ||             TO_CHAR(LA.APPL_CFM_DATE, 'dd') || '日',             '     年   月   日') APPL_PPSL_RAT_START_DATE   FROM LS_APPL LA  WHERE LA.LS_APPL_SEQ = '202420903';    /* 不用test窗口测试test的方法 */  DECLARE  L_RC NUMBER ; O_MSG_0 NUMBER  ; O_MSG_1 VARCHAR2(1000) ; BEGIN    -- 利息调整交易包    L_RC := loan_admin.pa3l_chg_paym.get_chg_amt('0515201380027401','1',O_MSG_0,O_MSG_1) ;    dbms_output.put_line('调用 function 返回的值是: ' || L_RC );    dbms_output.put_line('调用 function 返回的变动金额是: ' || O_MSG_0 );    dbms_output.put_line('调用 function 返回的变动原因是: ' || O_MSG_1 ); END ; /*一个简单的例子*/ DECLARE         i NUMBER ; BEGIN    i := 30 ;   dbms_output.put_line('i 的内容是: ' || i ) ; END ; /* PROMPT 注释 spool  输出 */ /*根据用户输入的雇员号查询对应的雇员名字*/ DECLARE      eno NUMBER ;     NAME VARCHAR2(8) ; BEGIN    dbms_output.put_line('请输入雇员编号 : ' ) ;   eno := &empno ;   -- & 表示让用户输入参数   SELECT ename INTO NAME FROM emp WHERE empno = eno ; -- into 表示取的一个别名,在正文中用到   dbms_output.put_line('雇员编号'|| eno || '对应的雇员姓名是: ' || NAME ) ;   EXCEPTION      WHEN no_data_found THEN        dbms_output.put_line('没有找到数据!'); END ; /* LOOP 循环*/ DECLARE    i NUMBER ; BEGIN     i := 1 ;     LOOP       dbms_output.put_line('i 的内容是 : ' || i );     EXIT WHEN i >= 10  ;          i := i + 1 ;      END LOOP ; END ; /*FOR LOOP 循环*/ DECLARE    i NUMBER ;   j NUMBER ; BEGIN   j := 10 ;     FOR i IN 1..j LOOP        dbms_output.put_line('i 的内容是 : ' || i );      END LOOP ; END ; /* 游标的使用    1、声明游标    2、打开游标     3、使用游标    4、关闭游标  */ -- %rowtype  与   %TYPE  与  &empno 的使用  DECLARE     erow emp%ROWTYPE ;         -- 此就是可以装载下一行 emp 的记录    eno emp.empno%TYPE ;       -- 使用 emp 表中的 empno 字段类型定义 eno 变量  BEGIN   eno := '7369' ; -- &empno &表示用户自己定义输入   SELECT * INTO erow FROM emp WHERE empno = eno ;   dbms_output.put_line('姓名: ' || erow.ename ) ;   dbms_output.put_line('工资: ' || erow.sal ) ; END ; -- 1、定义游标  while 循环 DECLARE     erow emp%ROWTYPE ;       -- 此就是可以装载下一行 emp 的记录    CURSOR mycur IS SELECT * FROM emp ;    -- 声明一个游标 游标的名称是 mycur ; BEGIN     OPEN mycur ;             -- 打开游标          FETCH mycur INTO erow ;      -- 移动游标 ，把移动的每一行记录都放在 erow 中     WHILE (mycur%FOUND) LOOP          -- 如果游标的内容存在         dbms_output.put_line(erow.empno || ' --> ' || erow.ename ) ;        FETCH mycur INTO erow ;           -- 输出后还要继续移动游标 到下一条记录     END LOOP ;    CLOSE mycur ;                     -- 关闭游标    END ;    -- 2、定义游标  loop 循环 DECLARE     erow emp%ROWTYPE ;        -- 此就是可以装载下一行 emp 的记录    CURSOR mycur IS SELECT * FROM emp ;    -- 声明一个游标 游标的名称是 mycur ; BEGIN    OPEN mycur ;           -- 打开游标     LOOP        FETCH mycur INTO erow ;  -- 移动游标 ，把移动的每一行记录都放在 erow 中    EXIT WHEN (mycur%NOTFOUND)  ;       dbms_output.put_line(erow.empno || ' --> ' || erow.ename ) ;       END LOOP ;    CLOSE mycur ;           -- 关闭游标        END ;  -- 3、定义游标  for 循环   DECLARE     func_error EXCEPTION ; -- 自定义一个异常类型    erow emp%ROWTYPE ;      -- 此就是可以装载下一行 emp 的记录    CURSOR mycur IS SELECT * FROM emp ;    -- 声明一个游标 游标的名称是 mycur ; BEGIN    FOR erow IN mycur LOOP      -- 自动打开 、 关闭 、 移动 游标        IF erow.ename = 'JONES' THEN          dbms_output.put_line(erow.empno || ' 对应的客户名是 ' || 'JONES' ) ;         /* 向外抛异常，程序将会中断*/        -- RAISE func_error ;        /*跳出当前的循环,执行下一次*/        -- CONTINUE ;        /*退出循环*/         EXIT ;       END IF ;        dbms_output.put_line(erow.empno || ' --> ' || erow.ename ) ;       END LOOP ;    EXCEPTION       WHEN func_error THEN         dbms_output.put_line('抛异常啦.............') ;   END ;    -- 过程  = 过程的声明 + PLSEL 块  CREATE OR REPLACE PROCEDURE myproc(eno emp.empno%TYPE)  AS     esal emp.sal%TYPE ; BEGIN     SELECT sal INTO esal FROM emp WHERE empno = eno ;     dbms_output.put_line('雇员编号为: ' || eno || ' 的工资是：' || esal ) ; END ; -- 带参数的 CREATE OR REPLACE PROCEDURE myproc2(eno IN emp.empno%TYPE , esal OUT emp.sal%TYPE)  AS BEGIN     SELECT sal INTO esal FROM emp WHERE empno = eno ;     dbms_output.put_line('雇员编号为: ' || eno || ' 的工资是：' || esal ) ; END ; -- 过程是需要调用 通过 Exec(参数)的形式调用 。  EXEC myproc('7369'); /*      过程的参数有三种类型      。IN(传递一个数值，默认的)      。INOUT(带值进，并且修改后的值可以带出来)      。OUT(不带值进，但是可以带值出) */ -- 通过过程调用向部门表中添加一条记录 CREATE OR REPLACE PROCEDURE myprco1(dno dept.deptno%TYPE , NAME dept.dname%TYPE , lo dept.loc%TYPE) AS         coun NUMBER ; BEGIN   SELECT COUNT(deptno) FROM deptno WHERE deptno = dno ;   IF coun > 0 THEN          dbms_output.put_line('此部门'|| deptno ||'的信息已经存在。');       ELSE          INSERT INTO dept (deptno , dname , loc ) VALUES (dno , NAME , lo) ;    END if END ; -- 过程的调用  EXEC myprco1(50 , '技术部' , '北京' ); SELECT * FROM dept ; -- sql 块调用  DECLARE     eno emp.empno%TYPE ;    esalry emp.sal%TYPE ; BEGIN   eno := &empno ;   myproc2(eno , esalry);   dbms_output.put_line(esalry); END ;    -- 函数 CREATE OR REPLACE FUNCTION myfun(eno NUMBER) RETURN NUMBER AS esal NUMBER ;     -- 函数有返回值 BEGIN           SELECT sal + NVL(comm , 0) INTO esal FROM emp WHERE empno = eno ;   RETURN esal ;         -- 把内容返回  END ; SELECT myfun(7369) FROM dual ;  -- 使用函数  /*创建包*/ -- 建包头 CREATE OR REPLACE PACKAGE dyx_pckTest IS     FUNCTION myfunc(eno NUMBER ) RETURN NUMBER ; END dyx_pckTest ; -- 建包体 CREATE OR REPLACE PACKAGE BODY dyx_pckTest IS     FUNCTION myfunc(eno NUMBER ) RETURN NUMBER AS esal NUMBER ;     BEGIN         SELECT sal + NVL(comm , 0) INTO esal FROM emp where empno = eno ;        RETURN esal ;     END ; END dyx_pckTest ; -- 测试  dyx_pckTest.myfunc ; -- union 表示联合两个查询 ，但是重复数据不会显示 union all 联合查询，所有的重复数据都会显示 minus 返回查询中的不同部分 intersect 返回相同的部分 -- 复制表  CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno = 20 ; -- 联合查询  SELECT * FROM emp  UNION  SELECT * FROM emp20 ; -- 查看全部的用户表 SELECT * FROM User_Tables ; -- 修改表的名称 RENAME 旧的表名称 TO 新的表名称 ; -- 截断表 ; 清空表中的所有数据 ，释放 序列等所有资源 TRUNCATE TABLE emp20 ;/*表名称 ;*/ -- 删除表  DROP TABLE emp20 ; DROP TABLE 表名称 ; -- 查看回收站 SHOW RECYCLEBIN ; -- 从回收站中恢复表  FLASHBACK TABLE emp20 /*表名称*/ TO BEFORE DROP ; -- 从回收站删除表 PURGE TABLE 表名称 ; -- 清空回收站 PURGE RECYCLEBIN ; -- 彻底删除表  DROP TABLE 表名称 PURGE ; -- 截取小数 /*trunc(x[,y]) 【功能】返回x按精度y截取后的值 【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。 【返回】数字 */ SELECT TRUNC(189.567),TRUNC(-189.567),TRUNC(189.567,2),TRUNC(189.567,-2) FROM dual ; -- 查询雇员来的天数，精确到天  SELECT empno,ename,hiredate,          TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ,          TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months ,          TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day FROM emp ; -- 查看 Sequence  SELECT myseq.nextval FROM dual ; SELECT COUNT(*) FROM emp ;

http://kb.cnblogs.com/kb/101378/