Oracle中的异常处理

任何ORACLE错误（报告为ORA-xxxxx形式的Oracle错误号）、PL/SQL运行错误或用户定义条件都可以通过异常处理进行捕获并进行处理。但是PL/SQL编译错误不能通过PL/SQL异常处理来处理，因为这些错误发生在PL/SQL程序执行之前。

一、Oracle的异常处理方式

异常情况处理（EXCEPTION）是用来处理正常执行过程中未预料的事件，当PL/SQL程序块产生异常而没有指出如何处理时，程序就会自动终止。异常的类型有以下三种：
（1）预定义（Predefined）异常：ORACLE预定义的异常情况有21个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
（2）非预定义（Predefined）异常：即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
（3）用户定义（User_define）异常：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

异常处理部分一般放在PL/SQL程序体的后半部，语法如下：

declare
    --定义类型、变量
begin 
    --业务处理
exception   --异常处理
    WHEN first_exception THEN 
        ......
    WHEN second_exception THEN
        ......
    WHEN OTHERS THEN
        ......
end;
/

二、预定义异常处理

常见的预定义异常名称及产生原因如下表所示：

下面的例子：

SQL> 
declare
    row_emp emp%rowtype;
    v_sal number;
begin
    v_sal:=&emp_sal;
    select * into row_emp 
    from emp where sal=v_sal;
    dbms_output.put_line('工资为'||v_sal||'的员工的姓名为：'||row_emp.ename);
exception
    when no_data_found then  --no_data_found：查询结果为空
        dbms_output.put_line('没有工资为'||v_sal||'的员工！');
    when too_many_rows then  --too_many_rows：查询到多条记录
        dbms_output.put_line('有多个工资为'||v_sal||'的员工！');
    when others then         --其它的异常出现
        dbms_output.put_line('出现其他错误.');
end;
/

第一种情况，输入一个唯一的工资800，此时程序没有出现任何错误。

SQL> 
declare
    row_emp emp%rowtype;
    v_sal number;
begin
    v_sal:=&emp_sal;
    select * into row_emp 
    from emp where sal=v_sal;
    dbms_output.put_line('工资为'||v_sal||'的员工的姓名为：'||row_emp.ename);
exception
    when no_data_found then  --no_data_found：查询结果为空
        dbms_output.put_line('没有工资为'||v_sal||'的员工！');
    when too_many_rows then  --too_many_rows：查询到多条记录
        dbms_output.put_line('有多个工资为'||v_sal||'的员工！');
    when others then         --其它的异常出现
        dbms_output.put_line('出现其他错误.');
end;
 17  /
Enter value for emp_sal: 800
old   5:     v_sal:=&emp_sal;
new   5:     v_sal:=800;
工资为800的员工的姓名为：SMITH

PL/SQL procedure successfully completed.

第二种情况，输入一个不存在的工资88，此时程序出现no_data_found异常。

SQL> 
declare
    row_emp emp%rowtype;
    v_sal number;
begin
    v_sal:=&emp_sal;
    select * into row_emp 
    from emp where sal=v_sal;
    dbms_output.put_line('工资为'||v_sal||'的员工的姓名为：'||row_emp.ename);
exception
    when no_data_found then  --no_data_found：查询结果为空
        dbms_output.put_line('没有工资为'||v_sal||'的员工！');
    when too_many_rows then  --too_many_rows：查询到多条记录
        dbms_output.put_line('有多个工资为'||v_sal||'的员工！');
    when others then         --其它的异常出现
        dbms_output.put_line('出现其他错误.');
end;
 17  /
Enter value for emp_sal: 88
old   5:     v_sal:=&emp_sal;
new   5:     v_sal:=88;
没有工资为88的员工！

PL/SQL procedure successfully completed.

第三种情况，输入工资3000，由于有多个雇员的工资为3000，此时程序出现too_many_rows异常。

SQL> 
declare
    row_emp emp%rowtype;
    v_sal number;
begin
    v_sal:=&emp_sal;
    select * into row_emp 
    from emp where sal=v_sal;
    dbms_output.put_line('工资为'||v_sal||'的员工的姓名为：'||row_emp.ename);
exception
    when no_data_found then  --no_data_found：查询结果为空
        dbms_output.put_line('没有工资为'||v_sal||'的员工！');
    when too_many_rows then  --too_many_rows：查询到多条记录
        dbms_output.put_line('有多个工资为'||v_sal||'的员工！');
    when others then         --其它的异常出现
        dbms_output.put_line('出现其他错误.');
end;
 17  /
Enter value for emp_sal: 3000
old   5:     v_sal:=&emp_sal;
new   5:     v_sal:=3000;
有多个工资为3000的员工！

PL/SQL procedure successfully completed.

三、非预定义异常处理（ORACLE错误）

对于非预定义异常的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：

1、在PL/SQL块的定义部分定义异常情况

<异常情况>  EXCEPTION;

2、将其定义好的异常情况，与标准的ORACLE错误关联起来

PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);

3、在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

（1）创建两张表，并定义外键约束

SQL> 
create table department(
    department_id number(2) primary key,
    department_name varchar2(20)
);

Table created.

SQL> 
create table student(
    student_id char(11) primary key,
    student_name varchar2(20) not null,
    birth date,
    department_id number(2), 
    foreign key(department_id) 
    references department(department_id)
);

Table created.

（2）在以上两张表中输入数据

SQL> select * from department;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           10 经济系
           20 管理系
           30 计算机系

SQL> select * from student;

STUDENT_ID  STUDENT_NAME	 BIRTH		     DEPARTMENT_ID
----------- -------------------- ------------------- -------------
20190224101 王鹏                 2001-11-23 00:00:00            10
20190224102 刘晓云               2001-06-03 00:00:00            10
20190224103 张静静               2002-08-09 00:00:00            10
20190224104 刘涛                 2000-06-23 00:00:00            20
20190224105 张晓刚               2001-03-31 00:00:00            20

（3）定义异常处理

SQL> 
DECLARE
    v_deptid number;
    def_myerror exception;  --定义异常
    pragma exception_init(def_myerror,-2292);  --与标准的ORACLE错误关联起来（-2292是违反一致性约束的错误代码）
BEGIN
    v_deptid:=&did;
    select department_id into v_deptid
    from department where department_id=v_deptid;
  9  
    delete from department where department_id=v_deptid;
    if sql%found then
        dbms_output.put_line('删除成功！');
    end if;
EXCEPTION
    when def_myerror then
        dbms_output.put_line('无法删除，违反数据完整性约束!');
    when others then
        dbms_output.put_line('错误代码：'||SQLCODE||', 错误信息：'||SQLERRM);
END;
 20  /
Enter value for did: 10
old   6:     v_deptid:=&did;
new   6:     v_deptid:=10;
无法删除，违反数据完整性约束!

PL/SQL procedure successfully completed.

四、用户自定义的异常处理

用户定义的异常是通过显式使用RAISE语句来触发。用户定义的异常的处理步骤如下：

（1）在PL/SQL块的定义部分定义异常

<异常情况>  EXCEPTION;

（2）使用RAISE抛出异常

RAISE <异常情况>;

（3）在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

SQL> 
declare
    sal_error exception;
    v_sal number(4);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise sal_error;
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        dbms_output.put_line('工资的范围必须高于或等于800并且低于10000！');
        dbms_output.put_line('错误代码：'||sqlcode||', 错误信息'||sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;
/
Enter value for sal: 22
old   5:     v_sal:=&sal;
new   5:     v_sal:=22;
工资的范围必须高于或等于800并且低于10000！
错误代码：1, 错误信息User-Defined Exception

PL/SQL procedure successfully completed.

五、使用RAISE_APPLICATION_ERROR函数处理自定义异常

调用RAISE_APPLICATION_ERROR函数，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。语法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);

说明：
（1）error_number：自定义错误代码，范围从–20,000 到 –20,999之间；
（2）error_message：自定义提示信息；
（3）keep_errors：可选参数，逻辑值，默认keep_errors=FALSE。

例子：

SQL> 
declare
    v_sal number(4);
    sal_error exception;
    pragma exception_init(sal_error,-20001);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise_application_error(-20001,'工资的范围必须高于或等于800并且低于10000！');
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        dbms_output.put_line('错误代码：'||sqlcode||', 错误信息'||sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;
 17  /
Enter value for sal: 11
old   6:     v_sal:=&sal;
new   6:     v_sal:=11;
错误代码：-20001, 错误信息ORA-20001: 工资的范围必须高于或等于800并且低于10000！

PL/SQL procedure successfully completed.

可以创建一张表，在exception中把错误代码和错误信息保存到表中。表结构如下：

SQL> create sequence seq_log_id;

Sequence created.

create table t_log(
    log_id number(10) primary key,
    log_code number(6),
    log_msg varchar(1000),
    log_time date default sysdate
);

Table created.

修改上例中的代码并执行：

SQL> 
declare
    v_sqlcode number(6);
    v_sqlerrm varchar2(1000);
    v_sal number(4);
    sal_error exception;
    pragma exception_init(sal_error,-20001);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise_application_error(-20001,'工资的范围必须高于或等于800并且低于10000！');
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        v_sqlcode:=sqlcode;
        v_sqlerrm:=sqlerrm;
        insert into t_log(log_id,log_code,log_msg)
        values(seq_log_id.nextval,v_sqlcode,v_sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;
 22  /
Enter value for sal: 300
old   8:     v_sal:=&sal;
new   8:     v_sal:=300;

PL/SQL procedure successfully completed.

查看t_log表中的数据，结果如下：

SQL> select * from t_log;

    LOG_ID   LOG_CODE LOG_MSG						      LOG_TIME
---------- ---------- ------------------------------------------------------- ------------
         3     -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！   2020-02-13 22:15:48
         4     -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！   2020-02-13 22:15:58
         5     -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！   2020-02-13 22:16:11

最后

以上就是小巧胡萝卜为你收集整理的Oracle中的异常处理Oracle中的异常处理的全部内容，希望文章能够帮你解决Oracle中的异常处理Oracle中的异常处理所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。