ORACLE Exception Handling tutorial, oracle tutorial

ORACLE Exception Handling tutorial, oracle tutorial

The main content of this article is as follows:

5.1 exception handling concepts

5.1.1 pre-defined exception handling

5.1.2 non-predefined Exception Handling

5.1.3 user-defined exception handling

5.1.4 user-defined exception handling

5.2 Error Propagation

5.2.1 exception errors in execution

5.2.2 Exception error in declaration part

5.3 exception handling programming

5.4 SQLCODE and SQLERRM exception handling functions are used in PL/SQL

Even the best-written PL/SQL program will encounter errors or unexpected events. A good program should be able to correctly handle all kinds of errors and recover from errors as much as possible. Any ORACLE errors (Oracle error numbers in the form of ORA-xxxxx are reported), PL/SQL running errors, or user-defined conditions (not necessarily written as errors. Of course, PL/SQL compilation errors cannot be handled through PL/SQL Exception Handling, because these errors occur before PL/SQL program execution.

ORACLE provides EXCEPTION and exception handler for error handling.

5.1 exception handling concepts

EXCEPTION Handling (EXCEPTION) is used to handle unexpected events during normal execution. The EXCEPTION handling of a program block is a predefined error or custom error, once an exception occurs in the PL/SQL block, the program automatically terminates the entire program.

There are three types of exception errors:

1. Predefined Error

There are about 24 predefined ORACLE exceptions. To handle this exception, you do not need to define it in the program. ORACLE will automatically throw it.

2. Predefined Error

That is, other standard ORACLE errors. To handle this exception, you need to define it in the program, and then the ORACLE will automatically cause it.

3. user defined (User_define) Error

During program execution, the programmer may think that the program is abnormal. To handle this exception, you need to define it in the program and then explicitly raise it in the program.

The exception handling part is generally placed in the back half of the PL/SQL program body. The structure is:

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 at the end.

5.1.1 pre-defined exception handling

Some ORACLE exception errors in the predefined description

Error Code

Error message name

Description

ORA-0001

Dup_val_on_index

The Uniqueness restriction is violated.

ORA-0051

Timeout-on-resource

Timeout while waiting for resources

ORA-0061

Transaction-backed-out

The deadlock transaction is canceled.

ORA-1001

Invalid-CURSOR

Try to use an invalid cursor

ORA-1012

Not-logged-on

Not connected to ORACLE

ORA-1017

Login-denied

Invalid username/password

ORA-1403

No_data_found

No data found in SELECT

ORA-1422

Too_many_rows

Select into returns multiple rows

ORA-1476

Zero-pide

Try to be division by zero

ORA-1722

Invalid-NUMBER

An error occurred while converting 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

The host cursor variable is incompatible with the PL/SQL variable.

ORA-6511

CURSOR-already-OPEN

Attempts to open a cursor that is already in the open state

ORA-6530

Access-INTO-null

An attempt is made to assign a value to the property of a null object.

ORA-6531

Collection-is-null

Try to apply the collection method other than Exists to a null pl/SQL table or varray

ORA-6532

Subscript-outside-limit

Reference to nested or varray indexes is beyond the declared range

ORA-6533

Subscript-beyond-count

Reference to nested or varray indexes is greater than the number of elements in the set.

To handle this exception, you only need to directly reference the corresponding Exception name in the Exception Handling Section of the PL/SQL block and handle the corresponding Exception error.

Example 1: update the specified employee's salary. If the employee's salary is smaller than 1500, the employee's salary is increased by 100;

DECLARE

V_empno employees. employee_id % TYPE: = & empno;

V_sal employees. salary % TYPE;

BEGIN

SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;

IF v_sal <= 1500 THEN

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

DBMS_OUTPUT.PUT_LINE ('Code: '| v_empno |' employee salary updated! ');

ELSE

DBMS_OUTPUT.PUT_LINE ('Code: '| v_empno |' the employee's salary has exceeded the specified value! ');

End if;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('the database is not encoded as '| v_empno |' employee ');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ('program running error! Use the cursor ');

WHEN OTHERS THEN

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

END;

5.1.2 non-predefined Exception Handling

To handle such exceptions, you must first define non-defined ORACLE errors. The procedure is as follows:

Define exceptions in the definition section of PL/SQL blocks:

<EXCEPTION> EXCEPTION;

Associate the defined exception with a standard ORACLE error and use the EXCEPTION_INIT statement:

PRAGMA EXCEPTION_INIT (<exception status>, <error code> );

Handle exceptions in PL/SQL blocks.

Example 2: Delete the records of a specified department to ensure that the Department has no employees.

Insert into attributes VALUES (50, 'Finance ', 'Chicago ');

DECLARE

V_deptno parameters. department_id % TYPE: = & deptno;

Deptno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT (deptno_remaining,-2292 );

/*-2292 is an error code that violates consistency constraints */

BEGIN

Delete from orders ments WHERE department_id = v_deptno;

EXCEPTION

WHEN deptno_remaining THEN

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

WHEN OTHERS THEN

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

END;

5.1.3 user-defined exception handling

When an error related to an exception occurs, it is implicitly triggered. A user-defined exception error is triggered by explicitly using the RAISE statement. When an EXCEPTION error is thrown, the control switches to the EXCEPTION part of the EXCEPTION block and runs the error handling code.

To handle such exceptions, follow these steps:

1. Define exceptions in the definition section of PL/SQL blocks:

<EXCEPTION> EXCEPTION;

2. RAISE <exception>;

3. handle exceptions in PL/SQL blocks.

Example 3: update the salary of a specified employee, increasing by 100;

DECLARE

V_empno employees. employee_id % TYPE: = & empno;

No_result EXCEPTION;

BEGIN

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

If SQL % NOTFOUND THEN

RAISE no_result;

End if;

EXCEPTION

WHEN no_result THEN

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

WHEN OTHERS THEN

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

END;

5.1.4 user-defined exception handling

You can call the RAISE_APPLICATION_ERROR process defined in the DBMS_STANDARD (ORACLE package) package to redefine the error message. This 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]);

Here error_number is the parameter from-20,000 to-20,999,

Error_message is the corresponding prompt information (<2048 bytes ),

Keep_errors is optional. If keep_errors is set to TRUE, a new error is added to the list of errors that have been thrown. If keep_errors = FALSE (default), the new error will replace the current error list.

Example 4: Create a get_salary function to retrieve the total salary of a specified department. The-20991 and-20992 errors are defined, handle two types of errors: Empty parameter and invalid department code:

Create table errlog (

Errcode NUMBER,

Errtext CHAR (40 ));

Create or replace function get_salary (p_deptno NUMBER)

RETURN NUMBER

AS

V_sal NUMBER;

BEGIN

IF p_deptno IS NULL THEN

RAISE_APPLICATION_ERROR (-20991, 'department code is null ');

ELSIF p_deptno <0 THEN

RAISE_APPLICATION_ERROR (-20992, 'invalid Department Code ');

ELSE

Select sum (employees. salary) INTO v_sal FROM employees

WHERE employees. department_id = p_deptno;

RETURN v_sal;

End if;

END;

DECLARE

V_salary NUMBER (7,2 );

V_sqlcode NUMBER;

V_sqlerr VARCHAR2 (512 );

Null_deptno EXCEPTION;

Invalid_deptno EXCEPTION;

PRAGMA EXCEPTION_INIT (null_deptno,-20991 );

PRAGMA EXCEPTION_INIT (invalid_deptno,-20992 );

BEGIN

V_salary: = get_salary (10 );

DBMS_OUTPUT.PUT_LINE ('10 Department salary: '| TO_CHAR (V_salary ));

BEGIN

V_salary: = get_salary (-10 );

EXCEPTION

WHEN invalid_deptno THEN

V_sqlcode: = SQLCODE;

V_sqlerr: = SQLERRM;

Insert into errlog (errcode, errtext)

VALUES (v_sqlcode, v_sqlerr );

COMMIT;

END inner1;

V_salary: = get_salary (20 );

DBMS_OUTPUT.PUT_LINE ('salary of Department number 20: '| TO_CHAR (V_salary ));

BEGIN

V_salary: = get_salary (NULL );

END inner2;

V_salary: = get_salary (30 );

DBMS_OUTPUT.PUT_LINE ('salary of Department number 30 is: '| TO_CHAR (V_salary ));

EXCEPTION

WHEN null_deptno THEN

V_sqlcode: = SQLCODE;

V_sqlerr: = SQLERRM;

Insert into errlog (errcode, errtext) VALUES (v_sqlcode, v_sqlerr );

COMMIT;

WHEN OTHERS THEN

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

END outer;

Example 5: Define a trigger and use RAISE_APPLICATION_ERROR to prevent insertion of new employee 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, 'employee must have a name .');

End if;

END;

5.2 Error Propagation

Exception errors can occur in the declaration part and execution part, and in the exception part, the exception errors are different.

5.2.1 exception errors in execution

When an exception is thrown in the execution part, the following situations occur:

L if the current block is set to handle this exception error, execute it and successfully complete the execution of the block, and then transfer the control to the inclusion block.

L if no processor is defined for the current block Exception error, An Exception error will be transmitted by raising it in the contained block. Then, perform Step 1 on the included block ).

5.2.2 Exception error in declaration part

If an exception occurs in the declaration part, that is, an error occurs in the declaration part, this error can affect other blocks. For example, the following PL/SQL program is available:

DECLARE

Name varchar2 (12): = 'erichu ';

Other statements

BEGIN

Other statements

EXCEPTION

WHEN OTHERS THEN

Other statements

END;

In this example, the when others then statement is not executed even though the when others then statement is described in EXCEPTION because Abc number (3) = 'abc. However, if an exception error exists outside the block, the error can be caught, for example:

BEGIN

DECLARE

Name varchar2 (12): = 'erichu ';

Other statements

BEGIN

Other statements

EXCEPTION

WHEN OTHERS THEN

Other statements

END;

EXCEPTION

WHEN OTHERS THEN

Other statements

END;

5.3 exception handling programming

In general application processing, it is recommended that the program personnel use exception handling, because if the program does not declare any exception handling, the program will be terminated when an error occurs during the program running, no information is displayed. The following is an example of programming with exceptions provided by the system.

5.4 SQLCODE and SQLERRM exception handling functions are used in PL/SQL

The maximum length of ORACLE error messages is 512 bytes. To obtain the complete error message, use the SQLERRM and SUBSTR functions to get the error message to facilitate the error, especially if the when others exception processor is more convenient.

The Oracle error code returned by SQLCODE,

SQLERRM returns the Oracle error message.

For example, SQLCODE =-100? SQLERRM = 'no _ data_found'

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

Example 6: store the ORACLE error code and 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;

Example 7: query the ORACLE error code;

BEGIN

Insert into employees (employee_id, first_name, last_name, hire_date, department_id)

VALUES (2222, 'Eric ', 'hu', SYSDATE, 20 );

DBMS_OUTPUT.PUT_LINE ('data inserted record successful! ');

Insert into employees (employee_id, first_name, last_name, hire_date, department_id)

VALUES (2222, 'hu ', 'yong', SYSDATE, 20 );

DBMS_OUTPUT.PUT_LINE ('data inserted record successful! ');

EXCEPTION

WHEN OTHERS THEN

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

END;

Example 8: Use the ORACLE error code to write the Exception error handling code;

DECLARE

Empno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT (empno_remaining,-1 );

/*-1 is the error code that violates the unique constraints */

BEGIN

Insert into employees (employee_id, first_name, last_name, hire_date, department_id)

VALUES (3333, 'Eric ', 'hu', SYSDATE, 20 );

DBMS_OUTPUT.PUT_LINE ('data inserted record successful! ');

Insert into employees (employee_id, first_name, last_name, hire_date, department_id)

VALUES (3333, 'hu ', 'yong', SYSDATE, 20 );

DBMS_OUTPUT.PUT_LINE ('data inserted record successful! ');

EXCEPTION

WHEN empno_remaining THEN

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

WHEN OTHERS THEN

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

END;