[bbk5364]第19集 - Chapter 08 - Handling Exceptions(02)

Autonomous Transactions

Autonomous Transaction(AT)，通过PRAGMA AUTONOMOUS_TRANSACTION;指明当前的事务操作只针对自身有效，不涉及到其他事务.

Trapping User-Defined Exceptions

Examples:

DECLARE
        v_deptno NUMBER := 500;
        v_name   VARCHAR2(20) := 'Testing';
        e_invalid_department EXCEPTION;
BEGIN
        UPDATE dept SET department_name = v_name WHERE department_id = v_deptno;

        IF SQL%NOTFOUND THEN
                RAISE e_invalid_department;
        END IF;

        COMMIT;
EXCEPTION
        WHEN e_invalid_department THEN
                DBMS_OUTPUT.PUT_LINE('No such department id!');
END;

/

Propagating Exceptions in PL/SQL

• When an exception is raised,if PL/SQL cannot find a handler for it in the current block or subprogram ,the exception propagates.
• That is ,the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to seache.
• If no handler is found,PL/SQL returns an unhandled exception error to the host enviroment.

Propagating Exceptions in a Subblock

Propagating Rules:Example 1

Propagaing Rules:Example 2

Propagating Rules:Example3

RAISE_APPLICATION_ERROR Procedure

Syntax:

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

• You can use this procedure to issue user-defined error messages from stored subprograms.
• You can report errors to your applicaiton and avoid returning unhandled exceptions.
• error_number is in the range-20000..-20999,message is a character string of at most 2-48 bytes.

RAISE_APPLICATION_ERROR Procedure

• Is used in two different places:
  • -Executable section
  • -Exception section
• Returns error conditions to the user in a manner consistent with other Oracle Server errros.

Executable section:

Exception section:

Example:

DECLARE
        v_deptno NUMBER := 500;
        v_name VARCHAR2(20) := 'Testing';

        e_invalid_department EXCEPTION;
        PRAGMA EXCEPTION_INIT(e_invalid_department,-20188);
BEGIN
        UPDATE dept SET department_name = v_name WHERE department_id = v_deptno;

        IF SQL%NOTFOUND THEN
                RAISE_APPLICATION_ERROR(-20188,'This is your Error Message!');
        END IF;

        COMMIT;

EXCEPTION
        WHEN e_invalid_department THEN

                --DBMS_OUTPUT.PUT_LINE(SQLCODE || '->' || SQLERRM);
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/

解析：

e_invalid_department EXCEPTION;                                                --定义一个EXCEPTION类型的变量，名称叫做e_invalid_department;

PRAGMA EXCEPTION_INIT(e_invalid_department,-20188);              --通过此函数指令，将-20188与异常类型变量e_valid_department进行关联;

RAISE_APPLICATION_ERROR(-20188,'This is your Error Message!'); --通过此过程，就可以将ERROR CODE: -20188与ERROR MESSAGE:'This is your Error Message进行关联'；

完成上述几步之后，在后面的代码块中，就可以使用Oracle Server build in`s function:SQLCODE and SQLERRM;