• PL/SQL异常处理


    As we all known,程序的错误一般分为两类:编译错误和运行时错误。其中运行时错误被称为异常。PL/SQL语句块中处理异常的部分即为异常处理部分。在异常处理部分,可以指定当特定异常发生时所采取的动作。

    PL/SQL有两种类型的异常:内置异常和用户自定义异常。

    其中,内置异常又分为预定义异常和非预定义异常。

    一、内置异常

    首先试举一例来抛砖引玉。

    复制代码
    DECLARE
       v_ename varchar2(10);
       v_empno number(4) := &v_empno;
    BEGIN
       SELECT ename
         INTO v_ename
       FROM EMP
       WHERE empno = v_empno;
       DBMS_OUTPUT.PUT_LINE('Employee name is '||v_ename);
    END;
    复制代码

    该语句块通过输入员工的编号来得出员工的姓名。当输入的员工编号存在时,输出员工姓名,当员工编号不存在时,会有运行错误。如下所示:

    复制代码
    SQL> /
    Enter value for v_empno: 7788 
    Employee name is SCOTT              -->> 输入的员工编号存在,输出员工姓名
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    Enter value for v_empno: 1234
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found            -->> 输入的员工编号不存在,报运行时错误
    ORA-06512: at line 5
    复制代码

     由此可见,编译器无法检测运行错误。为在程序中处理这种类型的错误,必须添加异常处理部分。异常处理部分的语法结构如下:

    EXCEPTION
           WHEN EXCEPTION_NAME THEN
                 ERROR-PROCESSING STATEMENTS;

    在语句块中,异常处理部分位于可执行部分之后,上例可修改如下:

    复制代码
    DECLARE
       v_ename varchar2(10);
       v_empno number(4) := &v_empno;
    BEGIN
       SELECT ename
         INTO v_ename
       FROM EMP
       WHERE empno = v_empno;
       DBMS_OUTPUT.PUT_LINE('Employee name is '||v_ename);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('There is no such employee');
    END;
    复制代码

    使用异常处理部分,可以使得程序能够正常结束,而不是非正常终止。同时,输出结果更加面向用户,而不是编程人员。

    上述NO_DATA_FOUND即为Oracle预定义异常。

    下面列出一些常见的预定义异常:

    TOO_MANY_ROWS : SELECT INTO返回多行
    INVALID_CURSOR :非法指针操作(关闭已经关闭的游标)
    ZERO_DIVIDE :除数等于零
    DUP_VAL_ON_INDEX :违反唯一性约束
    ACCESS_INTO_NULL: 未定义对象 
    CASE_NOT_FOUND: CASE 中若未包含相应的 WHEN ,并且没有设置 ELSE 时 
    COLLECTION_IS_NULL: 集合元素未初始化 
    CURSER_ALREADY_OPEN: 游标已经打开 
    DUP_VAL_ON_INDEX: 唯一索引对应的列上有重复的值 
    INVALID_NUMBER: 内嵌的 SQL 语句不能将字符转换为数字 
    NO_DATA_FOUND: 使用 select into 未返回行,或应用索引表未初始化的元素时 
    SUBSCRIPT_BEYOND_COUNT:元素下标超过嵌套表或 VARRAY 的最大值 
    SUBSCRIPT_OUTSIDE_LIMIT: 使用嵌套表或 VARRAY 时,将下标指定为负数 
    VALUE_ERROR: 赋值时,变量长度不足以容纳实际数据 
    LOGIN_DENIED: PL/SQL 应用程序连接到 oracle 数据库时,提供了不正确的用户名或密码 
    NOT_LOGGED_ON: PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 
    PROGRAM_ERROR: PL/SQL 内部问题,可能需要重装数据字典& pl./SQL 系统包 
    ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 
    SELF_IS_NULL: 使用对象类型时,在 null 对象上调用对象方法 
    STORAGE_ERROR: 运行 PL/SQL 时,超出内存空间 
    SYS_INVALID_ID: 无效的 ROWID 字符串 
    TIMEOUT_ON_RESOURCE: Oracle 在等待资源时超时

    others可以代表所有异常,oracle预定义的异常在20000以内。

    二、 用户自定义异常

    通常,在自己的程序里,也许需要处理与所写程序相关的问题。例如,在上个语句块中,需要输入员工编号。通常,希望员工编号是正值。但是无意间,用户输入一个负数。但是,没有发生任何错误,因为变量v_empno被定义为数值类型。这时,你希望自定义异常来处理这种情况,这种类型的异常被称为用户自定义异常。在使用该异常之前,必须首先进行声明。语法结构如下所示:

    DECLARE
         exception_name EXCEPTION;
    BEGIN
         ...
         IF  CONDITION THEN
              RAISE exception_name;
         ELSE
              ...
         END IF;
    EXCEPTION
         WHEN exception_name THEN
               ERROR-PROCESSING STATEMENTS;
    END;

    故上例可修改为:

    复制代码
    DECLARE
       v_ename varchar2(10);
       v_empno number(4) := &v_empno;
       e_invalid_no exception;
    BEGIN
       IF v_empno < 0 THEN
            RAISE e_invalid_no;    -->> 注意:RAISE语句应该与IF语句一起使用,否则,每次执行时,执行权都会转到该语句块的异常处理部分
       ELSE
          SELECT ename
             INTO v_ename
          FROM EMP
          WHERE empno = v_empno;
          DBMS_OUTPUT.PUT_LINE('Employee name is '||v_ename);
       END IF;
    EXCEPTION
       WHEN e_invalid_no THEN
       DBMS_OUTPUT.PUT_LINE('Employee number can not be negative');
    END;
    复制代码

    三、RAISE_APPLICATION_ERROR

         RAISE_APPLICATION_ERROR是oracle提供的一种特殊的内置过程,允许编程人员为特定应用程序创建有意义的错误信息。RAISE_APPLICATION_ERROR过程适用于未命名的用户定义异常。它负责将错误编号和错误文本关联起来,它的语法为:

         RAISE_APPLICATION_ERROR(error_number,error_message);

         error_number是与特定错误信息相关联的错误编号。这个编号的范围在-20999到-20000之间。error_message是错误文本,最多包含2048个字符。

         上例可修改为:

    复制代码
    DECLARE
       v_ename varchar2(10);
       v_empno number(4) := &v_empno;
    BEGIN
       IF v_empno < 0 THEN
            RAISE_APPLICATION_ERROR(-20000,'Employee number can not be negative');
       ELSE
          SELECT ename
             INTO v_ename
          FROM EMP
          WHERE empno = v_empno;
          DBMS_OUTPUT.PUT_LINE('Employee name is '||v_ename);
       END IF;
    END;
    复制代码

    当输入的员工编号为负数时,运行结果如下所示:

    复制代码
    SQL> /
    Enter value for v_empno: -1234
    DECLARE
    *
    ERROR at line 1:
    ORA-20000: Employee number can not be negative
    ORA-06512: at line 6
    复制代码

    借助于RAISE_APPLICATION_ERROR过程,编程人员能够遵循与Oracle错误一致的方式返回错误信息。

    四、 EXCEPTION_INIT

    在上文内置异常中,预定义异常的个数其实是非常有限的,当程序抛出其它不在上述预定义范围内的异常时,该如何捕捉呢?

    譬如下例:

    DECLARE
       v_deptno  number(2) := &v_deptno;
    BEGIN
       DELETE FROM dept
        WHERE deptno= v_deptno;
    END;

    当部门编号输入10时,我们来看看运行结果:

    复制代码
    SQL> /
    Enter value for v_deptno: 10
    DECLARE
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
    ORA-06512: at line 4
    复制代码

    违反父键约束,但是,我们如何捕捉此种错误呢?在这里,我们可以用到EXCEPTION_INIT。

    使用EXCEPTION_INIT指令,可以将某Oracle错误编号和用户定义异常的名称建立关联。EXCEPTION_INIT指令出现在语句块的声明部分,如下所示:

    DECLARE
          exception_name EXCEPTION;
          PRAGMA EXCEPTION_INIT(exception_name,error_code);

    注意,用户定义异常的声明出现在所使用的EXCEPTION_INIT指令之前,EXCEPTION_INIT指令有两个参数:exception_name和error_code。exception_name是异常的名称,error_code是希望与该异常建立关联的Oralce错误编号。

    上例可修改为:

    复制代码
    DECLARE
       v_deptno  number(2) := &v_deptno;
       e_child_exists EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_child_exists,-2292);
    BEGIN
       DELETE FROM dept
        WHERE deptno= v_deptno;
    EXCEPTION
       WHEN e_child_exists THEN
         DBMS_OUTPUT.PUT_LINE('Delete employees for No.'||v_deptno||' dept first');
    END;
    复制代码

    同样将部门编号输入为10,来看看结果:

    SQL> /
    Enter value for v_deptno: 10
    Delete employees for No. 10 dept frist
    
    PL/SQL procedure successfully completed.

    可以正常捕捉错误!

    五、 SQLCODE和SQLERRM

    所以Oracle错误都可以使用OTHERS异常处理程序进行捕获和处理,如下例所示:

    复制代码
    DECLARE
      v_deptno number(4) := &v_deptno;
      v_dname  varchar2(5);
      v_loc    varchar2(10);
    BEGIN
      SELECT dname,loc
        INTO v_dname,v_loc
        FROM dept
      WHERE deptno = v_deptno;
      DBMS_OUTPUT.PUT_LINE(v_dname||','||v_loc);
    EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('An error has occurred');
    END;
    复制代码

    当输入10作为部门编号的值时,我们来看看输出:

    SQL> /
    Enter value for v_deptno: 10
    An error has occurred
    
    PL/SQL procedure successfully completed.

    上述输出说明,在程序运行时发生一个错误。如果你对表结构及数据不是很熟悉的话,你很难知道这个错误是什么,以及是什么原因导致错误发生的。也许在运行时,dept表中不存在对应的部门编号,或者SELECT INTO语句所导致的数据类型匹配问题。尽管这只是一个简单地例子,但仍旧可能会发生很多意想不到的运行错误。

    当然,你永远无法知道程序执行时所有可能发生的运行错误,因此,最好在自己的程序中添加OTHERS异常处理程序。为改进自己程序的异常处理接口,Oracle提供了两个内置函数-SQLCODE和SQLERRM-用于实现OTHERS异常处理程序。SQLCODE函数会返回Oracle错误编号,SQLERRM函数返回错误信息。

    修改上例如下:

    复制代码
    DECLARE
      v_deptno number(4) := &v_deptno;
      v_dname  varchar2(5);
      v_loc    varchar2(10);
    BEGIN
      SELECT dname,loc
        INTO v_dname,v_loc
        FROM dept
      WHERE deptno = v_deptno;
      DBMS_OUTPUT.PUT_LINE(v_dname||','||v_loc);
    EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||chr(10)||SQLERRM);  -->> chr(10)代表回车键
    END;
    复制代码

    同样,当输入10作为部门编号时,看看输出:

    复制代码
    SQL> / 
    Enter value for v_deptno: 10
    -6502
    ORA-06502: PL/SQL: numeric or value error: character string buffer too
    small
    
    PL/SQL procedure successfully completed.
    复制代码

    这样可捕捉任何运行时错误的错误编号和错误信息 

    总结:

         1> 预定义异常的错误代码有名称,譬如上文提到的NO_DATA_FOUNG

         2> 非预定义异常只有错误代码,没有名称,如上文提到的ora-02292。这时可以通过EXCEPTION_INIT编译指令进行错误代码和名称的关联。

         3> 当PL/SQL语句块的可执行部分出现某个运行错误时,会抛出不同类型的异常。但是,运行错误也可能发生在语句块的声明部分或者异常处理部分。控制在这些环境下异常抛出方式的规则称为异常传播。

         4> 当PL/SQL语句块的声明部分或者异常处理部分出现运行错误时,该语句块的异常处理部分不能捕获此项错误。如果不存在外部语句块,该程序执行会终止,并将执行权转到主机环境。如果存在外部语句块,该异常会立即传播到外部语句块。如下例所示:

    复制代码
    --outer block
    BEGIN
       --inner block
       DECLARE
          v_test CHAR(3) := 'ABC';
       BEGIN
          v_test := '1234';
          DBMS_OUTPUT.PUT_LINE('v_test: '||v_test);
       EXCEPTION
          WHEN INVALID_NUMBER OR VALUE_ERROR THEN
              v_test := 'ABCD';
              DBMS_OUTPUT.PUT_LINE('An error has occurred in the inner block');
       END;
    EXCEPTION
       WHEN INVALID_NUMBER OR VALUE_ERROR THEN
           DBMS_OUTPUT.PUT_LINE('An error has occurred in the program');
    END;
    复制代码

    当执行时,得到如下输出:

    SQL> /
    An error has occurred in the program
    
    PL/SQL procedure successfully completed.

    最后试举一例练练思维:

    复制代码
    DECLARE
       my_error1 EXCEPTION;
       PRAGMA EXCEPTION_INIT(my_error1, -20001);
       my_error2 EXCEPTION;
       PRAGMA EXCEPTION_INIT(my_error2, -20002);
    BEGIN
      IF 1=2  THEN
        raise_application_error(-20001,'err_1');
      ELSE
        raise_application_error(-20002,'err_2');
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Not found');
      WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More data');
      WHEN MY_ERROR1 THEN
        dbms_output.put_line('This is a err_1 test');
      WHEN MY_ERROR2 THEN
        dbms_output.put_line('This is a err_2 test');
    END;
    复制代码
  • 相关阅读:
    MVC简介
    Scrapy中的item是什么
    Git删除文件操作
    vi编辑文件出现Can't open file for writing错误
    CentOs安装Scrapy出现error: Setup script exited with error: command ‘gcc’ failed with exit status 1错误解决方案
    Python脚本运行出现语法错误:IndentationError: unindent does not match any outer indentation level
    激活MyEclipse 6.5方法-通过一段Java程序生成激活码
    oninput,onpropertychange,onchange的用法和区别【转载】
    Myeclipse6.5项目启动时由于数据库连接失败的错误日志
    IE和Firefox的Javascript兼容性总结
  • 原文地址:https://www.cnblogs.com/xieweikai/p/6838072.html
一二三 - 开发者的网上家园