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节和本节介绍的技术结合起来,就有更多的机会诊断程序的问题所在。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值