编程语言控制结构
在下面的章节中，介绍了 SPL 编程语言如何实现对标准 SQL 进行完全面向过程的特性补充。
IF 语句
可以使用IF命令根据指定的条件来执行语句。SPL 提供了四种方式的 IF：
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSE IF
IF ... THEN ... ELSIF ... THEN ... ELSE
准备测试数据：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1006,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'JANE','CLERK',7912,'19-DEC-80',800,452,20);
INSERT INTO emp VALUES (1008,'JANE','CLERK',7912,'19-DEC-80',800,2500,20);
INSERT INTO emp VALUES (1009,'JANE','CLERK',7912,'19-DEC-80',800,1500,20);
IF-THEN
语法：
IF boolean-expression THEN
statements
END IF;
IF-THEN 语句是最简单的一种 IF 语句。如果条件为真的话，将执行在 THEN 和 END IF 之间的语句，否则，将不会执行这些语句。
在下面的示例中，使用 IF-THEN 语句测试和显示有佣金的雇员：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_1()
IS
v_empno emp.empno%TYPE;
v_comm emp.comm%TYPE;
CURSOR emp_cursor IS SELECT empno, comm FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_cursor INTO v_empno, v_comm;
EXIT WHEN emp_cursor%NOTFOUND;
--
-- Test whether or not the employee gets a commission
--
IF v_comm IS NOT NULL AND v_comm > 0 THEN
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是这个程序的输出结果：
antdb=# SELECT emp_test_1();
NOTICE: EMPNO COMM
NOTICE: ----- -------
NOTICE: 1001 $ 855.90
NOTICE: 1002 $ 300.00
NOTICE: 1007 $ 452.00
NOTICE: 1008 $ 2500.00
NOTICE: 1009 $ 1500.00
EMP_TEST_1
------------
(1 row)
IF-THEN-ELSE
语法：
IF boolean-expression THEN
statements
ELSE
statements
END IF;
如果 IF-THEN-ELSE 语句中条件表达式的返回值为 FALSE，那么将执行 ELSE 后面的语句。
现在对前面的示例进行了修改，用 IF-THEN-ELSE 语句替代了 IF-THEN 语句。这样当检测到雇员没有佣金的时候，会显示文本消息 'Non-commission'（emp 表格定义见【示例参考表格】） 。
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_2()
IS
v_empno emp.empno%TYPE;
v_comm emp.comm%TYPE;
CURSOR emp_cursor IS SELECT empno, comm FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_cursor INTO v_empno, v_comm;
EXIT WHEN emp_cursor%NOTFOUND;
--
-- Test whether or not the employee gets a commission
--
IF v_comm IS NOT NULL AND v_comm > 0 THEN
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
ELSE
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || 'Non-commission');
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是这个程序输出结果：
antdb=# SELECT emp_test_2();
NOTICE: EMPNO COMM
NOTICE: ----- -------
NOTICE: 1001 $ 855.90
NOTICE: 1002 $ 300.00
NOTICE: 1003 Non-commission
NOTICE: 1004 Non-commission
NOTICE: 1005 Non-commission
NOTICE: 1006 Non-commission
NOTICE: 1007 $ 452.00
NOTICE: 1008 $ 2500.00
NOTICE: 1009 $ 1500.00
EMP_TEST_2
------------
(1 row)
IF-THEN-ELSE IF
IF 语句可以嵌套使用，这样可以根据外层 IF 语句的条件返回值，来调用不同的内层 IF 语句。
在下面的示例中，外层的 IF-THEN-ELSE 语句测试一个雇员是否有佣金。内层的 IF-THEN-ELSE 语句测试雇员总的赔偿金额是否超过或者小于公司的平均水平（emp 表格定义见【示例参考表格】）。
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_3()
IS
v_empno emp.empno%TYPE;
v_sal emp.sal%TYPE;
v_comm emp.comm%TYPE;
v_avg NUMBER(7,2);
CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp ORDER BY 1;
BEGIN
--
-- Calculate the average yearly compensation in the company
--
SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||TO_CHAR(v_avg,'$999,999.99'));
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO YEARLY COMP');
DBMS_OUTPUT.PUT_LINE('----- -----------');
LOOP
FETCH emp_cursor INTO v_empno, v_sal, v_comm;
EXIT WHEN emp_cursor%NOTFOUND;
--
-- Test whether or not the employee gets a commission
--
IF v_comm IS NOT NULL AND v_comm > 0 THEN
--
-- Test if the employee's compensation with commission exceeds the average
--
IF (v_sal + v_comm) * 24 > v_avg THEN
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Exceeds Average');
ELSE
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Below Average');
END IF;
ELSE
--
-- Test if the employee's compensation without commission exceeds the average
--
IF v_sal * 24 > v_avg THEN
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
ELSE
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
END IF;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
注意：也可以使用游标的 SELECT 命令中的 NVL 函数计算雇员年度赔偿金额，这样可以简化程序的逻辑。这个示例的目的是为了演示 IF 语句可达到相同的功能。
下面是这个程序的输出结果：
antdb=# SELECT emp_test_3();
NOTICE: Average Yearly Compensation: $ 36,287.73
NOTICE: EMPNO YEARLY COMP
NOTICE: ----- -----------
NOTICE: 1001 $ 39,741.60 Exceeds Average
NOTICE: 1002 $ 45,600.00 Exceeds Average
NOTICE: 1003 $ 19,200.00 Below Average
NOTICE: 1004 $ 19,200.00 Below Average
NOTICE: 1005 $ 19,200.00 Below Average
NOTICE: 1006 $ 19,200.00 Below Average
NOTICE: 1007 $ 30,048.00 Below Average
NOTICE: 1008 $ 79,200.00 Exceeds Average
NOTICE: 1009 $ 55,200.00 Exceeds Average
EMP_TEST_3
------------
(1 row)
当使用这种形式 IF 语句的时候，实际上在外层 IF 语句的 ELSE 内部嵌套了一个 IF 语句。因此需要为每一个嵌套的 IF 语句提供相对应的一个 END IF 语句，为最外层的 IF-ELSE 提供一个 END IF 语句。
IF-THEN-ELSIF-ELSE
语法：
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END IF;
IF-THEN-ELSIF-ELSE 的作用是在 IF 语句中通过检测多个条件来执行相对应的语句。一般情况下，它等同于对 IF-THEN-ELSE-IF-THEN 命令的嵌套使用，不同之处是只需要一个 END IF 语句。
下面这个示例使用 IF-THEN-ELSIF-ELSE 语句来计算赔偿金范围在 25000 美元雇员的数量（emp 表格定义见【示例参考表格】）：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_4()
IS
v_empno emp.empno%TYPE;
v_comp NUMBER(8,2);
v_lt_25K SMALLINT := 0;
v_25K_50K SMALLINT := 0;
v_50K_75K SMALLINT := 0;
v_75K_100K SMALLINT := 0;
v_ge_100K SMALLINT := 0;
CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_comp;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_comp < 25000 THEN
v_lt_25K := v_lt_25K + 1;
ELSIF v_comp < 50000 THEN
v_25K_50K := v_25K_50K + 1;
ELSIF v_comp < 75000 THEN
v_50K_75K := v_50K_75K + 1;
ELSIF v_comp < 100000 THEN
v_75K_100K := v_75K_100K + 1;
ELSE
v_ge_100K := v_ge_100K + 1;
END IF;
END LOOP;
CLOSE emp_cursor;
DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
DBMS_OUTPUT.PUT_LINE('100,000 AND over : ' || v_ge_100K);
END;
/
\set PLSQL_MODE off
下面是这个程序的输出：
antdb=# SELECT emp_test_4();
NOTICE: Number of employees by yearly compensation
NOTICE: Less than 25,000 : 4
NOTICE: 25,000 - 49,9999 : 3
NOTICE: 50,000 - 74,9999 : 1
NOTICE: 75,000 - 99,9999 : 1
NOTICE: 100,000 AND over : 0
EMP_TEST_4
------------
(1 row)
CASE 表达式
CASE 表达式返回一个数值，用来替代在一个表达式中的 CASE 表达式。
CASE 表达式有两种格式。一种称为被动搜索型 CASE 表达式，另外一种是主动选择型 CASE 表达式。
准备测试数据：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,40);
INSERT INTO emp VALUES (1006,'MILLER','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'ADAMS','CLERK',7912,'19-DEC-80',800,452,30);
INSERT INTO emp VALUES (1008,'JONES','CLERK',7912,'19-DEC-80',800,2500,10);
INSERT INTO emp VALUES (1009,'FORD','CLERK',7912,'19-DEC-80',800,1500,30);
主动选择型 CASE 表达式
主动选择型 CASE 表达式使用一个被称为选择者的表达式匹配一个或多个在 WHEN 子句中指定的表达式。result 是与 CASE 表达式类型相兼容的表达式。如果相匹配，那么 CASE 表达式返回对应 THEN 子句中的值。如果这里没有匹配的话，那么 ELSE 子句后面的值将被返回。如果省略了 ELSE 子句，那么 CASE 表达式返回空值。
语法：
CASE SELECTor-expression
WHEN match-expression THEN
result
[ WHEN match-expression THEN
result
[ WHEN match-expression THEN
result ] ...]
[ ELSE
result ]
END;
match-expression 是根据在 CASE 表达式出现的顺序来计算的。result 是一个与 CASE 表达式类型相兼容的表达式。当遇到第一个等于selector-expression 的 match-expression，对应 THEN 子句的 result 作为 CASE 表达式的值返回。如果没有 match-expression 与 selector-expression 相等，那么将返回 ELSE 后面的参数 result。如果没有指定 ELSE，那么 CASE 表达式返回空值。
下面的示例用主动选择型 CASE 表达式根据部门号码把部门名称分配给一个变量：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_1()
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname VARCHAR2(20);
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
v_dname :=
CASE v_deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'unknown'
END;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是程序的输出结果：
antdb=# SELECT emp_case_1();
NOTICE: EMPNO ENAME DEPTNO DNAME
NOTICE: ----- ------- ------ ----------
NOTICE: 1001 SMITH 10 Accounting
NOTICE: 1002 ALLEN 30 Sales
NOTICE: 1003 SMITH 20 Research
NOTICE: 1004 JACK 20 Research
NOTICE: 1005 JANE 40 Operations
NOTICE: 1006 MILLER 20 Research
NOTICE: 1007 ADAMS 30 Sales
NOTICE: 1008 JONES 10 Accounting
NOTICE: 1009 FORD 30 Sales
EMP_CASE_1
------------
(1 row)
被动搜索型 CASE 表达式
一个被动搜索型 CASE 表达式是用一个或多个 Boolean 表达式确认结果值，然后将其返回。
语法：
CASE WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result ] ...]
[ ELSE
result ]
END;
boolean-expression 是根据在 CASE 表达式中出现的顺序来进行计算的。result 是在 CASE 表达式中类型相兼容的表达式。当遇到第一个计算为”true”的布尔表达式，那么在对应的 THEN 子句中 result 做为 CASE 表达式的值返回。如果没有遇到计算值为真 boolean-expression，那么返回 ELSE 后面的值。如果没有指定 ELSE 子句，那么 CASE 表达式返回为空。
在下面的示例中，使用了被动搜索型 CASE 表达式根据部门号码把部门名称分配给一个变量。
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_2()
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname VARCHAR2(20);
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
v_dname :=
CASE
WHEN v_deptno = 10 THEN 'Accounting'
WHEN v_deptno = 20 THEN 'Research'
WHEN v_deptno = 30 THEN 'Sales'
WHEN v_deptno = 40 THEN 'Operations'
ELSE 'unknown'
END;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是程序的输出结果：
antdb=# SELECT emp_case_2();
NOTICE: EMPNO ENAME DEPTNO DNAME
NOTICE: ----- ------- ------ ----------
NOTICE: 1001 SMITH 10 Accounting
NOTICE: 1002 ALLEN 30 Sales
NOTICE: 1003 SMITH 20 Research
NOTICE: 1004 JACK 20 Research
NOTICE: 1005 JANE 40 Operations
NOTICE: 1006 MILLER 20 Research
NOTICE: 1007 ADAMS 30 Sales
NOTICE: 1008 JONES 10 Accounting
NOTICE: 1009 FORD 30 Sales
EMP_CASE_2
------------
(1 row)
CASE 语句
当指定的搜索条件为真的时候 CASE 语句执行一个或多个的语句集。CASE 语句自己是一个单独的语句，前面讨论的 CASE 表达式作为整个表达式的一部分在 CASE 语句中出现。
CASE 语句有两种格式，一种被称为被动搜索型 CASE 语句，另外一种被称为主动选择型 CASE 语句。
主动选择型 CASE 语句
主动选择型 CASE 语句尝试去匹配在 WHEN 子句中指定的表达式。当找到匹配条件，将执行相对应的语句。
语法：
CASE selector-expression
WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
selector-expression 返回一个与每一个匹配表达式数据类型相兼容的值。match-expression 是按照在 CASE 语句中出现的顺序来进行计算的。statements 是 SPL 语句，每一个 statement 以一个分号结束。当遇到第一个与参数 selector-expression 的值相等的 match-expression，执行对应的 THEN 子句中的语句，然后流程就运行到 END CASE 关键字的后面。如果这里没有匹配，那么将执行 ELSE 后面的语句。如果没有找到匹配的表达式并且没有 ELSE 子句，那么就会产生一个异常。
下面的示例根据一个部门号使用主动选择型的 CASE 语句将一个部门名称和地理位置分配给一个变量：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_3()
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname VARCHAR2(20);
v_loc VARCHAR2(20);
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
CASE v_deptno
WHEN 10 THEN v_dname := 'Accounting';
v_loc := 'New York';
WHEN 20 THEN v_dname := 'Research';
v_loc := 'Dallas';
WHEN 30 THEN v_dname := 'Sales';
v_loc := 'Chicago';
WHEN 40 THEN v_dname := 'Operations';
v_loc := 'Boston';
ELSE v_dname := 'unknown';
v_loc := '';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是这个程序的输出结果：
antdb=# SELECT emp_case_3();
NOTICE: EMPNO ENAME DEPTNO DNAME LOC
NOTICE: ----- ------- ------ ---------- ---------
NOTICE: 1001 SMITH 10 Accounting New York
NOTICE: 1002 ALLEN 30 Sales Chicago
NOTICE: 1003 SMITH 20 Research Dallas
NOTICE: 1004 JACK 20 Research Dallas
NOTICE: 1005 JANE 40 Operations Boston
NOTICE: 1006 MILLER 20 Research Dallas
NOTICE: 1007 ADAMS 30 Sales Chicago
NOTICE: 1008 JONES 10 Accounting New York
NOTICE: 1009 FORD 30 Sales Chicago
EMP_CASE_3
------------
(1 row)
被动搜索型 CASE 语句
被动搜索型 CASE 语句使用一个或多个布尔型表达式来决定要执行的语句。
语法：
CASE WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
boolean-expression 是以在 CASE 语句中出现的顺序来进行计算的。当遇到第一个计算为”true”的 boolean-expression，那么就执行对应 THEN 子句中的语句，然后流程就会运行到关键字 END CASE 的后面。如果没有 boolean-expression 计算为真的话，那么将执行在 ELSE 后面的语句。如果没有没有 boolean-expression 计算为真并且没有 ELSE 子句，那么将会产生异常。
下面的示例根据部门编号使用被动搜索型 CASE 语句把部门名称和位置分配给变量：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_4()
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname VARCHAR2(20);
v_loc VARCHAR2(20);
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
CASE
WHEN v_deptno = 10 THEN v_dname := 'Accounting';
v_loc := 'New York';
WHEN v_deptno = 20 THEN v_dname := 'Research';
v_loc := 'Dallas';
WHEN v_deptno = 30 THEN v_dname := 'Sales';
v_loc := 'Chicago';
WHEN v_deptno = 40 THEN v_dname := 'Operations';
v_loc := 'Boston';
ELSE v_dname := 'unknown';
v_loc := '';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
END LOOP;
CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off
下面是这个程序的输出结果：
antdb=# SELECT emp_case_4();
NOTICE: EMPNO ENAME DEPTNO DNAME LOC
NOTICE: ----- ------- ------ ---------- ---------
NOTICE: 1001 SMITH 10 Accounting New York
NOTICE: 1002 ALLEN 30 Sales Chicago
NOTICE: 1003 SMITH 20 Research Dallas
NOTICE: 1004 JACK 20 Research Dallas
NOTICE: 1005 JANE 40 Operations Boston
NOTICE: 1006 MILLER 20 Research Dallas
NOTICE: 1007 ADAMS 30 Sales Chicago
NOTICE: 1008 JONES 10 Accounting New York
NOTICE: 1009 FORD 30 Sales Chicago
EMP_CASE_4
------------
(1 row)
循环语句
可以使用 LOOP、 EXIT、 CONTINUE、WHILE 和 FOR 语句，使 SPL 程序重复执行一个命令序列。
LOOP
语法：
LOOP
statements
END LOOP;
LOOP 语句定义了一个无条件循环，这个循环进行无限次循环，当遇到 EXIT 或者 RETURN 命令时终止循环。
EXIT
语法：
EXIT [ WHEN expression ];
这个语句的作用是结束最内层的循环，然后执行在 END LOOP 后面的语句。如果出现了 WHEN 子句，那么只有当指定的条件为真的时候，才能退出循环，否则流程将会走到 EXIT 后面的语句。可以使用 EXIT 语句，提前从所有类型的循环中退出，这个语句在无条件循环中的使用不受限制。
下面是一个循环的简单示例，这个循环重复执行 10 次，然后使用 EXIT 语句退出循环。
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_1 ()
IS
v_counter NUMBER(2);
BEGIN
v_counter := 1;
LOOP
EXIT WHEN v_counter > 10;
DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
\set PLSQL_MODE off
下面是程序的输出结果：
antdb=# SELECT LOOP_PRO_1();
NOTICE: Iteration # 1
NOTICE: Iteration # 2
NOTICE: Iteration # 3
NOTICE: Iteration # 4
NOTICE: Iteration # 5
NOTICE: Iteration # 6
NOTICE: Iteration # 7
NOTICE: Iteration # 8
NOTICE: Iteration # 9
NOTICE: Iteration # 10
LOOP_PRO_1
------------
(1 row)
CONTINUE
CONTINUE 语句提供一种方式可以跳开循环中间的语句，而重复下一次循环。
当在循环中遇到 CONTINUE 语句的时候，跳过所有在 CONTINUE 语句后面的语句，开始重复在最内层的循环，直到循环结束。如果循环体内部进行了重新计算，那么流程就传递给了循环控制表达式。
如果使用了 WHEN 子句，只有当 WHEN 子句中指定的表达式为真的情况下才重复下一次循环，否则控制流程就是运行到 CONTINUE 后面的语句。
COTNIUE 语句不能在循环的外部使用。
下面这个示例与前面的不同，它使用了 CONTINUE 语句来跳过奇数数值的显示：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_2 ()
IS
v_counter NUMBER(2);
BEGIN
v_counter := 0;
LOOP
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
CONTINUE WHEN MOD(v_counter,2) = 1;
DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
END LOOP;
END;
/
\set PLSQL_MODE off
下面是以上程序的输出：
antdb=# SELECT LOOP_PRO_2();
NOTICE: Iteration # 2
NOTICE: Iteration # 4
NOTICE: Iteration # 6
NOTICE: Iteration # 8
NOTICE: Iteration # 10
LOOP_PRO_2
------------
(1 row)
WHILE
语法：
WHILE expression LOOP
statements
END LOOP;
当条件表达式的值为真，WHILE 语句会重复循环体中语句。在每次进入循环主体前，都会先检查条件。
下面的示例和前面示例中的程序逻辑相同，不同之处是用 WHILE 语句替代 EXIT 语句来决定什么时候退出循环。
注意：必须在循环中修改用来决定何时退出循环的条件表达式，当 EXIT 语句的条件表达式为真的时候，将退出循环。当 WHILE 语句的条件表达式为假的时候，会结束循环（或者从来不进入循环）。
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_3 ()
IS
v_counter NUMBER(2);
BEGIN
v_counter := 1;
WHILE v_counter <= 10 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
\set PLSQL_MODE off
这个示例产生了和上一个示例一样的结果：
antdb=# SELECT LOOP_PRO_3();
NOTICE: Iteration # 1
NOTICE: Iteration # 2
NOTICE: Iteration # 3
NOTICE: Iteration # 4
NOTICE: Iteration # 5
NOTICE: Iteration # 6
NOTICE: Iteration # 7
NOTICE: Iteration # 8
NOTICE: Iteration # 9
NOTICE: Iteration # 10
LOOP_PRO_3
------------
(1 row)
FOR (根据整数循环)
语法：
FOR name IN expression .. expression LOOP
statements
END LOOP;
这种类型的 FOR 语句，创建了一个循环，循环次数由一个范围的整数来确定。变量 name 自动定义为整型，只在循环中有效。在开始进入循环的时候，对确定循环范围的表达式进行计算。重复的步骤为‘+1’，并且名字是以在‘..'左边的表达式的值开始。当变量 name 超过了'..'右边表达式的值，结束循环，因此两个表达式带有两个角色，开始值..结束值。
下面的示例使用了从 1 重复到 10 的 FOR 循环，这样进一步简化了 WHILE 循环：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_4 ()
IS
BEGIN
FOR i IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
END LOOP;
END;
/
\set PLSQL_MODE off
下面是使用 FOR 语句的程序输出结果：
antdb=# SELECT LOOP_PRO_4();
NOTICE: Iteration # 1
NOTICE: Iteration # 2
NOTICE: Iteration # 3
NOTICE: Iteration # 4
NOTICE: Iteration # 5
NOTICE: Iteration # 6
NOTICE: Iteration # 7
NOTICE: Iteration # 8
NOTICE: Iteration # 9
NOTICE: Iteration # 10
LOOP_PRO_4
------------
(1 row)
如果开始值大于结束值，那么循环主体的语句不会执行，而不会产生任何错误。如下面这个示例所示：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_5 ()
IS
BEGIN
FOR i IN 10 .. 1 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
END LOOP;
END;
/
\set PLSQL_MODE off
由于循环主体没有被执行，因此这个示例没有任何输出。
注意：SPL 也支持 CURSOR FOR 循环。
异常处理
在缺省状态下，在一个 SPL 程序中遇到任何错误都会中断程序的执行。可以使用带有 EXCEPTION 部分的 BEGIN 代码块来捕获错误，并且恢复程序的处理流程。具体语法就是 BEGIN 代码块语法的扩展。
语法：
[ IS
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ]... THEN
handler_statements
[ WHEN condition [ OR condition ]... THEN
handler_statements ]...
END;
如果没有遇到错误，那么这种形式的代码块执行所有的 statements，然后控制流程就转到 END 后面的语句。但是，如果在语句内出现了运行错误的情况，那么就会被放弃这个 statements 后面的处理工作，控制流程就会转到异常列表中。这个列表用来搜索匹配发生错误的第一个条件，如果找到一个匹配条件，那么将会执行相应的处理语句，然后控制流程会转到 END 后面的语句。如果没有找到匹配的条件，那么错误将会传送，就好像这里没有 EXCEPTION 子句。错误可以被封闭的处理异常的代码块捕获，否则的话，将在终止子程序的执行。
名称为 OTHERS 的指定条件匹配每一种错误类型，条件名称不区分大小写。
如果在异常处理中的语句产生了新的错误，那么 EXCEPTION 子句不能捕获这个错误，但是该错误将会继续传递，后面的 EXCEPTION 子句能够捕获这个异常。
在下面的表中列出了可以被使用的条件名称。
异常条件名称：
| 条件名称 | 描述 |
|---|---|
| NO_DATA_FOUND | 没有满足查询条件的记录 |
| TOO_MANY_ROWS | 当只有一行记录被允许返回时，返回多条满足查询条件的记录 |
| ZERO_DIVIDE | 除数为零 |
示例：
准备测试数据：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_1 ()
IS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO STRICT v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
DBMS_OUTPUT.PUT_LINE('employee is ' || v_ename);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found');
DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);
END;
/
\set PLSQL_MODE off
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_2 (p_empno IN NUMBER)
IS
r_emp emp%ROWTYPE;
BEGIN
SELECT * INTO STRICT r_emp FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;
/
\set PLSQL_MODE off
抛出程序异常
通过使用存储过程 RAISE_APPLICATION_ERROR，可以主动的产生异常，使 SPL 程序内部流程中断处理。除此之外，存储过程RAISE_APPLICATION_ERROR 为能够用来标识异常的程序提供了用户自定义的代码和相关的错误信息。
语法：
RAISE_APPLICATION_ERROR(error_number, message);
当执行存储过程时，err_number 是一个整数值或者是返回名称为 SQLCODE 的变量的表达式。message 是一个字符串常量或者一个返回一个命名为 SQLERRM 的变量的表达式。
在下面的示例中根据雇员的缺失信息使用 RAISE_APPLICATION_ERROR 过程来显示不同的代码和信息：
准备测试数据：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp VALUES (1004,'JOHN','CLERK',null,'17-DEC-80',800,855.90,20);
创建存储过程：
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE verify_emp (p_empno NUMBER)
IS
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;
v_mgr emp.mgr%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
SELECT ename, job, mgr, hiredate INTO v_ename, v_job, v_mgr, v_hiredate FROM emp WHERE empno = p_empno;
IF v_ename IS NULL THEN
RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
END IF;
IF v_job IS NULL THEN
RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
END IF;
IF v_mgr IS NULL THEN
RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
END IF;
IF v_hiredate IS NULL THEN
RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
END IF;
DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||' validated without errors');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER EXCEPTION.');
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/
\set PLSQL_MODE off
下面是当经理编号在雇员记录中不存在的情况下，存储过程的输出：
antdb=# SELECT verify_emp(1004);
NOTICE: OTHER EXCEPTION.
NOTICE: SQLCODE: 16777248
NOTICE: SQLERRM: ORA-20030: No manager for 1004
VERIFY_EMP
------------
(1 row)