PL/SQL Exception Example

一个PL/SQL程序的错误可以分为两种：编译时刻错误、运行时刻错误。

 

编译时刻错误

由于用户输入（如表名拼写错误）、能力（语法格式错误）等方面的原因而造成的程序方面的错误就是编译时的错误。这些错误在编译时会被PL/SQL编译器发现。也可以查询数据字典USER_ERRORS、ALL_ERRORS、DBA_ERRORS来读取错误信息。

 

运行时刻错误

即便是通过了编译的PL/SQL程序在运行时刻还是可能产生错误的。导致运行时错误的原因很多，如内存用尽、硬件故障、违反表的完整性约束、设计上的缺陷、被0除、数字或值在长度大小方面不匹配等。

 

在程序内部不需要对编译时错误做特殊处理。而运行时错误可能随着运行环境的变化而随时出现，难以预防、检查、排除。所以为了提高程序的健壮性、处理潜在的错误，开发人员必须在程序中尽可能地考虑各种主要异常错误，并进行相应的处理。

 

异常分为系统预定义、非预定义、用户定义三种。另外，异常分为错误和非错误两种。系统预定义、非预定义的异常都是针对Oracle（发现的）错误的，而用户定义的异常一般用于处理非错误的异常。

 

 

预定义异常错误

可以查看${ORACLE_HOME}\RDBMS\ADMIN\stdspec.sql脚本文件可以找到这些预定义异常错误。

预定义异常错误虽然是被隐含引发的，但要在异常处理部分利用“异常处理名称”来处理相应的异常错误。

 

--NO_DATA_FOUND

DECLARE
   v_ename   emp.ename%TYPE;
   v_empno   emp.empno%TYPE;
BEGIN
   v_empno := &eno;

   SELECT ename
     INTO v_ename
     FROM emp
    WHERE empno = v_empno;

   DBMS_OUTPUT.put_line (
      '雇员编号：' || v_empno || '，雇员名称：' || v_ename);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (
         '雇员编号：' || v_empno || '，不存在，请重新输入');
END;
/

 

--CASE_NOT_FOUND

DECLARE
   v_grade   CHAR := 'D';
BEGIN
   CASE v_grade
      WHEN 'A'
      THEN
         DBMS_OUTPUT.put_line ('Excellent');
      WHEN 'B'
      THEN
         DBMS_OUTPUT.put_line ('Very good');
      WHEN 'C'
      THEN
         DBMS_OUTPUT.put_line ('Good');
   END CASE;
EXCEPTION
   WHEN case_not_found
   THEN
      DBMS_OUTPUT.put_line ('No such grade');
      DBMS_OUTPUT.put_line (
         '在CASE语句中缺少与 ' || 'D' || ' 相关的条件');
END;
/

 

--DUP_VAL_ON_INDEX

DECLARE
   v_dept   dept%ROWTYPE;
BEGIN
   INSERT INTO dept
        VALUES (40, '印刷厂', '北京海淀区');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      DBMS_OUTPUT.put_line (
         '违反了主键（部门编号）不能重复的实体完整性约束');
END;
/

 

--TOO_MANY_ROWS

DECLARE
   v_ename   emp.ename%TYPE;
BEGIN
   SELECT ename
     INTO v_ename
     FROM emp
    WHERE sal = &sal;
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line (
         '返回了多行，请使用游标来处理多行记录的集合');
END;
/

 

--OTHERS

BEGIN
   DBMS_OUTPUT.put_line (1 / 0);                            --会发生ZERO_DIVIDE异常
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      DBMS_OUTPUT.put_line (
         '违反了主键（部门编号）不能重复的实体完整性约束');
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line (
         '返回了多行，请使用游标来处理多行记录的集合');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('发生了其他异常');
END;
/

 

非预定义异常错误

--为错误代码为“-1400”的Oracle错误声明异常，并进行处理

DECLARE
   ept_null_error   EXCEPTION;
   PRAGMA EXCEPTION_INIT (ept_null_error, -1400);
BEGIN
   INSERT INTO dept
        VALUES (NULL, '印刷厂', '北京海淀区');
EXCEPTION
   WHEN ept_null_error
   THEN
      DBMS_OUTPUT.put_line ('不能向dept表的"部门编号"列插入NULL');
END;
/

 

自定义异常

预定义异常、非预定义异常都是有Oracle判断的异常错误。而在实际的PL/SQL程序开发过程中，为了实施具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。他们往往并不一定是个错误，也不会对数据库造成破坏性的影响，而是程序员为特殊情况所定义的异常，以便将程序的控制流程引向异常处理部分，利用异常处理部分来集中处理这些问题。这种方法使程序的结果比较完美。

自定义异常必须要声明，并且必须要用RAISE语句显示引发（或抛出），然后才能将程序的控制流程引向异常处理部分。

 

--用异常处理的方法来补充提示雇员的工资太高了

DECLARE
   v_empno   emp.empno%TYPE;
   v_sal     emp.sal%TYPE;
   ept_sal   EXCEPTION;
BEGIN
   v_empno := &eno;

   SELECT sal
     INTO v_sal
     FROM emp
    WHERE empno = v_empno;

   IF v_sal >= 4000
   THEN
      DBMS_OUTPUT.put_line (v_empno || ' 雇员的工资是 ' || v_sal);
      RAISE ept_sal;
   ELSE
      DBMS_OUTPUT.put_line (
         v_empno || ' 雇员的工资是 ' || v_sal || '，基本正常');
   END IF;
EXCEPTION
   WHEN ept_sal
   THEN
      DBMS_OUTPUT.put_line (
         '补充：该雇员的工资超过4000了，太高了');
END;
/

 

使用SQLCODE和SQLERRM

另一种处理异常（预定义、尤其是非预定义异常）的方法是在异常处理部分最后的WHEN OTHERS子句使用内置异常函数SQLCODE和SQLERRM，以便根据SQL语句执行后的状态，即错误代码和错误消息，决定处理方法。

SQLCODE函数没有参数，它返回Oracle的错误代码的数字。

SQLERRM(oracle_error_code)函数按照输入的Oracle错误代码oracle_error_code返回其对应的Oracle错误消息文本。当省略oracle_error_code时，SQLERRM函数返回SQLCODE当前值对应的错误消息文本。

 

--几个特殊错误代码时SQLERRM函数的返回值

BEGIN
   DBMS_OUTPUT.put_line ('SQLERRM(0): ' || SQLERRM (0));
   DBMS_OUTPUT.put_line ('SQLERRM(100): ' || SQLERRM (100));
   DBMS_OUTPUT.put_line ('SQLERRM(10): ' || SQLERRM (10));
   DBMS_OUTPUT.put_line ('SQLERRM(1): ' || SQLERRM (1));
   DBMS_OUTPUT.put_line ('SQLERRM: ' || SQLERRM);
   DBMS_OUTPUT.put_line ('SQLERRM(-1): ' || SQLERRM (-1));
   DBMS_OUTPUT.put_line ('SQLERRM(-54): ' || SQLERRM (-54));
END;
/

SQLCODE和SQLERRM函数均不能直接用在SQL语句中，如果需要在SQL语句中使用的话，需要先将其值赋予变量，然后再引用。

 

--用SQLCODE和SQLERRM函数来处理非预定义异常错误

BEGIN
   INSERT INTO dept
        VALUES (NULL, '印刷厂', '北京海淀区');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('没有找到数据');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLCODE || '、' || SQLERRM);

      CASE SQLCODE
         WHEN -1400
         THEN
            DBMS_OUTPUT.put_line ('不能向非空字段插入NULL值');
         WHEN -2291
         THEN
            DBMS_OUTPUT.put_line ('主表中没有主键值，不能更新');
         WHEN -2292
         THEN
            DBMS_OUTPUT.put_line ('还有子记录，不能删除父记录');
      END CASE;
END;
/