Oracle异常处理的定义及使用

Love The Way You Lie 2022-05-16 05:14 198阅读 0赞

**1、Oracle系统异常分别为两大类：**  
系统异常：系统自身提供.  
自定义异常：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

**2、  异常案例（no\_data\_found）**

declare
       t_name teachers.name%type;
       
    begin
       select name into t_name from teachers where teacher_id=1234;
      --捕获异常  
      exception
         --如果找不到则提示没有找到数据
         when no_data_found  then dbms_output.put_line('没有找到员工数据');
        --其他异常
         when others then dbms_output.put_line('其他异常'); 
    end;
    /

**3、自定义异常案例**

eclare 
     --定义游标  
      cursor ts is select name from teachers where teacher_id=1234;
      t_name teachers.teacher_id%type;
      --自定义例外
      no_theacher_name_found exception;
    begin
       --打开游标
       open ts;     
        --使用fetch取出数据
        loop
        fetch ts into t_name;
        if ts%notfound
          then 
            raise no_theacher_name_found;
            end if;
        end loop;
       --关闭游标
       close ts;
       --捕获异常
        exception 
          when no_theacher_name_found 
            then dbms_output.put_line('没有找到该员工数据');
          when others then dbms_output.put_line('其他例外');    
    end;

**4、其他异常的定义和使用和定义类似，常见的系统异常大概有24种如下图。**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 错误号 </td> 
 <td style="vertical-align:top;"> 异常错误信息名称 </td> 
 <td style="vertical-align:top;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-0001 </td> 
 <td style="vertical-align:top;"> Dup_val_on_index </td> 
 <td style="vertical-align:top;"> 违反了唯一性限制 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-0051 </td> 
 <td style="vertical-align:top;"> Timeout-on-resource </td> 
 <td style="vertical-align:top;"> 在等待资源时发生超时 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-0061 </td> 
 <td style="vertical-align:top;"> Transaction-backed-out </td> 
 <td style="vertical-align:top;"> 由于发生死锁事务被撤消 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1001 </td> 
 <td style="vertical-align:top;"> Invalid-CURSOR </td> 
 <td style="vertical-align:top;"> 试图使用一个无效的游标 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1012 </td> 
 <td style="vertical-align:top;"> Not-logged-on </td> 
 <td style="vertical-align:top;"> 没有连接到ORACLE </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1017 </td> 
 <td style="vertical-align:top;"> Login-denied </td> 
 <td style="vertical-align:top;"> 无效的用户名/口令 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1403 </td> 
 <td style="vertical-align:top;"> No_data_found </td> 
 <td style="vertical-align:top;"> SELECT INTO没有找到数据 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1422 </td> 
 <td style="vertical-align:top;"> Too_many_rows </td> 
 <td style="vertical-align:top;"> SELECT INTO&nbsp;返回多行 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1476 </td> 
 <td style="vertical-align:top;"> Zero-divide </td> 
 <td style="vertical-align:top;"> 试图被零除 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-1722 </td> 
 <td style="vertical-align:top;"> Invalid-NUMBER </td> 
 <td style="vertical-align:top;"> 转换一个数字失败 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6500 </td> 
 <td style="vertical-align:top;"> Storage-error </td> 
 <td style="vertical-align:top;"> 内存不够引发的内部错误 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6501 </td> 
 <td style="vertical-align:top;"> Program-error </td> 
 <td style="vertical-align:top;"> 内部错误 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6502 </td> 
 <td style="vertical-align:top;"> Value-error </td> 
 <td style="vertical-align:top;"> 转换或截断错误 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6504 </td> 
 <td style="vertical-align:top;"> Rowtype-mismatch </td> 
 <td style="vertical-align:top;"> 宿主游标变量与&nbsp;PL/SQL变量有不兼容行类型 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6511 </td> 
 <td style="vertical-align:top;"> CURSOR-already-OPEN </td> 
 <td style="vertical-align:top;"> 试图打开一个已处于打开状态的游标 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6530 </td> 
 <td style="vertical-align:top;"> Access-INTO-null </td> 
 <td style="vertical-align:top;"> 试图为null&nbsp;对象的属性赋值 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6531 </td> 
 <td style="vertical-align:top;"> Collection-is-null </td> 
 <td style="vertical-align:top;"> 试图将Exists&nbsp;以外的集合( collection)方法应用于一个null pl/sql&nbsp;表上或varray上 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6532 </td> 
 <td style="vertical-align:top;"> Subscript-outside-limit </td> 
 <td style="vertical-align:top;"> 对嵌套或varray索引得引用超出声明范围以外 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ORA-6533 </td> 
 <td style="vertical-align:top;"> Subscript-beyond-count </td> 
 <td style="vertical-align:top;"> 对嵌套或varray&nbsp;索引得引用大于集合中元素的个数. </td> 
 </tr> 
 </tbody> 
</table>