第七章 控制PL/SQL错误(1)[PL/SQL 用户指南与参考]

 

一、错误控制一览

在PL/SQL中，警告或错误被称为异常。异常可以是内部(运行时系统)定义的或是用户定义的。内部定义的案例包括除零操作和内存溢出等。一些常见的内部异常都有一个预定义的名字，如ZERO_DIVIDE和STORAGE_ERROR等。对于其它的内部异常，我们可以手动为它们命名。

我们可以在PL/SQL块、子程序或包的声明部分自定义异常。例如，我们可以定义一个名为insufficient_funds的异常来标示帐户透支的情况。与内部异常不同的是，用户自定义异常必须有一个名字。

错误发生时，异常就会被抛出。也就是说，正常的执行语句会被终止，控制权被转到PL/SQL块的异常控制部分或子程序的异常控制部分。内部异常会由运行时系统隐式地抛出，而用户定义异常必须显式地用RAISE语句抛出，RAISE语句也可以抛出预定义异常。

为了控制被抛出的异常，我们需要单独编写被称为"exception handler"的异常控制程序。异常控制程序运行后，当前块就会停止执行，封闭块继续执行下一条语句。如果没有封闭块，控制权会直接交给主环境。

下例中，我们为一家股票代码(Ticker Symbol)为XYZ的公司计算并保存市盈率(price-to-earning)。如果公司的收入为零，预定义异常ZERO_DIVIDE就会被抛出。这将导致正常的执行被终止，控制权被交给异常控制程序。可选的OTHERS处理器可以捕获所有的未命名异常。

DECLARE    pe_ratio   NUMBER (3, 1); BEGIN   SELECT price / earnings     INTO pe_ratio     FROM stocks    WHERE symbol = 'XYZ';   -- might cause division-by-zero error   INSERT INTO stats (symbol, ratio)        VALUES ('XYZ', pe_ratio);   COMMIT; EXCEPTION   -- exception handlers begin   WHEN ZERO_DIVIDE THEN   -- handles 'division by zero' error     INSERT INTO stats (symbol, ratio)          VALUES ('XYZ', NULL);     COMMIT;      ...   WHEN OTHERS THEN   -- handles all other errors     ROLLBACK; END;   -- exception handlers and block end here

上面的例子演示了异常控制，但对于INSERT语句的使用就有些低效了。使用下面的语句就要好一些：

INSERT INTO stats (symbol, ratio)   SELECT symbol, DECODE (earnings, 0, NULL, price / earnings)     FROM stocks    WHERE symbol = 'XYZ';

在下面这个例子中，子查询为INSERT语句提供了数据。如果earnings是零的话，函数DECODE就会返回空，否则DECODE就会返回price与earnings的比值。

二、异常的优点

使用异常来控制错误有几个优点。如果没有异常控制的话，每次执行一条语句，我们都必须进行错误检查：

BEGIN   SELECT ...     -- check for ’no data found’ error   SELECT ...     -- check for ’no data found’ error   SELECT ...     -- check for ’no data found’ error

错误处理和正常的处理内容界限不明显，导致代码混乱。如果我们不编写错误检查代码，一个错误就可能引起其它错误，有时还可能是一些无关错误。

但有了异常后，我们就能很方便的控制错误，而且不需要编写多个检查代码：

BEGIN   SELECT ...   SELECT ...   SELECT ...    ... EXCEPTION   WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors

异常能把错误控制程序单独分离出来，改善可读性，主要的算法不会受到错误恢复算法影响。异常还可以提高可靠性。我们不需要在每一个可能出现错误的地方编写错误检查代码了，只要在PL/SQL块中添加一个异常控制代码即可。这样，如果有异常被抛出，我们就可以确保它能够被捕获并处理。

三、预定义PL/SQL异常

当我们的PL/SQL程序与Oracle规则相冲突或超过系统相关(system-dependent)的限制时，内部异常就会被抛出。每个Oracle错误都有一个错误编号，但异常只能按名称捕获，然后被处理。所以，PL/SQL把一些常见Oracle错误定义为异常。例如，如果SELECT INTO语句查询不到数据时，PL/SQL就会抛出预定义异常NO_DATA_FOUND。

要控制其它Oracle异常，我们可以使用OTHERS处理器。函数SQLCODE和SQLERRM在OTHERS处理器中特别有用，因为它们能返回Oracle错误编号和消息。另外，我们还可以使用编译指示(pragma)EXCEPTION_INIT把一个异常名称和一个Oracle错误编号关联起来。PL/SQL在STANDARD包中声明了全局预定义异常。所以，我们不需要自己声明它们。我们可以为下面列表中命名的预定义异常编写处理程序：

异常	Oracle错误号	SQLCODE值
ACCESS_INTO_NULL	ORA-06530	-6530
CASE_NOT_FOUND	ORA-06592	-6592
COLLECTION_IS_NULL	ORA-06531	-6531
CURSOR_ALREADY_OPEN	ORA-06511	-6511
DUP_VAL_ON_INDEX	ORA-00001	-1
INVALID_CURSOR	ORA-01001	-1001
INVALID_NUMBER	ORA-01722	-1722
LOGIN_DENIED	ORA-01017	-1017
NO_DATA_FOUND	ORA-01403	100
NOT_LOGGED_ON	ORA-01012	-1012
PROGRAM_ERROR	ORA-06501	-6501
ROWTYPE_MISMATCH	ORA-06504	-6504
SELF_IS_NULL	ORA-30625	-30625
STORAGE_ERROR	ORA-06500	-6500
SUBSCRIPT_BEYOND_COUNT	ORA-06533	-6533
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	-6532
SYS_INVALID_ROWID	ORA-01410	-1410
TIMEOUT_ON_RESOURCE	ORA-00051	-51
TOO_MANY_ROWS	ORA-01422	-1422
VALUE_ERROR	ORA-06502	-6502
ZERO_DIVIDE	ORA-01476	-1476

预定义异常的简要描述：

异常	抛出时机
ACCESS_INTO_NULL	程序尝试为一个未初始化(自动赋为null)对象的属性赋值。
CASE_NOT_FOUND	CASE语句中没有任何WHEN子句满足条件，并且没有编写ELSE子句。
COLLECTION_IS_NULL	程序尝试调用一个未初始化(自动赋为null)嵌套表或变长数组的集合方法(不包括EXISTS)，或者是程序尝试为一个未初始化嵌套表或变长数组的元素赋值。
CURSOR_ALREADY_OPEN	程序尝试打开一个已经打开的游标。一个游标在重新打开之前必须关闭。一个游标FOR循环会自动打开它所引用的游标。所以，我们的程序不能在循环内部打开游标。
DUP_VAL_ON_INDEX	程序尝试向一个有着唯一约束条件的数据库字段中保存重复值。
INVALID_CURSOR	程序尝试操作一个不合法的游标，例如关闭一个未打开的游标。
INVALID_NUMBER	在一个SQL语句中，由于字符串并不代表一个有效的数字，导致字符串向数字转换时会发生错误。(在过程化语句中，会抛出异常VALUE_ERROR。)当FETCH语句的LIMIT子句表达式后面不是一个正数时，这个异常也会被抛出。
LOGIN_DENIED	程序尝试使用无效的用户名和/或密码来登录Oracle。
NO_DATA_FOUND	SELECT INTO语句没有返回数据，或者是我们的程序引用了一个嵌套表中被删除了的元素或是索引表中未初始化的元素。SQL聚合函数，如AVG和SUM，总是能返回一个值或空。所以，一个调用聚合函数的SELECT INTO语句从来不会抛出NO_DATA_FOUND异常。FETCH语句最终会取不到数据，当这种情况发生时，不会有异常抛出的。
NOT_LOGGED_ON	程序没有连接到Oracle就要调用数据库。
PROGRAM_ERROR	PL/SQL程序发生内部错误。
ROWTYPE_MISMATCH	赋值语句中使用的主游标变量和PL/SQL游标变量的类型不兼容。例如，当一个打开的主游标变量传递到一个存储子程序时，实参的返回类型和形参的必须一致。
SELF_IS_NULL	程序尝试调用一个空实例的MEMBER方法。也就是内置参数SELF(它总是第一个传递到MEMBER方法的参数)是空。
STORAGE_ERROR	PL/SQL运行时内存溢出或内存不足。
SUBSCRIPT_BEYOND_COUNT	程序引用一个嵌套表或变长数组元素，但使用的下标索引超过嵌套表或变长数组元素总个数。
SUBSCRIPT_OUTSIDE_LIMIT	程序引用一个嵌套表或变长数组，但使用的下标索引不在合法的范围内(如-1)。
SYS_INVALID_ROWID	从字符串向ROWID转换发生错误，因为字符串并不代表一个有效的ROWID。
TIMEOUT_ON_RESOURCE	当Oracle等待资源时，发生超时现象。
TOO_MANY_ROWS	SELECT INTO语句返回多行数据。
VALUE_ERROR	发生算术、转换、截位或长度约束错误。例如，当我们的程序把一个字段的值放到一个字符变量中时，如果值的长度大于变量的长度，PL/SQL就会终止赋值操作并抛出异常VALUE_ERROR。在过程化语句中，如果字符串向数字转换失败，异常VALUE_ERROR就会被抛出。(在SQL语句中，异常INVALID_NUMBER会被抛出。)
ZERO_DIVIDE	程序尝试除以0。

四、自定义PL/SQL异常

PL/SQL允许我们定义自己的异常。与预定义异常不同的是，用户自定义异常必须声明，并且需要用RAISE语句显式地抛出。

1、声明PL/SQL异常

异常只能在PL/SQL块、子程序或包的声明部分声明。下例中，我们声明一个名为past_due的异常：

DECLARE    past_due EXCEPTION;

异常和变量的声明是相似的。但是要记住，异常是一种错误情况(error condition)，而不是数据项。与变量不同的是，异常不能出现在赋值语句或是SQL语句中。但是，变量的作用域规则也适用于异常。

2、PL/SQL异常的作用域规则

在同一个块内，异常不能声明两次。但可以在不同的块声明相同的异常。

块中声明的异常对于当前块来说是本地的，但对于当前块的所有子块来说是全局的。因为块只能引用本地或全局的异常，所以封闭块不能引用声明在子块中的异常。

如果我们在子块中重新声明了一个全局的异常，本地声明的异常的优先级是要高于全局的。所以，子块就不能引用全局的异常，除非全局异常在它的所在块中用标签作了标记，这种情况下可以使用下面的语法来引用全局异常：

block_label.exception_name

下例中演示了作用范围规则：

DECLARE    past_due   EXCEPTION;    acct_num   NUMBER; BEGIN   DECLARE   -- sub-block begins      past_due   EXCEPTION;   -- this declaration prevails      acct_num   NUMBER;   BEGIN      ...     IF ... THEN       RAISE past_due;   -- this is not handled     END IF;   END;   -- sub-block ends EXCEPTION   WHEN past_due THEN   -- does not handle RAISEd exception      ... END;

上例中的封闭块并不能捕获抛出来的异常，因为在子块中声明的past_due优先级要高于封闭块声明的异常。虽然它们的名字相同，但实际上是两个不同的past_due异常，就像两个acct_num变量只是共享着相同的名字一样，实际上它们是完全不同的两个变量。因此，RAISE语句和WHEN子句所引用的是不同的异常。如果想让封闭块能捕获到子块中的past_due异常，我们就必须从子块中删除声明，或是在封闭块中添加OTHERS处理器。

3、把PL/SQL异常与编号关联：编译指示EXCEPTION_INIT

要想控制没有预定义名称的错误(通常为 ORA- 消息)，我们就必须使用OTHERS处理器或编译指示EXCEPTION_INIT。编译指示就是能在编译期而非运行时进行处理的编译指令。

在PL/SQL中，编译指示EXCPTION_INIT能告诉编译器把异常名称和错误编号关联起来。这就能让我们按名称来引用所有的内部异常，并为它编写特定的处理程序。在我们看到的错误栈或是错误消息序列中，最顶层的就是我们能捕获和处理的信息。

我们可以把编译指示EXCEPTION_INIT写在PL/SQL块、子程序或包的声明部分，语法如下：

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

其中exception_name是已经声明过的异常名称，Oracle_error_number是Oracle错误编号。编译指示必须和异常声明处于同一个声明中，并且只能在异常声明之后出现。如下例所示：

DECLARE    deadlock_detected   EXCEPTION;   PRAGMA EXCEPTION_INIT (deadlock_detected, -60); BEGIN    ...   -- Some operation that causes an ORA-00060 error EXCEPTION   WHEN deadlock_detected THEN     -- handle the error      ... END;

4、自定我们自己的错误消息：过程RAISE_APPLICATION_ERROR

过程RAISE_APPLICATION_ERROR能帮助我们从存储子程序中抛出用户自定义的错误消息。这样，我们就能把错误消息报告给应用程序而避免返回未捕获异常。

调用RAISE_APPLICATION_ERROR的语法如下：

raise_application_error(error_number, message[, {TRUE | FALSE}]);

error_number是一个范围在-20000至-20999之间的负整数，message是最大长度为2048字节的字符串。如果第三个可选参数为TRUE的话，错误就会被放到前面错误的栈顶。如果为FALSE(默认值)，错误就会替代前面所有的错误。

RAISE_APPLICATION_ERROR是包DBMS_STANDARD的一部分，所以，我们对它的引用不需要添加限定修饰词。

应用程序只能从一个正在执行的存储子程序或方法中调用raise_application_error。在调用时，raise_application_error会结束子程序并把用户定义的错误编号和消息返回给应用程序。错误编号和消息可以像其它的Oracle错误一样被捕获。

在下面的例子中，我们在雇员工资栏的内容为空的情况下调用raise_application_error：

CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS    curr_sal   NUMBER; BEGIN   SELECT sal     INTO curr_sal     FROM emp    WHERE empno = emp_id;   IF curr_sal IS NULL THEN      /* Issue user-defined error message. */      raise_application_error (-20101, 'Salary is missing');   ELSE     UPDATE emp        SET sal = curr_sal + amount      WHERE empno = emp_id;   END IF; END raise_salary;

调用程序会得到一个PL/SQL异常，它能在OTHERS处理器中使用错误报告函数SQLCODE和SQLERRM来进行处理。同样，我们也可以使用编译指示EXCEPTION_INIT把raise_application_error返回的错误编号映射到异常本身。如下面的Pro*C例子所示：

EXEC SQL EXECUTE    /* Execute embedded PL/SQL block using host    variables my_emp_id and my_amount, which were    assigned values in the host environment. */ DECLARE    null_salary   EXCEPTION;    /* Map error number returned by raise_application_error    to user-defined exception. */   PRAGMA EXCEPTION_INIT (null_salary, -20101); BEGIN    raise_salary (:my_emp_id, :my_amount); EXCEPTION   WHEN null_salary THEN     INSERT INTO emp_audit          VALUES (:my_emp_id, ...); END; END-EXEC;

这项技术能让调用程序在特定的异常处理程序中控制错误。

5、重新声明预定义异常

请记住，PL/SQL把预定义的异常作为全局内容声明在包STANDARD中，所以，我们没有必要重新声明它们。重新声明预定义异常是错误的做法，因为我们的本地声明会覆盖掉全局声明。例如，如果我们声明了一个invalid_number，当PL/SQL抛出预定义异常INVALID_NUMBER时，我们为异常INVALID_NUMBER编写的异常控制程序就无法正确地捕获到它了。这种情况下，我们必须像下面这样使用点标志来指定预定义异常：

EXCEPTION   WHEN INVALID_NUMBER OR STANDARD.INVALID_NUMBER THEN     -- handle the error END;

五、如何抛出PL/SQL异常

内部异常会由运行时系统隐式地抛出，其中也包括使用编译指示EXCEPTION_INIT与Oracle错误编号关联起来的用户自定义异常。但是，用户自定义的异常就必须显式地用RAISE语句抛出。

1、使用RAISE语句抛出异常

PL/SQL块和子程序应该只在错误发生或无法完成正常程序处理的时候才抛出异常。下例中，我们用RAISE语句抛出一个用户自定义的out_of_stack异常：

DECLARE    out_of_stock     EXCEPTION;    number_on_hand   NUMBER (4); BEGIN    ...   IF number_on_hand < 1 THEN     RAISE out_of_stock;   END IF; EXCEPTION   WHEN out_of_stock THEN     -- handle the error END;

我们也可以显式地抛出预定义异常。这样，为预定义异常编写的处理程序也就能够处理其它错误了，示例如下：

DECLARE    acct_type   INTEGER := 7; BEGIN   IF acct_type NOT IN (1, 2, 3) THEN     RAISE INVALID_NUMBER;   -- raise predefined exception   END IF; EXCEPTION   WHEN INVALID_NUMBER THEN     ROLLBACK; END;