[bbk5355]第18集 Chapter 08 Handling Exceptions(01)

Exception Types

• Predefined Oracle Server                                                   --Implicitly raised
• Non-predefined Oracle Server                                            --Implicitly raised
• User-defined　　　　　　　　　　　　　　　　　　　　　　　　--Explicitly raised

Syntax to Trap Exceptions

EXCEPTION

        WHEN exception1 [OR exception2...] THEN
                statement1;
                statement2;
        [WHEN exception3[OR exception4...] THEN
                statement1;
                statement2;
                ...]
        [WHEN OTHERS THEN
                statement1;
                statement2;
                ...]

不需要加break，只要找到处理的句柄，执行完成后，就会自动跳出.

Guidelines for Trapping Exceptions

• The EXECEPTION keyword starts the exception-handling section.
• Several exception handlers are allowed.
• Only one handler is prcessed before leaving the block.
• WHEN OTHERS is the last clause to act as the handler for all exceptions not named specifically.You use the WHEN OTHERS handler to guarantee that no execepion is unhandled.

Trapping Predefined Oracle Server Errors

• Reference the predefined name in the exception-handling routine. 
• Sample predefined execptions:
  • -NO_DATA_FOUND
  • -TOO_MANY_ROWS
  • -INVALID_CURSOR
  • -ZERO-DIVIDE
  • -DUP_VAL_ON_INDEX

Trapping Non-Predefined Oracle Server Errors

Example:

To trap Oracle Server error 01400("cannot insert NULL");

DECLARE
        e_insert_excep EXCEPTION;
        /*
        PRAGMA EXCEPTION_INIT(e_insert_excep,-01400)功能:
        将异常名称e_insert_excep与error code -01400进行关联起来，以后直接引用此名称就相当于引用此代码;
        */
        PRAGMA EXCEPTION_INIT(e_insert_excep,-01400);
BEGIN
        INSERT INTO dept(department_id,department_name) VALUES (280,NULL);
EXCEPTION
        WHEN e_insert_excep THEN
                DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILD');
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/
SQL> @2
INSERT OPERATION FAILD
ORA-01400: cannot insert NULL into ("HR"."DEPT"."DEPARTMENT_NAME")

PL/SQL procedure successfully completed.

EXCEPTION_INIT Directive

• EXCEPTION_INIT is a complie-time command or gragma used to associate a name with an internal error code.
• EXCEPTION_INIT instructs the compiler to associate an identifier,declared as an EXCEPTION,with a specific error number.

Syntax:

DECLARE

　　exception_name EXCEPTION;

　　PRAGMA EXCEPTION_INIT(exception_name,integer);

Functions for Trapping Exceptions

• SQLCODE:Returns the numeric value for the error code.
• SQLERRM:Returns the message associated with the erro number.

孪生兄弟，并行出现；值被自动填充.

SQLCODE、SQLERRM是2个函数.

Functions for Trapping Exceptions

DECLARE
        error_code      NUMBER;
        error_message   VARCHAR2(255);
BEGIN
...
EXCEPTION
...
        WHEN OTHERS THEN
                ROLLBACK;
                error_code := SQLCODE;
                error_messsage :=SQLERRM;
                INSERT INTO errors(e_user,e_date,error_code,error_message)
                VALUES(USER,SYSDATE,error_code,error_message);
END;

/

通过上述demo延伸问题：

当BEGIN section里面的语句发生异常的时候，此时进入到EXCEPTION section,此时有ROLLBACK clause，但是紧接着又有INSERT 日志文件的语句发起了一个新的事务，那么此时就有必要进行指明ROLLBACK到哪一个事务，不ROLLBACK到即将用到的事务：