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??-AOracleError-UseRAISEExplicit statement triggering
•How to handle exceptions?-Intercepted by processor-Exception Propagation in the call Environment

Two methods to trigger an exception:

Occurred Oracle Errors 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.

Interception exception

If an exception occurs in the execution part of the block, the current block will be transferred to the corresponding exception processor in the execution part. 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.

Abnormal Propagation

If a part of the block execution exception is triggered, but there is no corresponding exception processor, 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:

1. EXCEPTION
2. WHEN exception1 [OR exception2...] THEN
3. Statement1;
4. Statement2;
5. ...
6. [WHEN exception3 [OR exception4...] THEN
7. Statement1;
8. Statement2;
9. ...]
10. [WHEN OTHERS THEN
11. Statement1;
12. Statement2;
13. ...]

• In exception Section WHEN No limit on the number of clauses • WHEN OTHERS Is the last clause • Exception Handling part from the keyword EXCEPTION Start • When an exception is thrown, the control is unconditionally transferred to the exception handling section. • Only one exception can be processed before leaving the block.
Predefined exceptions:

Predefined exceptions are pre-defined by Oracle for Common Errors and do 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:

1. DECLARE
2. V_sal emp. sal % type;
3. BEGIN
4. SELECT sal INTO v_sal
5. FROM emp
6. WHERE e-mapreduce = 999;
7. EXCEPTION
8. WHEN NO_DATA_FOUND then
9. Dbms_output.put_line ('no data found ');
10. WHEN others then
11. Dbms_output.put_line ('other exception ');
12. END;

To intercept an Oracle server without a predefined error, you must first declare the error or use the OTHERS processor.

Non-predefined exceptions:

1. Declare the Exception name in the Declaration section.

Syntax:

Exception 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.

1. DECLARE
2. E_emp_cons EXCEPTION;
3. PRAGMA EXCEPTION_INIT (e_emp_cons,-00001 );
4. BEGIN
5. Insert into emp
6. SELECT * FROM emp;
7. EXCEPTION
8. WHEN e_emp_cons THEN
9. Dbms_output.put_line ('violation of uniqueness constraint ');
10. END;

Function for capturing exceptions:

• SQLCODE

ReturnOracleError Code

• SQLERRM

Returns the information associated with the error value.

SQLCODEValue description

0No exception

100 NO_DATA_FOUNDException

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 transmission;

When the sub-block handles exceptions 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_ErrorProcess
-Used to create user-defined error messages -Returns an error to the user in the format and otherOracleThe format of the error is the same. -It can be used in the executable part or in the exception part.

Raise_Application_Error (error_number, message );

-The error number must be-20000And-20999Between -Maximum error message length2048Bytes

The RAISE_APPLICATION_ERRO process returns a non-standard error code and error message 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.

1. Execution region:
2. BEGIN
3. ...
4. Delete from emp where demtno = 10;
5. If SQL % NOTFOUND then
6. Raise_Application_Error (-20202,
7. 'This is not a valid department ');
8. End if;
9. ...
10. Exception area:
11. EXCEPTION
12. When NO_DATA_FOUND then
13. Raise_Application_Error (-20202,
14. 'This is not a valid department ');
15. END;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12