PL/SQL异常处理

建站服务器 1. PL/SQL异常处理

创新互联公司专注于青海网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供青海营销型网站建设，青海网站制作、青海网页设计、青海网站官网定制、微信小程序服务，打造青海网络公司原创品牌,更为您提供青海网站排名全网营销落地服务。1.1.编译告警 1.1.1. 设置告警

1、设置参数PLSQL_WARNINGS，记录告警

会话级别启用所有告警类型

ALTER SESSION SET PLSQL_WARNINGS=\'ENABLE:ALL\';

会话级别关闭所有告警

ALTER SESSION SET PLSQL_WARNINGS=\'DISABLE:ALL\';

开启 SEVERE warnings, 关闭 PERFORMANCE warnings, PLW-06002 告警为错误

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

2、通过dbms告警包记录告警

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

检查告警设置

SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;

1.1.2. 测试

创建存储过程

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;

/

开启告警记录

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

重新编译

ALTER PROCEDURE unreachable_code COMPILE;

查看错误

Show errors:

1.2.异常处理概述

即使是写得最好的PL/SQL程序也会遇到错误或未预料到的事件。一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。任何ORACLE错误（报告为ORA-xxxxx形式的Oracle错误号）、PL/SQL运行错误或用户定义条件（不一写是错误），都可以。当然了，PL/SQL编译错误不能通过PL/SQL异常处理来处理，因为这些错误发生在PL/SQL程序执行之前。

ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。

有两大类类型的异常错误：

1、预定义 ( Predefined )错误

ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

2、用户定义(User_define) 错误

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

用户定义错误有分为两类。

? 内部错误码定义的异常

? 用户定义的错误代码异常

1.3.预定义异常处理

预定义说明的部分 ORACLE 异常错误

错误号

异常错误信息名称

说明

ORA-0001

Dup_val_on_index

违反了唯一性限制

ORA-0051

Timeout-on-resource

在等待资源时发生超时

ORA-0061

Transaction-backed-out

由于发生死锁事务被撤消

ORA-1001

Invalid-CURSOR

试图使用一个无效的游标

ORA-1012

Not-logged-on

没有连接到ORACLE

ORA-1017

Login-denied

无效的用户名/口令

ORA-1403

No_data_found

SELECT INTO没有找到数据

ORA-1422

Too_many_rows

SELECT INTO 返回多行

ORA-1476

Zero-divide

试图被零除

ORA-1722

Invalid-NUMBER

转换一个数字失败

ORA-6500

Storage-error

内存不够引发的内部错误

ORA-6501

Program-error

内部错误

ORA-6502

Value-error

转换或截断错误

ORA-6504

Rowtype-mismatch

宿主游标变量与 PL/SQL变量有不兼容行类型

ORA-6511

CURSOR-already-OPEN

试图打开一个已处于打开状态的游标

ORA-6530

Access-INTO-null

试图为null 对象的属性赋值

ORA-6531

Collection-is-null

试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上

ORA-6532

Subscript-outside-limit

对嵌套或varray索引得引用超出声明范围以外

ORA-6533

Subscript-beyond-count

对嵌套或varray 索引得引用大于集合中元素的个数.

对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可

1.4.用户定义的异常处理

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

1.4.1. 异常定义和异常的范围

异常定义

DECLARE

past_due EXCEPTION;

BEGIN

NULL;

END;

/

异常的范围

异常必须在当前块定义，全局异常可以在子块重处理，子块中的异常只在只在子块中处理，子块中的异常定义可以传播给上层块，但是子块需要重新触发异常。

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.异常的处理

定义中出现错误，直接关闭块。异常中出现错误，后续操作时。

1.7.1. 异常处理---记录错误代码和错误消息

Displaying SQLCODE and SQLERRM

创建表

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. 异常处理—异常出发后继续执行

CREATE TABLE employees_temp AS

SELECT employee_id, salary, commission_pct

FROM employees;

DECLARE

sal_calc NUMBER(8,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. 异常处理—重新开始事务

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. 异常处理—定位变量标识异常位置

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;

/

分享名称：PL/SQL异常处理
文章链接：http://cqcxhl.cn/article/cpoheg.html