八、处理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;