PL\SQL用户指南与参考7.2 转载

八、处理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、反复执行的事务

异常发生后，我们也许还不想放弃我们事务，仍想重新尝试一次。这项技术的实现方法就是：

1. 把事务装入一个子块中。
2. 把子块放入一个循环，然后反复执行事务
3. 在开始事务之前标记一个保存点。如果事务执行成功的话，就提交事务并退出循环。如果事务执行失败，控制权就会交给异常处理程序，事务回滚到保存点，然后重新尝试执行事务。

如下例所示。当异常处理程序完成时，子块终止，控制权被交给外围块的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 ;