PLSQL-ERROR管理

一、异常概述

       在PL/SQL中有三类异常:内部定义异常、预定义异常和用户定义异常。

1、内部定义异常（An internally defined exception）

       内部定义异常是由Oracle数据库进程内部引发的，这种异常总是有一个错误代码，但没有名称，除非它是由PL/SQL或自己的代码指定的。内部定义的异常的一个示例：ORA-00060(等待资源时检测到死锁)。

2、预定义异常（predefined exception）

       预定义异常是由PL/SQL分配名称的内部定义的异常。大多数预定义的异常都是在标准包(Oracle数据库提供的包，定义了PL/SQL语言的许多通用编程元素)中定义的，并且是最常见的异常。一个例子：ORA-00001，它在PL/SQL中被赋值为DUP_VAL_ON_INDEX，并在违反唯一索引约束时引发。

3、用户定义异常（user-defined exception ）

       用户定义异常是在程序单元的声明部分中声明的。用户定义的异常可以与内部定义的异常相关联(也就是说，可以为未命名的异常提供名称)，也可以与特定于应用程序的错误相关联。

      每个异常都有一个错误代码和与之关联的错误信息。 Oracle数据库提供了在处理异常时检索这些值的函数。（参见下表）

描述	如何获取
错误代码。当需要查找可能导致此类问题的通用信息时，此代码非常有用。	SQLCODE 注意: 不能在SQL语句中调用此函数
错误信息. 该文本通常包含特定于应用程序的数据，例如约束的名称或与问题关联的列。	SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK 注意: 不能在SQL语句中调用SQLERRM。
错误发生的行数。这个功能是在Oracle数据库10g版本2中添加的，在跟踪错误原因方面非常有用。	DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
执行调用堆栈。这就回答了问题“我是怎么来到这里的?”并向您显示代码在DBMS_UTILITY.FORMAT_CALL_STACK被调用的路径。	DBMS_UTILITY.FORMAT_CALL_STACK

二、异常抛出

       在大多数情况下，当应用程序中出现异常时，Oracle数据库将执行此操作。也就是说，在您的代码执行过程中出现了某种问题，您无法控制这个过程。一旦引发异常，您所能做的就是处理异常——或者让它“转移”，不被处理到主机环境中。

       然而，您也可以在自己的代码中引发异常。你为什么要这么做?因为并不是应用程序中的所有错误都是由于Oracle数据库实例内部处理失败造成的。还有一种可能是，某个数据条件构成了应用程序中的错误，在这种情况下，您需要停止对算法的处理，并且很可能会通知用户发生了错误。

PL / SQL提供了两种引发异常的机制：

• RAISE

• RAISE_APPLICATION_ERROR（内置存储过程）

1、RAISE

       可以使用RAISE语句引发用户定义的异常或Oracle数据库预定义的异常。在下面的示例中，如果用户为department ID提供了空值，将引发VALUE_ERROR异常:

CREATE OR REPLACE PROCEDURE 
process_department (
   department_id_in IN INTEGER)
IS
BEGIN
   IF department_id_in IS NULL
   THEN
      RAISE VALUE_ERROR;
   END IF;

2、RAISE_APPLICATION_ERROR

       RAISE语句引发一个异常，阻止当前块继续。还设置当前错误代码和错误信息。这个错误信息—例如“ORA-06502: PL/SQL:数值或值错误”—由Oracle数据库提供，通常是通用的。

       这种错误消息可能足以报告数据库错误，但是如果某个特定于应用程序的错误——例如“员工太年轻”或“工资不能超过1000美元”——有异常怎么办?“数值或数值错误”信息不会帮助用户理解他们做错了什么以及如何修复它。

       如果在发生错误时需要将特定于应用程序的消息传递回用户，则应该调用RAISE_APPLICATION_ERROR内置过程。此过程接受一个整数(您的错误代码)，其值必须介于-20,999和-20,000之间，以及一个字符串(您的错误消息)。

       下面是一个使用RAISE_APPLICATION_ERROR的示例:雇员必须至少满18岁。如果出生日期不符合，则抛出一个错误，以便停止插入或更新，并向用户返回一条信息。

CREATE OR REPLACE PROCEDURE 
validate_employee (
   birthdate_in IN DATE)
IS
BEGIN
   IF birthdate_in > 
      ADD_MONTHS (SYSDATE, -12 * 18)
   THEN
      RAISE_APPLICATION_ERROR (-20500
        , '雇员必须至少满18岁。'); 
   END IF;
END;

三、自定义异常

       可能有两个原因想要定义自己的异常(使用用户定义的异常):给一个没有被Oracle数据库分配名称的错误命名，或者定义一个特定于应用程序的异常，比如“金额值太小”错误，错误代码-20000。

CREATE OR REPLACE PROCEDURE 
process_balance (
   balance_in IN NUMBER)
IS
   e_balance_too_low   EXCEPTION;
   PRAGMA EXCEPTION_INIT (
      e_balance_too_low, -20000);
BEGIN
   IF balance_in < 1000
   THEN
      RAISE e_balance_too_low;
   END IF;
END;

四、异常处理

       Oracle数据库可能会引发内部或预定义的异常，您还可以显式地引发为应用程序定义的异常。接下来，您需要决定您的程序如何处理或处理这个异常。
       如果您不希望异常在处理之前离开您的块或子程序，那么您必须包含一个异常部分来捕获异常。异常部分以关键字exception开头，然后包含一个或多个WHEN子句。WHEN子句可以指定单个异常(通过名称)，多个异常用OR连接或则任何异常。

1. 捕获 NO_DATA_FOUND 异常， 通常在执行SELECT-INTO语句并没有找到行时引发。

      WHEN NO_DATA_FOUND 
      THEN 

2. 捕获 NO_DATA_FOUND 或 DUP_VAL_ON_INDEX 预定义异常。

      WHEN NO_DATA_FOUND OR
           DUP_VAL_ON_INDEX
      THEN 

3. 捕获任何异常

      WHEN OTHERS 
      THEN

异常部分可以有多个WHEN子句，但是如果你有一个WHEN OTHERS子句，它必须在最后。

       定义一个或多个WHEN子句很容易。异常部分的棘手部分是在捕获异常后决定要做什么。通常，异常处理程序中的代码应执行以下两个步骤：

       1、在某种日志中记录错误信息，通常是数据库表。

        2、抛出相同或不同的异常，因此将未处理的异常传播到外部块。

重新引用异常

       您可以简单地记录关于错误的信息，然后不重新检查异常。这种方法的问题是您的应用程序已经“吞噬”了一个错误。用户(或正在运行的脚本)将不知道存在问题。在某些情况下，这可能是可以的，但是非常罕见。在几乎所有发生错误的情况下，您却是需要确保运行引发错误的代码的人员或作业得到通知。

       Oracle数据库使得使用RAISE语句很容易做到这一点。如果在可执行部分中使用RAISE，则必须指定要引发的异常。

       RAISE NO_DATA_FOUND;

       但是在异常处理程序中，您也可以使用RAISE而不使用任何异常。

       RAISE;

错误记录

       通过将存储过程声明为一个自治事务，我可以提交或回滚对这个过程中的表所做的任何更改，而不会影响在会话中所做的其他更改。因此，现在我可以将新行保存到错误日志中，以后的业务事务回滚不会消除这些信息。

       异常记录存储过程作为自治事务提交

CREATE OR REPLACE PROCEDURE record_error
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_code   PLS_INTEGER := SQLCODE;
   l_mesg VARCHAR2(32767) := SQLERRM; 
BEGIN
   INSERT INTO error_log (error_code
                        ,  error_message
                        ,  backtrace
                        ,  callstack
                        ,  created_on
                        ,  created_by)
        VALUES (l_code
              ,  l_mesg 
              ,  sys.DBMS_UTILITY.format_error_backtrace
              ,  sys.DBMS_UTILITY.format_call_stack
              ,  SYSDATE
              ,  USER);
   COMMIT;
END;

通过定义的这个日志记录存储过程，可以非常轻松快速地编写异常处理程序，如下所示：

EXCEPTION
   WHEN OTHERS
   THEN
      record_error();
      RAISE;

声明时异常

       如果在块的声明部分中引发异常，则异常将传播到外部块。换句话说，块的异常部分只能捕获块的可执行部分中引发的异常。

       以下块包含WHEN OTHERS处理程序，它应该捕获块中引发的任何异常并只显示错误代码：

DECLARE
  l_number   NUMBER (1) := 100;
BEGIN
  statement1;
  ...
  statementN;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLCODE);
END;

       当执行块时，Oracle数据库将尝试将值100赋给l_number。但是，因为它被声明为NUMBER(1)，所以100不会“适合”这个变量。因此，Oracle数据库将导致ORA-06502错误，这是PL/SQL中预定义的VALUE_ERROR错误。

       因为异常是在声明变量的过程中引发的，所以异常处理程序不会捕捉到这个错误。相反，我将看到一个未处理的异常:

ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2 

       因此，应该避免在声明部分中为变量赋值，除非您确定不会引发错误。相反，您可以在可执行部分中赋值，然后异常处理程序可以捕获并记录错误:

DECLARE
  l_number   NUMBER (1);
BEGIN
  l_number := 100;

  statement1;
  ...
  statementN;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLCODE);
END;

五、异常和回滚

       未处理的异常不会自动导致会话中未处理的更改回滚。实际上，除非将ROLLBACK语句显式地编码到异常部分或异常传播未处理到主机环境，否则不会发生回滚。让我们看一个例子。

假设我编写了一个执行两种数据操作语言（DML）操作的代码块：

1.从Employees表中删除部门20中的所有员工。

2.通过将当前薪水乘以200来向所有剩余员工加薪。

        但是工资列上的约束被定义为数字(8,2)。一些员工的工资已经非常高，新的工资金额将会违反这个约束，导致Oracle数据库提高了“ORA-01438:值大于该列允许的指定精度”错误。

假设我在SQL * Plus会话中运行以下块：

BEGIN
   DELETE FROM employees
         WHERE department_id = 20;
   UPDATE employees
      SET salary = salary * 200;
EXCEPTION
   WHEN OTHERS
   THEN
      DECLARE
         l_count   PLS_INTEGER;
      BEGIN
         SELECT COUNT (*)
           INTO l_count
           FROM employees
          WHERE department_id = 20;

         DBMS_OUTPUT.put_line (l_count);
         RAISE;
      END;
END;

       删除操作成功完成，但是Oracle数据库在尝试执行UPDATE语句时引发了ORA-01438错误。我捕获错误并显示Employees表中department_id = 20的行数。会显示“0”，因为UPDATE语句失败不会导致会话回滚。

       然而，在我显示计数之后，我重新抛出相同的异常。因为没有封闭块，并且此最外层块以未处理的异常终止，所以此块中所做的任何更改都将由数据库回滚。

       因此，在运行此块后，部门20中的员工仍将在表中。