Oracle专题13之异常错误处理

1、异常处理的概念与分类

a、什么是异常？

• 异常：是程序在正常执行过程中发生的未预料的事件。

  b、什么是异常处理？

• 异常处理是为了提高程序的健壮性，使用异常处理部分可以有效地解决程序正常执行过程中可能出现的各种错误，使得程序正常运行。

  c、异常处理的语法格式

  EXCEPTION 
  WHEN first_exception THEN 
      statement1;
      ......
  WHEN second_exception THEN 
      statement1;
      ......
  WHEN OTHERS THEN
      statement1;
      ......

  d、异常处理代码的PL/SQL块中的位置

  DECLARE
      /*
      *  声明部分--声明变量、常量、复杂数据类型、游标等
      */
  BEGIN
      /*
      *  执行部分--PL/SQL语句和SQL语句
      */
  EXCEPTION
   /*
   *  异常处理部分--处理运行错误
   */
   END; --块结束标记

• 简单示例代码：查询指定部门的所有员工姓名和工资。

  DECLARE
      v_name emp.ename%TYPE;
      v_sal emp.sal%TYPE;
  BEGIN
      SELECT ename, sal INTO v_name, v_sal FROM emp  WHERE empno = &no;
      IF v_sal < 3000 THEN 
          dbms_output.put_line(v_name || '的工资是：' || v_sal);
          END IF;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('员工号输入错误！');
      WHEN OTHERS THEN 
          dbms_output.put_line('其他错误！');
  END;

  e、异常的分类

• 预定义异常、非预定义异常和自定义异常

  2、预定义异常

• 预定义异常是指由PL/SQL所提供的系统异常，Oracle提供了20多个预定义异常，每个预定义异常对应一个特定的Oracle错误，当PL/SQL块出现这些Oracle错误时，会隐含地触发相应的预定义异常。
  
• 对于预定义异常情况的处理，无需在程序中定义，只需要在PL/SQL块中的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。
• 示例代码：根据输入的工资，查询员工的姓名。并输出员工的姓名以及工资。

      DECLARE
          v_name emp.ename%TYPE;
          v_sal emp.sal%TYPE := &salary;
      BEGIN
          SELECT ename INTO v_name FROM emp WHERE sal = v_sal;
          DBMS_OUTPUT.put_line(v_name || '的工资是：' || v_sal);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.put_line('没有该工资的员工');
          WHEN TOO_MANY_ROWS THEN
              DBMS_OUTPUT.put_line('多个员工具有该工资');
          WHEN OTHERS THEN
              DBMS_OUTPUT.put_line('其他错误');
      END;

  3、非预定义异常

• 用于处理预定义异常所不能够处理的ORACLE错误，此种异常需要在程序中定义。

  a、非预定义异常的处理包括3步

  1、在PL/SQL块中定义部分定义异常情况：<异常情况> EXCEPTION;
  2、将其定义好的异常情况与标准的ORACLE错误联系起来，使用PRAGMA EXCEPTION_INIT语句：PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
  3、在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。

  b、代码演示

• 删除dept表中指定部分的信息。（删除部门时应该保证指定部门中没有员工，因为dept和emp表存在主外键关系）
  1、当删除有其员工的部门时，获取错误号：

  BEGIN
      DELETE FROM dept WHERE deptno = &deptno;
  EXCEPTION
      WHEN OTHERS THEN
          dbms_output.put_line(SQLCODE || '###' || SQLERRM);
  END;

• 当按F8执行，键入20后，控制台打印如下内容：

  -2292###ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录

  2、删除dept表中指定部分的信息：

  DECLARE
      --1、定义非预定义异常的标识符
      e_fk EXCEPTION;
      --2、把Oracle错误和异常信息建立关联
      -- -2292 违反外键约束的错误号
      PRAGMA EXCEPTION_INIT(e_fk, -2292);
  BEGIN
      DELETE FROM dept WHERE deptno = &deptno;
  EXCEPTION
      WHEN e_fk THEN
          -- 3、捕捉并处理异常
          dbms_output.put_line('此部门下有员工，不能删除！');
      WHEN OTHERS THEN
          dbms_output.put_line(SQLCODE || '###' || SQLERRM);
  END;   

• 当按下F8执行，键入20后，控制台打印如下内容：

  此部门下有员工，不能删除！

  4、自定义异常

• 如果你想在某个特定事件发生时向应用程序的用户发出一些警告信息。而事件本身不会抛出Oracle内部异常，这个异常是属于应用程序的特定异常。那么就需要自定义异常。
• 用户定义的异常错误时通过显式使用RAISE语句来触发的。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。

  a、自定义异常的处理步骤

• 对于这类异常情况的处理，步骤如下：
  1、在PL/SQL块的声明部分定义异常情况：<异常情况> EXCEPTION;
  2、RAISE <异常情况>
  3、在PL/SQL块的异常处理部分对异常情况做出相应的处理。

  b、示例代码

• 更新指定员工编号的员工工资：

  DECLARE
      v_empno emp.empno%TYPE := &empno;
      e_no_result EXCEPTION;
  BEGIN
      UPDATE emp SET sal = sal + 100 WHERE empno = v_empno;
      IF SQL%NOTFOUND THEN
          RAISE e_no_result;
      ELSE
          COMMIT;
      END IF;
  EXCEPTION
      WHEN e_no_result THEN
          dbms_output.put_line('数据更新失败！');
      WHEN OTHERS THEN 
          dbms_output.put_line('其他错误');
  END;

  5、SQLCODE和SQLERRM

  a、异常处理函数

• 异常处理函数用于取得Oracle错误号和错误信息，其中函数SQLCODE用于取得错误号，SQLERRM用于取得错误信息。
• 当编写PL/SQL块时，通过在异常处理部分引用函数SQLCODE和SQLERRM，可以取得未预计到的Oracle错误。
• 另外，通过使用内置过程RAISE_APPLICATION_ERROR，可以在建立子程序（过程、函数、包）时自定义错误号和错误信息。

  b、异常处理函数示例代码

• 插入员工信息（包括员工号、员工名和员工所属部门号）：

  DECLARE
      v_empno emp.empno%TYPE := &empno;
      v_ename emp.ename%TYPE := '&ename';
      v_deptno emp.deptno%TYPE := &deptno;
  BEGIN
      INSERT INTO emp(empno, ename, deptno) VALUES(v_empno, v_ename, v_deptno);
      IF SQL%FOUND THEN
          DBMS_OUTPUT.put_line('数据插入成功');
          COMMIT; 
      END IF;
  EXCEPTION
      WHEN OTHERS THEN
          DBMS_OUTPUT.put_line('错误号：' || SQLCODE);
          DBMS_OUTPUT.put_line('错误信息：' || SQLERRM);
  END;  

  c、RAISE_APPLICATION_ERROR

• 该过程用于在PL/SQL子程序中自定义错误信息。
• 语法格式为：

  raise_application_error(error_number, message);

• error_number：用于定义错误号（-20000~-20999）。
• message：用于指定错误信息，长度不能超过2048个字节。