Oracle -- plsql Exception Handling

What is an exception?
Errors in Oracle are usually divided into compile-time errors and run-time errors ), an exception is a warning or error that occurs during PL/SQL Execution. • How is an exception triggered? -When an Oracle error occurs-use the RAISE statement to explicitly trigger • How to handle the exception? -Intercept with a processor-propagation of exceptions in the call environment: an Oracle error will automatically trigger related exceptions. You can use the RAISE statement in the block to explicitly trigger an exception. The triggered exception can be a predefined or custom exception. If some exceptions are triggered during block execution, the current block will be transferred to the corresponding exception processor for exception handling. If the PL/SQL statement successfully handles the exception, it will not spread the exception to the external block or environment, and the PL/SQL block ends normally. If an exception is triggered when the execution part of the block is triggered, but no exception processor is available, the block will terminate abnormally and pass the exception to the calling environment. When an exception is thrown, the control is passed to the exception handler to handle the exception. capture exceptions: [plain] exception when exception1 [OR exception2...] THEN statement1; -www.2cto.com-statement2 ;... [WHEN exception3 [OR exception4...] THEN statement1; statement2;...] [when others then statement1; statement2;...] • There is no limit on the number of WHEN clauses in the EXCEPTION section • when others is the last clause • The EXCEPTION Handling Section starts with the keyword EXCEPTION • WHEN an EXCEPTION is thrown, control unconditional transfer to Exception Handling Section • only one exception can be executed before leaving the block to handle a predefined exception: a predefined exception is a common error in Oracle. It is incorrectly pre-defined and does not need to be explicitly declared. Reference the standard error name in the corresponding exception handling routine to capture an Oracle Server predefined error.

Example: [plain] DECLARE v_sal emp. sal % type; begin select sal INTO v_sal FROM emp WHERE empno = 999; exception when NO_DATA_FOUND then partition ('no data found '); WHEN others then dbms_output.put_line ('other exception '); END; -www.2cto.com-to intercept an Oracle server error that is not predefined, you must first declare the error or use the OTHERS processor. Non-predefined exception: 1. Declare the Exception name in the Declaration section. Syntax: exception EXCEPTION exception; where: exception EXCEPTION name 2. use the PRAGMAEXCEPTION_INIT statement to associate the exception handling name with the error code of Oracle. syntax: PRAGMA EXCEPTION_INIT (exception, error_number); where: exception previously declared exception name error_number standard Oracle error code 3. reference declared exceptions in the corresponding exception handling routine. The keyword PRAGMA (pseudo-command pseudo doinstructions) indicates that the statement is a compilation command and is not processed when PL/SQL blocks are executed. In the PL/SQL block, a compilation command EXCEPTION_INIT tells the compiler to associate the name of an exception handling with an Oracle error code. [Plain] DECLARE e_emp_cons EXCEPTION; PRAGMA EXCEPTION_INIT (e_emp_cons,-00001); begin insert into emp SELECT * FROM emp; exception when e_emp_cons THEN limit ('violation of uniqueness constraint '); END; -www.2cto.com-function for capturing exceptions: • SQLCODE returns the Oracle error code • SQLERRM returns the information associated with the error value SQLCODE value description 0 no exception 100 NO_DATA_FOUND abnormal negative other Oracle error code custom exception: declaration in the Declaration section of PL/SQL blocks. Use the RAISE statement to publish it explicitly. Exception transfer; when the sub-block handles the exception by itself, it can be terminated normally, and the control can be handed over to the external block immediately after the END statement of the sub-block. However, if PL/SQL encounters an exception but the current block does not have a processor for the exception, it will find whether the external block has a processor, if no external block can handle this exception, an unhandled exception will occur in the host environment. When an exception is transmitted to an external block, the code waiting for execution in the current block is no longer executed. The advantage of this method is that the internal block only handles its own unique errors and leaves the general exception handling to the external block. Raise_Application_Error process-used to create a user-defined error message-returns an error to the user in the same format as other Oracle errors-this can be used in the executable section, you can also use Raise_Application_Error (error_number, message) in the exception section ); -The error number must be between-20000 and-20999-the length of the error message can be up to 2048 bytes -www.2cto.com-a non-standard error code and error message will be returned during the use of RAISE_APPLICATION_ERRO, this allows you to interact with a predefined exception. You can use RAISE_APPLICATION_ERROR to report error messages to the application and avoid returning exceptions that have not been processed. [Plain] execution region: BEGIN... Delete from emp where demtno = 10; if SQL % NOTFOUND then Raise_Application_Error (-20202, 'This is not a valid department '); end if ;... EXCEPTION region: EXCEPTION when NO_DATA_FOUND then Raise_Application_Error (-20202, 'This is not a valid department '); END; author shaojie519