Pl-sql Programming Basics (4) Exception handling

Exception handling:

Even if a good Pl-sql program encounters errors or unforeseen events, a good program should be able to handle all kinds of error situations and recover as much as possible from the error. An error that occurs while the program is running becomes an exception. After an exception occurs, the statement terminates execution, and Plsql immediately gives control to the Plsql exception handling section. Oracle uses exception to handle exceptions, typically with 3 exception errors.

There are three types of exception errors:

1. Pre-defined (predefined) errors

There are approximately 24 pre-defined exception cases for Oracle. The handling of this exception does not have to be defined in the program and is automatically raised by Oracle.

2. Non-pre-defined (predefined) error

That is, other standard Oracle errors. Handling this exception requires the user to define it in the program, which is then automatically raised by Oracle.

3. User-defined (user_define) error

During the execution of the program, it appears that the programmer considers the abnormal situation. Handling this exception requires the user to define it in the program and then explicitly raise it in the program.

The exception handling section is generally placed in the second half of the PL/SQL program body:

Grammar:

EXCEPTION
When a first_exception then the first exception-handling code;
When Second_exception then the second exception-handling code
When OTHERS then other exception-handling code
END;

1. Pre-defined error:

Oracle's pre-defined exceptions are approximately 24 predefined exceptions in Oracle. As shown in the following table.

Error number	Exception error message name	Description
ORA-0001	Dup_val_on_index	Violation of uniqueness restrictions
ORA-0051	Timeout-on-resource	A timeout occurred while waiting for the resource
ORA-0061	Transaction-backed-out	The deadlock transaction has been undone because
ORA-1001	Invalid-cursor	An attempt was made to use an invalid cursor
ORA-1012	Not-logged-on	Not connected to Oracle
ORA-1017	Login-denied	Invalid user name/password
ORA-1403	No_data_found	SELECT into no data found
ORA-1422	Too_many_rows	SELECT into returns multiple rows
ORA-1476	Zero-divide	Tried to be 0 apart
ORA-1722	Invalid-number	Failed to convert a number
ORA-6500	Storage-error	Internal error caused by insufficient memory
ORA-6501	Program-error	Internal Error
ORA-6502	Value-error	Conversion or truncation Error
ORA-6504	Rowtype-mismatch	Host cursor variable has incompatible row type with PL/SQL variable
ORA-6511	Cursor-already-open	An attempt was made to open a cursor that is already in an open state
ORA-6530	Access-into-null	An attempt was made to assign a value to a null object's property
ORA-6531	Collection-is-null	An attempt was made to apply a collection (collection) method other than exists to a null PL/SQL table or Varray
ORA-6532	Subscript-outside-limit	References to nested or Varray indexes beyond the declared range
ORA-6533	Subscript-beyond-count	A reference to a nested or Varray index is greater than the number of elements in the collection.

Note: Pre-defined exceptions are first violated by Oracle's specification, followed by Oracle's name only for those more than 20 exceptions, such as the error number "ORA-01043 data not Found" is named "No_data_found", which is used in Plsql No_data_ Found to capture the processing.

Example:

DECLAREv_id Number;BEGIN    SELECTId intov_id fromEs_userWHERE 1=0;    Dbms_output.put_line (v_id); EXCEPTION whenToo_many_rows ThenDbms_output.put_line ('cannot assign multiple values of a query to a variable');  whenNo_data_found ThenDbms_output.put_line ('cannot pay a variable for a null value');  whenOTHERS ThenDbms_output.put_line ('Other Exceptions');END;

If the query statement returns more than one result, the exception-handling code in the too_many_rows is executed, and if no value is returned, the code that performs the no_data_found exception handling. Execute exception handling code in others if other errors occur

2. Non-pre-defined error

Non-predefined exceptions refer to other standard Oracle errors, which require the user to be defined in the program and then automatically raised by Oracle for this total exception handling scenario.

For this type of exception handling, you must first define a non-defined Oracle exception. The steps are as follows:

(1) In the Plsql Declaration section defines the exception condition.

DECLARE   fk_exception EXCEPTION;  --Define an exception

(2) associate its defined exception with the standard Oracle exception, using the Exception_init statement:

  PRAGMA Exception_init (fk_exception,-2291);  -- 2291 the error number defined for Oracle, which is a FOREIGN key constraint violation

(3) in the Plsql exception processing part of the exception to make corresponding treatment. (Full code)

DECLAREfk_exception EXCEPTION; PRAGMA Exception_init (fk_exception,-2291);--2291 the error number defined for Oracle, which is a FOREIGN key constraint violationBEGIN    UPDATEEs_orderSET user_id =  - WHEREId=1;--an exception is thrown when the user's ID is modified to a value that does not exist. EXCEPTION whenFk_exception ThenDbms_output.put_line ('the user does not exist');END;

　　A non-predefined exception, like a predefined exception, violates the specification of Oracle, but Oracle does not name the exception, such as the error number "ORA-2291", which does not have a name, so the exception variable is pre-defined in the exception section of the Plsql block.

3. User Custom Error

During the execution of the program, there is an abnormal situation that the programmer thinks, for this kind of exception, the user is required to define the exception in the program, and then display it in the program to be issued. User-defined exceptions are triggered by raise statements. When this exception is thrown, the program will specifically exception to handle the exception quickly.

For this exception handling, the steps are as follows:

(1) Declare the exception in the Declaration section

DECLARE   age_exception EXCEPTION;   -- declaring an exception

(2) Throwing exception information

IF < 0 OR >  -  Then         RAISE age_exception;
END IF;

(3) in the Plsql abnormal handling part of the abnormal situation to make corresponding treatment.

EXCEPTION      when  Then         dbms_output.put_line (' age is only between 0-100!  ');

Example: (Judging whether the age is between 0-100)

DECLAREV_age Number:= &Age ; Age_exception EXCEPTION;BEGIN    IFV_age< 0 ORV_age>  -  ThenRAISE age_exception; END IF; EXCEPTION whenAge_exception ThenDbms_output.put_line ('age can only be between 0-100! ');END;

Exception stored procedures:

In addition to the above 3 exception handling, the Raise_application_error stored procedure allows you to redefine the exception error message, which provides a way for the application to interact with Oracle.

Grammar:

Raise_application_error (Error_number,error_message);

Error_number: Represents the number specified by the user for the exception, which must be a negative integer between -20000~-20999.

Error_message: Represents the message text specified by the user for the exception. The message length can be up to 2048 bytes, and the error message is the text associated with the error_number.

Pl-sql Programming Basics (4) Exception handling