PL/SQL异常处理

PL/SQL异常处理

• 来源:网络
• 更新日期:2020-08-19

摘要：建站服务器 1. PL/SQL异常处理 1.1.编译告警 1.1.1. 设置告警 1、设置参数PLSQL_WA

建站服务器 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;

/