建站服务器

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异常处理-风君雪科技博客