oracle資料異常處理--抛出特定異常(轉)

1.        在PL/SQL 塊的定義部分定義異常情況：

<異常情況>  EXCEPTION;

2.        将其定義好的異常情況，與标準的ORACLE錯誤聯系起來，使用EXCEPTION_INIT語句：

      PRAGMA EXCEPTION_INIT(<異常情況>, <錯誤代碼>)；

3.        在PL/SQL 塊的異常情況處理部分對異常情況做出相應的處理。

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT

To handle error conditions (typically

ORA-

messages) that have no predefined name, you must use the

OTHERS

handler or the pragma

EXCEPTION_INIT

. A pragma is a compiler directive that is processed at compile time, not at run time.

In PL/SQL, the pragma

EXCEPTION_INIT

tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

You code the pragma

EXCEPTION_INIT

in the declarative part of a PL/SQL block, subprogram, or package using the syntax

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

      

where

exception_name

is the name of a previously declared exception and the number is a negative value corresponding to an

ORA-

error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

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