【PL/SQL】异常处理

一、异常简介

·为了提高程序的健壮性，必须考虑异常情况，否则程序会被终止

·异常（Exception）是一种PL/SQL标识符

·通过在PL/SQL块中引入异常处理部分，可以捕获各种例外，并根据例外的情况进行相应的处理

二、异常分类

1.oracle提供的预定义异常

①预定义的异常是PL/SQL所提供的系统异常

②当PL/SQL应用程序违反oracle规则或系统限制时，会触发一个内部异常

③PL/SQL提供20多种预定义的异常

④常见的预定义异常：

ACCESS_INTO_NULL：对象未初始化异常

CASE_NOT_FOUND:WHEN子句中没有包含CASE的情况

COLLECTION_IS_NULL:集合未初始化异常

CURSOR_ALREADY_OPEN:重新打开游标时的异常

DUP_VAL_ON_INDEX:当在唯一索引对应的列上键入重复值时的异常

INVALID_CURSOR:在不合法的游标上执行操作的异常

INVALID_NUMBER:不能有效地将字符转变成数字的异常

NO_DATA_FOUND:没有数据返回的异常

TOO_DATA_ROWS:返回超多一行数据时的异常

ZERO_DIVIDE:使用数字值除0时的异常

SUBSCRIPT_OUTSIDE_LIMIT:元素下标为负的异常

SUBSCRIPT_BEYOND_COUNT:元素下标越界的异常

VALUE_ERROR:变量长度不足以容纳实际数据时的异常

LOGIN_DENIED:用户名和口令错误时的异常

NOT_LOGGED_ON:没有连接数据库时的异常

PROGRAM_ERROR:PL/SQL的内部问题，需要重新安装数据字典和PL/SQL系统包

ROWTYPE_MISMATCH:宿主游标变量和PL/SQL游标变量的返回类型不兼容时的异常

STORAGE_ERROR:超出内存空间和内存损坏时的异常

SYS_INVALID_ROWID:将字符串转化为ROWID时的异常

TIMEOUT_ON_RESOURCE:等待资源超时的异常

OTHERS:代表所有没有在前面被捕获的异常返回行数超过一行

实例：

①TOO_MANY_ROWS  在隐式游标处理时，select返回行数超过一行

SQL> DECLARE

  2    v_ename emp.ename%TYPE;

  3    v_sal emp.sal%TYPE;

  4  BEGIN

  5    SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE deptno=&n;

  6    dbms_output.put_line(v_ename||':'||v_sal);

  7  EXCEPTION

  8    WHEN too_many_rows THEN

  9      dbms_output.put_line('You return rows more than one!');

 10    WHEN OTHERS THEN

 11      dbms_output.put_line('Other''s error!');

 12  END;

 13  /

You return rows more than one!

PL/SQL procedure successfully completed

②NO_DATA_FOUND 在访问数据时，没有发现数据

SQL> DECLARE

  2    v_ename emp.ename%TYPE;

  3    v_sal emp.sal%TYPE;

  4  BEGIN

  5    SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=#

  6    dbms_output.put_line(v_ename||':'||v_sal);

  7  EXCEPTION

  8    WHEN no_data_found THEN

  9       dbms_output.put_line('No data found,Please input correct number!');

 10    WHEN OTHERS THEN

 11      dbms_output.put_line('Others''s error!');

 12  END;

 13  /

No data found,Please input correct number!

PL/SQL procedure successfully completed

③ZERO_DIVIDE 除数为零

SQL> DECLARE

  2    v_num1 NUMBER :=10;

  3    v_num2 NUMBER;

  4    v_num3 NUMBER;

  5  BEGIN

  6    v_num2:=&nn;

  7    v_num3:=v_num1/v_num2;

  8    dbms_output.put_line('Number is:'||v_num3);

  9  EXCEPTION

 10    WHEN zero_divide THEN

 11      dbms_output.put_line('Divisor is equal to zero,Please input correct Number!');

 12    WHEN OTHERS THEN

 13      dbms_output.put_line('Other''s error!');

 14   end;

 15  /

Divisor is equal to zero,Please input correct Number!

PL/SQL procedure successfully completed

2.自定义异常

①自定义异常的步骤

·定义异常名称： 类型exception

·举出异常（raise exception）

·捕获异常

实例：

SQL> DECLARE

  2    e_user_err  EXCEPTION;

  3    v_deptno  dept.deptno%TYPE :=&NO;

  4  BEGIN

  5    UPDATE dept SET loc='HongKong' WHERE deptno=v_deptno;

  6    IF SQL%NOTFOUND THEN

  7      RAISE e_user_err;

  8    END IF;

  9    COMMIT;

 10  EXCEPTION

 11    WHEN e_user_err THEN

 12      dbms_output.put_line('This department'||v_deptno||' is not in dept table,Please input corrent number!');

 13    END;

 14  /

This department99 is not in dept table,Please input corrent number!

PL/SQL procedure successfully completed

3.非预定义异常

①通过oracle ora-xxxx错误代码，建立异常捕获

SQL> DECLARE

  2    e_emp_remain EXCEPTION;

  3    PRAGMA EXCEPTION_INIT(e_emp_remain,-2292);

  4    v_deptno dept.deptno%TYPE :=&NO;

  5  BEGIN

  6    DELETE FROM dept WHERE deptno=v_deptno;

  7    COMMIT;

  8  EXCEPTION

  9    WHEN e_emp_remain THEN

 10      dbms_output.put_line('Don''t remove this'||v_deptno||' department!'||',This record is in emp!');

 11   WHEN OTHERS THEN

 12     dbms_output.put_line('Others error!');

 13   END;

 14  /

PL/SQL procedure successfully completed

 

SQL> /

Don't remove this10 department!,This record is in emp!

PL/SQL procedure successfully completed

 

②获取others中错误代码和错误信息

Sqlcode：oracle 错误代码

Sqlerrm：oracle 错误信息

SQL> CREATE TABLE ERRORS(CODE NUMBER,msg VARCHAR2(255));

SQL> DECLARE

  2    v_code errors.code%TYPE;

  3    v_msg errors.msg%TYPE;

  4    v_sal  emp.sal%TYPE;

  5  BEGIN

  6    SELECT sal INTO v_sal FROM emp WHERE deptno=&NO;

  7  EXCEPTION

  8    WHEN no_data_found THEN

  9      dbms_output.put_line('No this department ID');

 10      WHEN OTHERS THEN

 11        dbms_output.put_line('Others error');

 12        v_code :=SQLCODE;

 13        v_msg :=SQLERRM;

 14        dbms_output.put_line(v_msg);

 15      INSERT INTO ERRORS VALUES(v_code,v_msg);

 16      INSERT INTO ERRORS VALUES(v_code,v_msg);

 17      COMMIT;

 18   END;

 19  /

Others error

ORA-01422: 实际返回的行数超出请求的行数

PL/SQL procedure successfully completed

SQL> select * from errors;

      CODE MSG

---------- --------------------------------------------------------------------------------

     -1422 ORA-01422: 实际返回的行数超出请求的行数

     -1422 ORA-01422: 实际返回的行数超出请求的行数

     -1422 ORA-01422: 实际返回的行数超出请求的行数

     -1422 ORA-01422: 实际返回的行数超出请求的行数