1. 首页
  2. 文章中心
  3. Oracle基本操作

Oracle中的异常处理Oracle中的异常处理

90 阅读 0 评论
我是靠谱客的博主 小巧胡萝卜，这篇文章主要介绍Oracle中的异常处理Oracle中的异常处理，现在分享给大家，希望可以做个参考。

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程序体的后半部，语法如下：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare
    --定义类型、变量
begin 
    --业务处理
exception   --异常处理
    WHEN first_exception THEN 
        ......
    WHEN second_exception THEN
        ......
    WHEN OTHERS THEN
        ......
end;
/

二、预定义异常处理

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

序号系统异常名称产生原因
1ACCESS_INTO_NULL未定义对象
2CASE_NOT_FOUNDCASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
3COLLECTION_IS_NULL集合元素未初始化
4CURSER_ALREADY_OPEN游标已经打开
5DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值
6INVALID_CURSOR在不合法的游标上进行操作
7INVALID_NUMBER内嵌的 SQL 语句不能将字符转换为数字
8NO_DATA_FOUND使用 select into 未返回行，或应用索引表未初始化的元素时
9TOO_MANY_ROWS执行 select into 时，结果集超过一行
10ZERO_DIVIDE除数为 0
11SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或 VARRAY 的最大值
12SUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或 VARRAY 时，将下标指定为负数
13VALUE_ERROR赋值时，变量长度不足以容纳实际数据
14LOGIN_DENIEDPL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
15NOT_LOGGED_ONPL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
16PROGRAM_ERRORPL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
17ROWTYPE_MISMATCH宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
18SELF_IS_NULL使用对象类型时，在 null 对象上调用对象方法
19STORAGE_ERROR运行 PL/SQL 时，超出内存空间
20SYS_INVALID_ID无效的 ROWID 字符串
21TIMEOUT_ON_RESOURCEOracle 在等待资源时超时

下面的例子：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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，此时程序没有出现任何错误。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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异常。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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异常。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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块的定义部分定义异常情况

复制代码
1
2
<异常情况>  EXCEPTION;

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

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

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

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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）在以上两张表中输入数据

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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）定义异常处理

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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块的定义部分定义异常

复制代码
1
2
<异常情况>  EXCEPTION;

（2）使用RAISE抛出异常

复制代码
1
2
RAISE <异常情况>;

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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交互的方法。语法如下：

复制代码
1
2
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。

例子：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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中把错误代码和错误信息保存到表中。表结构如下：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
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.

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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表中的数据，结果如下：

复制代码
1
2
3
4
5
6
7
8
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中内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(60)

相关文章

oracle报错数据复数,oracle毛病（二）-数据库专栏,ORACLE
oracle报错数据复数,oracle毛病（二）-数据库专栏,ORACLE
oracle 12c undo,Oracle 12c undo异常处理—root pdb undo异常
oracle 12c undo,Oracle 12c undo异常处理—root pdb undo异常
oracle存储过程报错输出报错信息,oracle存储过程执行时，如何获得详细的错误信息...
oracle存储过程报错输出报错信息,oracle存储过程执行时，如何获得详细的错误信息...
数据库Oracle开发手——报错原因及解决方法
数据库Oracle开发手——报错原因及解决方法
Oracle中的异常处理Oracle中的异常处理
Oracle中的异常处理Oracle中的异常处理
ORA-01439错误处理：column to be modified must be empty to change datatype
ORA-01439错误处理：column to be modified must be empty to change datatype
003.vue-cli2.x中使用echarts时，配置trigger=axis无效不显示的解决办法
003.vue-cli2.x中使用echarts时，配置trigger=axis无效不显示的解决办法
Mysql5.7修改用户密码，报错“Unknown trigger has an error in its body: ‘Unknown system variable ‘maintain_user”
Mysql5.7修改用户密码，报错“Unknown trigger has an error in its body: ‘Unknown system variable ‘maintain_user”

评论列表共有 0 条评论

立即
投稿
返回
顶部