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

1. PL/SQL exception handling 1.1. Compile alarm 1.1.1. Set alarm

1. Set the parameter PLSQL_WARNINGS and record the alarm

Session level enables all alarm types

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'

Turn off all alarms at the session level

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'

Enable SEVERE warnings and disable PERFORMANCE warnings. PLW-06002 alarm is an error

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',' ERROR:06002'

2. Record alarm through dbms alarm package

CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL',' SESSION')

Check alarm settin

SELECT DBMS_WARNING.get_warning_setting_string () FROM DUAL

1.1.2. test

Create a stored procedure

CREATE OR REPLACE PROCEDURE unreachable_code AS

X CONSTANT BOOLEAN: = TRUE

BEGIN

IF x THEN

DBMS_OUTPUT.PUT_LINE ('TRUE')

ELSE

DBMS_OUTPUT.PUT_LINE ('FALSE')

END IF

END unreachable_code

/

Open alarm record

CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL',' SESSION')

Recompile

ALTER PROCEDURE unreachable_code COMPILE

View error

Show errors:

1.2. Overview of exception handling

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.

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 two main 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. 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.

There are two types of user-defined errors.

? Exception defined by internal error code

? User-defined error code exception

1.3. Predefined exception handling

Some ORACLE exception errors in predefined instructions

Error number

Exception error message name

Description

ORA-0001

Dup_val_on_index

Violates the limit of uniqueness

ORA-0051

Timeout-on-resource

Timeout occurred while waiting for resources

ORA-0061

Transaction-backed-out

Undone due to deadlock transaction

ORA-1001

Invalid-CURSOR

Attempt 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

SELECT INTO did not find the data

ORA-1422

Too_many_rows

SELECT INTO returns multiple rows

ORA-1476

Zero-divide

Try to divide by zero

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 variables and PL/SQL variables have incompatible row types

ORA-6511

CURSOR-already-OPEN

An attempt was made to open a cursor that is already open

ORA-6530

Access-INTO-null

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

ORA-6531

Collection-is-null

Attempt to apply a collection method other than Exists to an null pl/sql table or varray

ORA-6532

Subscript-outside-limit

References to nested or varray indexes are outside the scope of the declaration

ORA-6533

Subscript-beyond-count

The reference to a nested or varray index is greater than the number of elements in the collection.

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.

1.4. User-defined exception handling

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.

1.4.1. Exception definition and scope of exception

Exception definition

DECLARE

Past_due EXCEPTION

BEGIN

NULL

END

/

The scope of the exception

The exception must be defined in the current block, the global exception can be rehandled in the sub-block, the exception in the sub-block can only be handled in the sub-block, and the exception definition in the sub-block can be propagated to the upper block, but the sub-block needs to trigger the exception again.

DECLARE

Past_due EXCEPTION

Acct_num NUMBER

BEGIN

DECLARE-sub-block begins

Past_due EXCEPTION;-this declaration prevails

Acct_num NUMBER

Due_date DATE: = SYSDATE-1

Todays_date DATE: = SYSDATE

BEGIN

IF due_date

< todays_date THEN RAISE past_due; -- this is not handled END IF; END; -- sub-block ends EXCEPTION -- Does not handle raised exception WHEN past_due THEN DBMS_OUTPUT.PUT_LINE ('Handling PAST_DUE exception.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Could not recognize PAST_DUE_EXCEPTION in this scope.'); END; / 1.4.2. 内部错误码定义的异常 EXCEPTION; PRAGMA EXCEPTION_INIT(, ); DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN NULL; -- Some operation that causes an ORA-00060 error EXCEPTION WHEN deadlock_detected THEN NULL; -- handle the error END; / 1.4.3. 用户定义的错误代码异常 EXCEPTION; 触发异常 RAISE ； 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。 举例 DECLARE out_of_stock EXCEPTION; number_on_hand NUMBER := 0; BEGIN IF number_on_hand < 1 THEN RAISE out_of_stock; END IF; EXCEPTION WHEN out_of_stock THEN DBMS_OUTPUT.PUT_LINE('Out-of-stock error.'); END; / 1.5.异常的传播1.6.异常的重新触发 异常必须在当前块定义，全局异常可以在子块重处理，子块中的异常只在只在子块中处理，子块中的异常定义可以传播给上层块，但是子块需要重新触发异常，上层块才能处理相同的异常。 DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; erroneous_salary NUMBER; BEGIN BEGIN -- sub-block begins IF current_salary >

Max_salary THEN

RAISE salary_too_high;-raise the exception

END IF

EXCEPTION

WHEN salary_too_high THEN

-- first step in handling the error

DBMS_OUTPUT.PUT_LINE ('Salary' | | erroneous_salary | |'is out of range.')

DBMS_OUTPUT.PUT_LINE ('Maximum salary is' | | max_salary | |'.')

RAISE;-reraise exception

END;-sub-block ends

EXCEPTION

WHEN salary_too_high THEN

-- handle error more thoroughly

Erroneous_salary: = current_salary

Current_salary: = max_salary

DBMS_OUTPUT.PUT_LINE (

'Revising salary from' | | erroneous_salary | |

'to' | | current_salary | |'.'

);

END

/

1.7. Exception handling

If there is an error in the definition, close the block directly. There is an error in the exception, when the subsequent operation.

1.7.1. Exception handling-record error codes and error messages

Displaying SQLCODE and SQLERRM

Create a tabl

CREATE TABLE errors (

Code NUMBER

Message VARCHAR2 (64)

Happened TIMESTAMP

);

DECLARE

Name EMPLOYEES.LAST_NAME%TYPE

V_code NUMBER

V_errm VARCHAR2 (64)

BEGIN

SELECT last_name INTO name

FROM EMPLOYEES

WHERE EMPLOYEE_ID =-1

EXCEPTION

WHEN OTHERS THEN

V_code: = SQLCODE

V_errm: = SUBSTR (SQLERRM, 1,64)

DBMS_OUTPUT.PUT_LINE

('Error code' | | v_code | |':'| | v_errm)

INSERT INTO errors (code, message, happened)

VALUES (v_code, v_errm, SYSTIMESTAMP)

Commit

END

/

1.7.2. Exception handling-continue execution after exception departure

CREATE TABLE employees_temp AS

SELECT employee_id, salary, commission_pct

FROM employees

DECLARE

Sal_calc NUMBER (8 dint 2)

BEGIN

INSERT INTO employees_temp (employee_id, salary, commission_pct)

VALUES (301, 2500, 0)

SELECT (salary / commission_pct) INTO sal_calc

FROM employees_temp

WHERE employee_id = 301

INSERT INTO employees_temp VALUES (302, sal_calc/100, .1)

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE (sqlcode | | sqlerrm)

Null

END

/

1.7.3. Exception handling-restart the transaction

DROP TABLE results

CREATE TABLE results (

Res_name VARCHAR (20)

Res_answer VARCHAR2 (3)

);

CREATE UNIQUE INDEX res_name_ix ON results (res_name)

INSERT INTO results (res_name, res_answer)

VALUES ('SMYTHE',' YES')

INSERT INTO results (res_name, res_answer)

VALUES ('JONES',' NO')

DECLARE

Name VARCHAR2 (20): = 'SMYTHE'

Answer VARCHAR2 (3): = 'NO'

Suffix NUMBER: = 1

BEGIN

FOR i IN 1.. 5 LOOP-- try 5 times

BEGIN-sub-block begins

SAVEPOINT start_transaction

-- Remove rows from a table of survey results:

DELETE FROM results WHERE res_answer = 'NO'

-- Add a survey respondent's name and answers:

INSERT INTO results (res_name, res_answer)

VALUES (name, answer)

-- If two respondents have same name, raise DUP_VAL_ON_INDEX.

COMMIT

EXIT

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO start_transaction;-undo changes

Suffix: = suffix + 1;-- try to fix problem

Name: = name | | TO_CHAR (suffix)

END;-sub-block ends

END LOOP

END

/

1.7.4. Exception handling-locating variables to identify the location of the exception

CREATE OR REPLACE PROCEDURE loc_var AS

Stmt_no NUMBER

Name_ VARCHAR2 (100)

BEGIN

Stmt_no: = 1;-- designates 1st SELECT statement

SELECT table_name INTO name_

FROM user_tables

WHERE table_name LIKE 'ABC%'

Stmt_no: = 2;-- designates 2nd SELECT statement

SELECT table_name INTO name_

FROM user_tables

WHERE table_name LIKE 'XYZ%'

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('Table name not found in query' | | stmt_no)

END

/

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.