Shulou(Shulou.com)06/01 Report--

Even the best-written PL/SQL programs encounter errors or unexpected events. A good program should be able to handle all kinds of errors correctly and recover from them as much as possible. Any ORACLE error (reported as an Oracle error number in the form of ORA-xxxxx), PL/SQL run error, or user-defined condition (multiple writes are errors) is fine. Of course, PL/SQL compilation errors cannot be handled by PL/SQL exception handling because they occur before the PL/SQL program executes.

ORACLE provides exception case (EXCEPTION) and exception handling (EXCEPTION HANDLER) for error handling.

1 the concept of exception handling

Exception handling (EXCEPTION) is used to handle unexpected events during normal execution, and the exception handling of the program block handles predefined errors and custom errors. Because once the PL/SQL block produces an exception and does not indicate how to handle it, the program will automatically terminate the whole program.

There are three types of exception errors:

1. Predefined (Predefined) error

There are about 24 predefined exceptions in ORACLE. The handling of this exception does not need to be defined in the program and is automatically thrown by ORACLE.

2. Unpredefined (Predefined) error

That is, other standard ORACLE errors. The handling of this exception requires the user to define it in the program, and then ORACLE automatically throws it.

3. User defined (User_define) error

During the execution of the program, the programmer thinks that the abnormal situation occurs. Handling of this exception requires the user to define it in the program and then explicitly throw it in the program.

The exception handling part is usually placed in the second half of the PL/SQL program body, which is structured as follows:

EXCEPTION

WHEN first_exception THEN

WHEN second_exception THEN

WHEN OTHERS THEN

END

Exception handling can be arranged in any order, but OTHERS must be placed last.

1 predefined exception handling

Some ORACLE exception errors in predefined instructions

All error codes and descriptions are available in $ORACLE_HOME/rdbms/mesg/oraus.msg

To handle this exception, you only need to refer to the corresponding exception name directly in the exception handling part of the PL/SQL block, and complete the corresponding exception error handling.

Example 1: update the salary of the designated employee, if the salary is less than 1500, add 100th

D:\ instantclient_12_2 > sqlplus hr/hr@pdbtest

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 21 09:55:15 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Wed Dec 20 2017 10:59:38 + 08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

SQL > set serveroutput on

SQL > DECLARE

2 v_empno employees.employee_id%TYPE: = & empno

3 v_sal employees.salary%TYPE

4 BEGIN

5 SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno

6 IF v_sal DECLARE

2 v_deptno departments.department_id%TYPE: = & deptno

3 deptno_remaining EXCEPTION

4 PRAGMA EXCEPTION_INIT (deptno_remaining,-2292)

5 /-2292 is an error code that violates the consistency constraint.

6 BEGIN

7 DELETE FROM departments WHERE department_id = v_deptno

8 EXCEPTION

9 WHEN deptno_remaining THEN

10 DBMS_OUTPUT.PUT_LINE ('violation of data integrity constraints!')

11 WHEN OTHERS THEN

12 DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

13 END

14 /

Enter value for deptno: 50

Old 2: v_deptno departments.department_id%TYPE: = & deptno

New 2: v_deptno departments.department_id%TYPE: = 50

Violation of data integrity constraints!

PL/SQL procedure successfully completed.

3 user-defined exception handling

The exception error is implicitly triggered when an error associated with an exception error occurs. User-defined exception errors are triggered by the explicit use of RAISE statements. When an exception error is thrown, control goes to the exception error section of the EXCEPTION block and executes the error handling code.

For handling this type of exception, the steps are as follows:

1. Define the exception in the definition section of the PL/SQL block:

EXCEPTION

2. RAISE

3. In the exception handling section of the PL/SQL block, the exception is handled accordingly.

Example 1: update the salary of the designated employee, increase by 100

SQL > DECLARE

2 v_empno employees.employee_id%TYPE: = & empno

3 no_result EXCEPTION

4 BEGIN

5 UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno

6 IF SQL%NOTFOUND THEN

7 RAISE no_result

8 END IF

9 EXCEPTION

10 WHEN no_result THEN

11 DBMS_OUTPUT.PUT_LINE ('your data update statement failed!')

12 WHEN OTHERS THEN

13 DBMS_OUTPUT.PUT_LINE (SQLCODE | |'- -'| | SQLERRM)

14 END

15 /

Enter value for empno: 5000

Old 2: v_empno employees.employee_id%TYPE: = & empno

New 2: v_empno employees.employee_id%TYPE: = 5000

Your data update statement failed!

PL/SQL procedure successfully completed.

4 user-defined exception handling

Calling the RAISE_APPLICATION_ERROR procedure defined by the DBMS_STANDARD (package provided by ORACLE) package redefines the exception error message, which provides an application with a way to interact with ORACLE.

The syntax of RAISE_APPLICATION_ERROR is as follows:

RAISE_APPLICATION_ERROR (error_number,error_message, [keep_errors]); where error_number is a parameter from-20000 to-20999, and error_message is the corresponding prompt (< 2048 bytes)

Keep_errors is optional, and if keep_errors = TRUE, the new error will be added to the list of errors that have been raised. If keep_errors=FALSE (the default), the new error replaces the current error list.

Example 1: define triggers and use RAISE_APPLICATION_ERROR to prevent the insertion of new records without employee names:

CREATE OR REPLACE TRIGGER tr_insert_emp

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

IF: new.first_name IS NULL OR: new.last_name is null THEN

RAISE_APPLICATION_ERROR (- 20000 training employee must have a name.')

END IF

END

5. Abnormal error propagation

Because exception errors can occur in the declaration and execution sections, as well as in the exception errors section, the exception errors thrown in different parts are not the same.

Throw an exception error in the execution part

When an exception error is thrown in the execution section, the following occurs:

If the current block sets handling for the exception error, it executes and completes the execution of the block successfully, and then control transfers to the containing block.

If no handler is defined for the current block exception error setting, the exception error is propagated by throwing it in the containing block. Then perform step 1 on the containing block.

Throw an exception error in the declaration section

If an exception occurs in the declaration section, that is, an error occurs in the declaration section, then the error can affect other blocks. For example, there are the following PL/SQL programs:

DECLARE

Name varchar2 (12): = 'EricHu'

Other statements

BEGIN

Other statements

EXCEPTION

WHEN OTHERS THEN

Other statements

END

Exception error handling programming

In general application handling, programmers are advised to use exception handling, because if no exception handling is declared in the program, the program will be terminated when something goes wrong, and no information will be prompted. The following is an example of programming using system-provided exceptions.

Using SQLCODE, SQLERRM exception handlers in PL/SQL

Since the maximum length of the error message of ORACLE is 512 bytes, in order to get the complete error message, we can use SQLERRM and SUBSTR function to get the error message together, which is convenient for error, especially if WHEN OTHERS exception handler.

SQLCODE returns the Oracle error number encountered

SQLERRM returns the Oracle error message encountered.

Such as: SQLCODE=-100 è SQLERRM='no_data_found'

SQLCODE=0 è SQLERRM='normal, successfual completion'

Example 1. Store the ORACLE error code and its information in the error code table

CREATE TABLE errors (errnum NUMBER 4, errmsg VARCHAR2 100)

DECLARE

Err_msg VARCHAR2 (100)

BEGIN

/ get all ORACLE error messages /

FOR err_num IN-100.. 0 LOOP

Err_msg: = SQLERRM (err_num)

INSERT INTO errors VALUES (err_num, err_msg)

END LOOP

END

DROP TABLE errors

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.