八、处理PL/SQL异常
异常抛出时,PL/SQL块或子程序的正常执行就会停止,控制权转到块或子程序的异常处理部分,语法如下:
EXCEPTION
WHEN exception_name1 THEN -- handler
sequence_of_statements1
WHEN exception_name2 THEN -- another handler
sequence_of_statements2
...
WHEN OTHERS THEN -- optional handler
sequence_of_statements3
END ;
为捕获抛出的异常,我们需要编写异常处理程序。每个处理程序都由一个WHEN子句和语句序列组成。这些语句执行完毕后,块或子程序就会结束,控制权不再返回异常被抛起的地方。换句话说,也就是我们不能再次返回异常发生的地方继续执行我们的程序。
可选的OTHERS处理器总是块或子程序的最后一个处理程序,它可以用于捕获所有的未命名异常。因此,块或子程序只能有一个OTHERS处理器。如下例所示,OTHERS处理器能够保证所有的异常都会被控制:
EXCEPTION
WHEN ... THEN
-- handle the error
WHEN ... THEN
-- handle the error
WHEN OTHERS THEN
-- handle all other errors
END ;
如果我们想让两个或更多的异常执行同样的语句序列,只需把异常名称用关键字OR隔开,放在同一个WHEN子句中即可,如下例所示:
EXCEPTION
WHEN over_limit OR under_limit OR VALUE_ERROR THEN
-- handle the error
只要在WHEN子句的异常列表中有一项与被抛出异常相匹配,相关的语句序列就会被执行。关键字OTHERS不能出现在异常名称列表中;它只能单独使用。我们可以有任意数量的异常处理程序,而且每个处理程序都与一个异常列表及其对应的语句序列相关联。但是,异常名称只能在块或子程序的异常处理部分出现一次。
变量作用范围的规则在这里也同样适用,所以我们可以在异常处理程序中引用本地或全局变量。但是,当游标FOR循环中有异常抛出时,游标就会在异常处理程序调用之前被隐式地关闭。因此,显式游标的属性值在异常处理程序中就不再可用了。
1、声明中控制异常
如果在声明时使用了错误的初始化表达式也有可能引发异常。例如,下面的声明就是因常量credit_limit不能存储超过999的数字而抛出了异常:
DECLARE
credit_limit CONSTANT NUMBER (3) := 5000; -- raises an exception
BEGIN
...
EXCEPTION
WHEN OTHERS THEN -- cannot catch the exception
...
END ;
当前块中的处理程序并不能捕获到抛出的异常,这是因为声明时抛出的异常会被立即传递到最近的封闭块中去。
2、异常句柄中控制异常
在一个块或子程序中,一次只能有一个异常被激活。所以,一个被异常处理程序抛出的异常会被立即传递到封闭块,在那儿,封闭块会为它查找新的处理程序。从那一刻起,异常传递才开始正常化。参考下面的例子:
EXCEPTION
WHEN INVALID_NUMBER THEN
INSERT INTO ... -- might raise DUP_VAL_ON_INDEX
WHEN DUP_VAL_ON_INDEX THEN ... -- cannot catch the exception
END ;
3、异常分支
GOTO语句不能跳转到异常控制程序。同样,GOTO语句也不能从异常控制程序跳转到当前块。例如,下面的GOTO语句就是非法的:
DECLARE
pe_ratio NUMBER (3, 1);
BEGIN
DELETE FROM stats
WHERE symbol = 'xyz' ;
SELECT price / NVL (earnings, 0)
INTO pe_ratio
FROM stocks
WHERE symbol = 'xyz' ;
<<my_label>>
INSERT INTO stats (symbol, ratio)
VALUES ('xyz' , pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
pe_ratio := 0;
GOTO my_label; -- illegal branch into current block
END ;
但是,GOTO语句可以从一个异常控制程序中跳转到一个封闭块。
4、获取错误代号与消息:SQLCODE和SQLERRM
在异常处理程序中,我们可以使用内置函数SQLCODE和SQLERRM来查出到底发生了什么错误,并能够获取相关的错误信息。对于内部异常来说, SQLCODE会返回Oracle错误编号。SQLCODE返回的总是一个负数,除非发生的Oracle错误是没有找到数据,这时返回的是+100。 SQLERRM会返回对应的错误消息。消息是以Oracle错误编号开头的。
如果我们没有使用编译指令EXCEPTION_INIT把异常与编号关联的话,SQLCODE和SQLERRM就会分别返回+1和消息"User- Defined Exception"。Oracle错误消息最大长度是512个字符,其中包括错误编号、嵌套消息和具体表和字段的名称。
如果没有异常抛出,SQLCODE返回0,SQLERRM返回消息"ORA-0000: normal, successful completion"。
我们可以把错误编号传递给SQLERRM,让它返回对应的错误消息。但是,一定要保证我们传递给SQLERRM的错误编号是负数。下例中,我们把一个正数传递给SQLERRM,结果就不是我们想要的那样的了:
DECLARE
err_msg VARCHAR2 (100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1 .. 9999 LOOP
err_msg := SQLERRM (err_num); -- wrong; should be -err_num
INSERT INTO ERRORS
VALUES (err_msg);
END LOOP ;
END ;
把正数传给SQLERRM时,如果传递的是+100,返回的结果是"no data found",其他情况总是会返回消息"user-defined exception"。把0传递给SQLERRM,就会返回消息"normal, successful completion"。
我们不能直接在SQL语句中使用SQLCODE或SQLERRM。我们必须先把它们的值赋给本地变量,然后再在SQL中使用变量,如下例所示:
DECLARE
err_num NUMBER ;
err_msg VARCHAR2 (100);
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE ;
err_msg := SUBSTR(SQLERRM , 1, 100);
INSERT INTO ERRORS
VALUES (err_num, err_msg);
END ;
字符串函数SUBSTR可以保证用SQLERRM为err_msg赋值时不会引起VALUE_ERROR异常。函数SQLCODE和SQLERRM在OTHERS异常处理程序中特别有用,因为它们能让我们知道哪个内部异常被抛出。
注意:在使用编译指示RESTRICT_REFERENCES判断存储函数的纯度时,如果函数调用了SQLCODE和SQLERRM,我们就不能指定约束为WNPS和RNPS了。
5、捕获未控制异常
记住,如果被抛出的异常找不到合适的异常控制程序,PL/SQL会向主环境抛出一个未捕获的异常错误,然后由主环境决定如何处理。例如,在Oracle预编译程序环境中,任何一个执行失败的SQL语句或PL/SQL块所涉及到的改动都会被回滚。
未捕获也能影响到子程序。如果我们成功地从子程序中退出,PL/SQL就会把值赋给OUT参数。但是,如果我们因未捕获异常而退出程序,PL/SQL就不会为OUT参数进行赋值。同样,如果一个存储子程序因异常而执行失败,PL/SQL也不会回滚子程序所做的数据变化。
我们可以在每个PL/SQL程序的顶级使用OTHERS句柄来捕获那些没有被子程序捕捉到的异常。
九、PL/SQL错误控制技巧
这里,我们将学习三个提高程序灵活性的技巧。
1、模拟TRY..CATCH..块
异常控制程序能让我们在退出一个块之前做一些恢复操作。但是在异常程序完成后,语句块就会终止。我们不能从异常句柄再重新回到当前块。例如,如果下面的SELECT INTO语句引起了ZERO_DIVIDE异常,我们就不能执行INSERT语句了:
DECLARE
pe_ratio NUMBER (3, 1);
BEGIN
DELETE FROM stats
WHERE symbol = 'XYZ' ;
SELECT price / NVL(earnings, 0)
INTO pe_ratio
FROM stocks
WHERE symbol = 'XYZ' ;
INSERT INTO stats(symbol, ratio)
VALUES ('XYZ' , pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
...
END ;
其实我们可以控制某一条语句引起的异常,然后继续下一条语句。只要把可能引起异常的语句放到它自己的子块中,并编写对应的异常控制程序。一旦在子块中有错误发生,它的本地异常处理程序就能捕获并处理异常。当子块结束时,封闭块程序会继续执行紧接着的下一条语句。如下例:
DECLARE
pe_ratio NUMBER (3, 1);
BEGIN
DELETE FROM stats
WHERE symbol = 'XYZ' ;
BEGIN -- sub-block begins
SELECT price / NVL(earnings, 0)
INTO pe_ratio
FROM stocks
WHERE symbol = 'XYZ' ;
EXCEPTION
WHEN ZERO_DIVIDE THEN
pe_ratio := 0;
END ; -- sub-block ends
INSERT INTO stats(symbol, ratio)
VALUES ('XYZ' , pe_ratio);
EXCEPTION
WHEN OTHERS THEN
...
END ;
在上面这个例子中,如果SELECT INTO语句抛出了ZERO_DIVIDE异常,本地异常处理程序就会捕捉到它并把pe_ratio赋值为0。当处理程序完成时,子块也就终止,INSERT语句就会被执行。
2、反复执行的事务
异常发生后,我们也许还不想放弃我们事务,仍想重新尝试一次。这项技术的实现方法就是:
- 把事务装入一个子块中。
- 把子块放入一个循环,然后反复执行事务
- 在开始事务之前标记一个保存点。如果事务执行成功的话,就提交事务并退出循环。如果事务执行失败,控制权就会交给异常处理程序,事务回滚到保存点,然后重新尝试执行事务。
如下例所示。当异常处理程序完成时,子块终止,控制权被交给外围块的LOOP语句,子块再次重新开始执行。而且,我们还可以用FOR或WHILE语句来限制重做的次数。
DECLARE
NAME VARCHAR2 (20);
ans1 VARCHAR2 (3);
ans2 VARCHAR2 (3);
ans3 VARCHAR2 (3);
suffix NUMBER := 1;
BEGIN
...
LOOP -- could be FOR i IN 1..10 LOOP to allow ten tries
BEGIN -- sub-block begins
SAVEPOINT start_transaction; -- mark a savepoint
/* Remove rows from a table of survey results. */
DELETE FROM results
WHERE answer1 = ’no’;
/* Add a survey respondent’s name and answers. */
INSERT INTO results
VALUES (NAME, ans1, ans2, ans3);
-- raises DUP_VAL_ON_INDEX if two respondents have the same name
COMMIT ;
EXIT ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO start_transaction; -- undo changes
suffix := suffix + 1; -- try to fix problem
NAME := NAME || TO_CHAR(suffix);
END ; -- sub-block ends
END LOOP ;
END ;
3、使用定位变量标记异常发生点
只用一个异常句柄来捕获一系列语句的话,可能无法知道到底是哪一条语句产生了错误:
BEGIN
SELECT ...
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN ...
-- Which SELECT statement caused the error?
END ;
要想解决这个问题,我们可以使用一个定位变量来跟踪执行语句,例如:
DECLARE
stmt INTEGER := 1; -- designates 1st SELECT statement
BEGIN
SELECT ...
stmt := 2; -- designates 2nd SELECT statement
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO errors VALUES ('Error in statement ' || stmt);
END ;