Oracle异常判断OTHERS处理程序中发生了哪个错误

9.4 判断OTHERS处理程序中发生了哪个错误

问题

你的代码因为一个异常而不断执行失败时，就会调用OTHERS处理程序。这时你需要做的就是准确地找出异常原因以便对它进行修复。

解决方案

像9.2节那样编写一个OTHERS异常处理程序，并利用SQLCODE和DBMS_UNILITY.FORMAT_ERROR_STACK函数返回Oracle的错误编码和抛出异常的消息内容。下面的例子演示了这两个函数的用法，还是使用9.3节中的过程，只是在调用OTHERS处理程序时增加了对错误代码和消息的处理。

CREATE OR replace PROCEDURE Salary_increase(emp_id       IN NUMBER, 
                                            pct_increase IN NUMBER) 
AS 
  salary       employees.salary%TYPE; 
  max_salary   jobs.max_salary%TYPE; 
  invalid_increase EXCEPTION; 
  error_number NUMBER; 
  error_message   VARCHAR2(1500); 
BEGIN 
  SELECT salary, 
         max_salary 
  INTO   salary, max_salary 
  FROM   employees, 
         jobs 
  WHERE  employee_id = emp_id 
         AND jobs.job_id = employees.employee_id; 

  IF ( salary + ( salary * pct_increase ) ) <= max_salary THEN 
    UPDATE employees 
    SET    salary = ( salary + ( salary * pct_increase ) ) 
    WHERE  employee_id = emp_id; 

    dbms_output.Put_line('SUCCESSFUL SALARY INCREASE FOR EMPLOYEE #: ' 
                         || emp_id 
                         || '.  NEW SALARY = ' 
                         || salary + ( salary * pct_increase )); 
  ELSE 
    RAISE invalid_increase; 
  END IF; 
EXCEPTION 
  WHEN no_data_found THEN 
    dbms_output.Put_line('UNSUCCESSFUL INCREASE, NO EMPLOYEE RECORD FOUND '
                     || 'FOR THE ' 
                     || 'GIVEN ID'); 
  WHEN invalid_increase THEN 
    dbms_output.Put_line('UNSUCCESSFUL INCREASE.  YOU CANNOT INCREASE THE ' 
                     || 'EMPLOYEE ' 
                     || 'SALARY BY ' 
                     || pct_increase 
                     || ' PERCENT...PLEASE ENTER ' 
                     || 'A SMALLER INCREASE AMOUNT TO TRY AGAIN'); 
WHEN OTHERS THEN 
           error_number := SQLCODE; 

           error_message := DBMS_UTILITY.FORMAT_ERROR_STACK; 

    dbms_output.Put_line('UNSUCCESSFUL INCREASE.  AN UNKNOWN ERROR HAS '
                     || 'OCCURRED, ' 
                     || 'PLEASE TRY AGAIN OR CONTACT ADMINISTRATOR' 
                     || ' Error #: ' 
                     || error_number 
                     || ' - ' 
                     || error_message);

END; 
/

过程执行时，如果有错误发生，就会出现下面的输出：

UNSUCCESSFUL INCREASE. AN UNKNOWN ERROR HAS OCCURRED, PLEASE TRY AGAIN OR CONTACT
ADMINISTRATOR Error #: -1722 - ORA-01722: invalid number

这个例子故意抛出一个错误，目的就是要演示这些工具函数的功能用法。如果能在异常发生时找到发生错误的代码行号那就再好不过了。如果编写能够返回错误行号的异常处理程序，请参考9.9节。

原理分析

SQLCODE和DBMS_UTILITY.FORMAT_ERROR_STACK函数提供了一种方法，可以找出引发终极异常抛出的代码和消息。函数SQLCODE会返回Oracle内部异常的错误编号，而+1则代表用户自定义异常，DBMS_UTILITY.FORMAT_ERROR_STACK函数会返回被抛出的Oracle内部异常消息，如果抛出的是用户定义异常，消息中则会包含User-Defined Exceptipn字样。用户自定义异常可以接受一个自定义的错误编号，具体信息可以参考9.9节。在这种情况下，函数SQLCODE会在异常抛出时返回自定义错误编号。
要使用这些函数，必须先把它们赋给一个变量，因为它们无法直接调用。比如你想在CASE语句中使用SQLCODE函数，就必须先把这个函数赋给一个变量。赋值完成后，就可以在语句中使用这个已经赋值的变量了。
Oracle中的DBMS_UTILITY.FORMAT_ERROR_STACK可以用来返回与当前错误相关联的错误消息。DBMS_UTILITY.FORMAT_ERROR_STACK最多可以保存1899个字符，所以，使用这个函数时，消息几乎不会被截断。SQLERRM是一个具有类似功能的函数，它也可以返回错误消息，但它最多只能显示512字节。所以使用SQLERRM时，消息经常会被截断。相比SQLERRM，Oracle更推荐使用DBMS_UTILITY.FORMAT_ERROR_STACK，因为这个工具函数不会把消息长度限制在这么小的范围内。
但是，SQLERRM也有它自己的重要位置，因为它可以为我们带来几个好处。SQLERRM一个很方便的特性就是你可以把一个错误消息编号传递给它，然后可以得到相应的错误消息。传入SQLERRM的任何错误编号都应该是负数，否则，你得到的消息就是一个用户自定义消息。表9-2列举了使用SQLCODE和SQLERRM时，它们分别对应的错误编号取值范围和消息。

如果你想选用SQLERRM，代码不会与使用DBMS_UTILITY.FORMAT_ERROR_STACK相差太多，但是可能需要增加一些代码截断结果。下面的例子演示的是用SQLERRM而不是DBMS_UTILITY.FORMAT_ERROR_STACK的过程实现。

CREATE OR replace PROCEDURE Salary_increase(emp_id       IN NUMBER, 
                                            pct_increase IN NUMBER) 
AS 
  salary       employees.salary%TYPE; 
  max_salary   jobs.max_salary%TYPE; 
  invalid_increase EXCEPTION; 
  error_number NUMBER; 
  error_message   VARCHAR2(150); 
BEGIN 
  SELECT salary, 
         max_salary 
  INTO   salary, max_salary 
  FROM   employees, 
         jobs 
  WHERE  employee_id = emp_id 
         AND jobs.job_id = employees.employee_id; 

  IF ( salary + ( salary * pct_increase ) ) <= max_salary THEN 
    UPDATE employees 
    SET    salary = ( salary + ( salary * pct_increase ) ) 
    WHERE  employee_id = emp_id; 

    dbms_output.Put_line('SUCCESSFUL SALARY INCREASE FOR EMPLOYEE #: ' 
                         || emp_id 
                         || '.  NEW SALARY = ' 
                         || salary + ( salary * pct_increase )); 
  ELSE 
    RAISE invalid_increase; 
  END IF; 
EXCEPTION 
  WHEN no_data_found THEN 
dbms_output.Put_line('UNSUCCESSFUL INCREASE, NO EMPLOYEE RECORD FOUND FOR THE ' 
                     || 'GIVEN ID'); 
  WHEN invalid_increase THEN 
dbms_output.Put_line('UNSUCCESSFUL INCREASE.  YOU CANNOT INCREASE THE EMPLOYEE ' 
                     || 'SALARY BY ' 
                     || pct_increase 
                     || ' PERCENT...PLEASE ENTER ' 
                     || 'A SMALLER INCREASE AMOUNT TO TRY AGAIN'); 
  WHEN OTHERS THEN 
           error_number := SQLCODE; 

           error_message := Substr(sqlerrm, 1, 150); 

dbms_output.Put_line('UNSUCCESSFUL INCREASE.  AN UNKNOWN ERROR HAS OCCURRED, ' 
                     || 'PLEASE TRY AGAIN OR CONTACT ADMINISTRATOR' 
                     || ' Error #: ' 
                     || error_number 
                     || ' - ' 
                     || error_message); 
END; 
/

除了上面介绍的几个工具之外，还有一些别的工具可以帮助我们诊断抛出的是哪些错误，甚至可以查看完整的栈跟踪。这些工具的用法将在9.9节进一步讨论。到那时，将9.9节和本节介绍的技术结合起来，就有更多的机会诊断程序的问题所在。