文章目录
异常
异常是PL/SQL语句在运行时或者编译时的错误。
1.1 异常处理
- 在单个begin…end语句中,一旦发生异常,程序将在错误处停止,并转到该语句的末尾搜索是否存在异常处理语句。
- 对于多层嵌套的语句块中发生的错误,当前语句块没有异常处理语句,或者异常处理语句类型不匹配,则要跳出当前可执行语句块,到外层语句块末尾继续搜索,直到异常得到处理。
对于多层嵌套语句块进行实例分析讲解:
SQL> l
1 declare
2 result number;
3 begin
4 dbms_output.put_line('block1');
5 begin
6 dbms_output.put_line('block2');
7 begin
8 result := 1/0;
9 dbms_output.put_line('result is '|| result);
10 end;
11 exception
12 when value_error then
13 dbms_output.put_line('block 2 :value error!');
14 end;
15 exception
16 when zero_divide then
17 dbms_output.put_line('block 1 : divisor is equal to zero!');
18* end;
定义三个语句块:BLOCK1、BLOCK2、BLOCK3,在BLOCK3中出发一个异常,但是BLOCK3末尾不写异常处理语句,在BLOCK2末尾定义Oracle预定义异常value_error
,在BLOCK1的末尾定义Oracle预定义异常zero_divide
,如下图所示:
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e2478fdf446d8fea857e5eebac94c0b0.png)
查看执行结果,显然 BLOCK3中的result结果触发异常后,不会执行下面的打印输出语句,而是查找 BLOCK3末尾是否有异常处理语句,没有则跳出语句块,执行 BLOCK2中的异常处理语句,但是异常不符合
value_error
条件,所以继续跳出,执行
BLOCK1中的
zero_value
异常,符合条件后,异常执行语句执行输出
"block 1 : divisor is equal to zero!"
。
SQL> /
block1
block2
block 1 : divisor is equal to zero!
PL/SQL 过程已成功完成。
1.2 预定义异常
参考Oracle官方文档《表12-3 PL / SQL预定义异常》和一篇博文《Oracle异常处理及举例(Oracle预定义异常和自定义异常)》
Oracle常见的预定义异常:
ZERO_DIVIDE 除数为零
ACCESS_INTO_NULL 企图为某个未初始化的对象的属性赋值
COLLECTION_IS_NULL 企图使用未初始化的集合元素
CURSOR_ALREADY_OPEN 企图再次打开已经打开的游标
INVALID_CURSOR 执行一个非法的游标操作
INVALID_NUMBER 企图将一个字符串转换为一个无效的数字而失败
LOGIN_DENIED 企图使用无效的用户名或者密码连接数据库
NO_DATA_FOUND select into 语句没有返回数据
ROWTYPE_MISMATCH 主游标变量与PL/SQL游标变量的返回类型不兼容
SELF_IS_NULL 使用对象类型时,使用空对象调用其方法
SUBSCRIPT_BEYOND_COUNT 元素下表超过嵌套表或者VARRAY的最大值
SUBSCRIPT_OUTSIDE_LIMIT 企图使用非法索引号引用嵌套表或者VARRAY的最大值
SYS_INVALID_ROWID 字符串向ROWID转换时的错误,因为该字符串不是一个有效的ROWID值
TIMEOUT_ON_RESOURCE Oracle在等待资源时超时
TOO_MANY_ROWS 执行select into 语句时,结果集超过一行引发的异常
1.3 自定义异常
- 首先在DECLARE声明部分声明异常
- 在异常处理语句中使用
when
子句捕获对应的异常 - 在 执行语句中可能发生异常的部分显式地抛出异常
语法格式:
DECLARE
exception_name EXCEPTION;
BEGIN
...
IF 条件 then
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name then
处理异常
END;
示例:
自定义一个异常test_exception
,一个number类型的变量test
,当赋给该变量的值大于100时则报错,抛出自定义异常test_exception
处理。
SQL> declare
2 test_number number;
3 test_exception exception;
4 begin
5 dbms_output.put_line('continue..!');
6 begin
7 test_number := '&number';
8 if test_number >100 then
9 raise test_exception;
10 end if;
11 exception
12 when no_data_found then
13 dbms_output.put_line('no data found');
14 when value_error then
15 dbms_output.put_line('value error');
16 when test_exception then
17 dbms_output.put_line('test_exception');
18 test_number := 100;
19 end;
20 end;
21 /
输入 number 的值: 5
原值 7: test_number := '&number';
新值 7: test_number := '5';
continue..! #符合条件不报错
PL/SQL 过程已成功完成。
SQL> /
输入 number 的值: 1000
原值 7: test_number := '&number';
新值 7: test_number := '1000';
continue..!
test_exception #大于100,执行test_exception
PL/SQL 过程已成功完成。
SQL> /
输入 number 的值: a
原值 7: test_number := '&number';
新值 7: test_number := 'a';
continue..!
value error #变量类型不匹配,执行value_error
PL/SQL 过程已成功完成。
1.4 异常作用范围
异常的作用范围仅限于当前的语句块,外部语句块无法调用内部语句块声明的异常。
示例2:
定义两个语句块,在外部语句块中声明变量test
,在内部语句块中定义用户自定义异常test_exception
;
内部语句块为变量test
赋值,判断该值则在外部语句块中执行,并抛出内部语句块自定义的异常。
SQL> declare
2 test_number number;
3 begin
4 dbms_output.put_line('continue...!');
5 declare
6 test_exception exception;
7 begin
8 test_number :='&number';
9 exception#内部语句块中的异常处理
10 when test_exception then
11 dbms_output.put_line('test_exception');
12 end;
13 if test_number >100 then
14 raise test_exception;
#在外部语句块中抛出异常
15 end if;
16 end;
17 /
输入 number 的值: 1000
原值 8: test_number :='&number';
新值 8: test_number :='1000';
raise test_exception;
*
第 14 行出现错误:
ORA-06550: 第 14 行, 第 10 列:
PLS-00201: 必须声明标识符 'TEST_EXCEPTION'
ORA-06550: 第 14 行, 第 4 列:
PL/SQL: Statement ignored
PLS-00201: 必须声明标识符 'TEST_EXCEPTION'
,显然外部语句块无法看到内部语句块中的异常定义。
但是外部语句块中声明的test
变量在内部语句块中是可见的。
1.5 异常传播
异常可发生在
- 可执行部分
- 声明部分
- 异常处理部分
具体部分的区分请参考《Oracle的PL/SQL语句块结构》
1.5.1 可执行部分发生异常
上述实例均为可执行部分发生的异常,这里不再赘述。
1.5.2 声明部分发生异常
实例1:单层异常传播
SQL> declare
2 var_name varchar2(5) := 'Abroham';
3 begin
4 dbms_output.put_line('var_name : '||var_name);
5 exception
6 when others then
7 dbms_output.put_line(sqlcode);
8 dbms_output.put_line(sqlerrm);
9 dbms_output.put_line('inner :error happened...!');
10 end;
11 /
declare
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 2
可见异常发生时,过程已终止,OTHERS异常无法捕获过程声明中发生的错误,异常最终传播到了操作系统环境。
显然这种错误传播是不能让之发生的,如果采用多层begin...end
可执行部分,可将内部语句块中的异常传播到外部语句块时进行处理。
实例2:多层begin…end可执行部分异常传播
SQL> l
1 declare
2 begin
3 dbms_output.put_line('outer....');
4 declare
5 var_name varchar2(5) := 'Abroham';
6 begin
7 dbms_output.put_line('var_name : '||var_name);
8 exception
9 when others then
10 dbms_output.put_line(sqlcode);
11 dbms_output.put_line(sqlerrm);
12 dbms_output.put_line('inner :error happened...!');
13 end;
14 exception
15 when others then
16 dbms_output.put_line(sqlcode);
17 dbms_output.put_line(sqlerrm);
18 dbms_output.put_line('outer:error happened..!');
19* end;
SQL> /
outer....
-6502
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
outer:error happened..!
PL/SQL 过程已成功完成。
发生声明部分异常传播时的流程图如下图所示:
当内部语句块发生声明错误时,该异常会立即传播到外部语句块。如果外部语句块不能处理该异常,则异常继续向外传播。如果外部语句块还是不能处理,则传播到主机环境。
实例3:raise用户自定义异常
用Raise
抛出用户自定义的异常来传播该异常,一旦显示抛出该异常后,将在EXCEPTION
部分执行异常处理。
- 内部异常是由运行系统隐式抛出
- 自定义异常使用
EXCEPTION_INIT
过程将ORACLE错误号和错误信息关联后,也隐式抛出 - 其他异常必须用
Raise
关键字显示抛出
SQL> declare
2 my_expl exception;
3 var_name varchar2(10) :='&title';
4 begin
5 if var_name = 'manager' then
6 raise my_expl;
7 end if;
8 exception
9 when my_expl then
10 dbms_output.put_line('can not modify manager!');
11 end;
12 /
输入 title 的值: manager
原值 3: var_name varchar2(10) :='&title';
新值 3: var_name varchar2(10) :='manager';
can not modify manager!
PL/SQL 过程已成功完成。
实例4:异常再次抛出
在内部语句块中处理异常时,可使用raise
语句再次抛出异常,且不需要指出异常名称。
此时raise
的含义是将当前异常处理部分处理的异常再次抛出,在外部语句块中接收处理。
SQL> declare
2 number_high EXCEPTION;
3 current_number NUMBER := '&curr_number';
4 max_number NUMBER := 100;
5 err_number NUMBER;
6 BEGIN -- 外部语句块
7 BEGIN -- 内部语句块
8 IF current_number > max_number then
9 raise number_high;
10 END IF;
11 EXCEPTION
12 WHEN number_high THEN -- 第一次处理异常number_high
13 DBMS_OUTPUT.PUT_LINE('number'||current_number||'is out of range.');
14 DBMS_OUTPUT.PUT_LINE('Maximum number is '||max_number||'.');
15 RAISE; -- 再次抛出异常
16 END; -- 结束内部语句块
17 EXCEPTION
18 WHEN number_high THEN -- 再次处理异常。
-- 因为是内部异常处理语句块抛出的异常,所以与内部异常处理语句块异常同名。
19 err_number := current_number;
20 current_number := max_number;
21 DBMS_OUTPUT.PUT_LINE (
22 'Revising number from '||err_number||' to ' ||current_number||'.');
23 END;
24 /
输入 curr_number 的值: 102
原值 3: current_number NUMBER := '&curr_number';
新值 3: current_number NUMBER := '102';
number102is out of range.
Maximum number is 100.
Revising number from 102 to 100.
PL/SQL 过程已成功完成。
1.5.3 异常部分发生异常
简单来说,和声明部分发生异常一样,异常部分的异常并不是由内部语句块整理,而是传播到外部语句块,如果外部语句块无法处理,则继续传播到主机环境。
实例:
SQL> l
1 begin
2 declare
3 var_name varchar2(5) := 'Mark';
4 var_number number;
5 begin
6 var_number := 1/0;
7 dbms_output.put_line('var_name :'||var_name);
8 exception
9 when others then
10 var_name := 'Abraham';
11 dbms_output.put_line(sqlcode);
12 dbms_output.put_line(sqlerrm);
13 dbms_output.put_line('inner :error happened...!');
14 end;
15 exception
16 when others then
17 dbms_output.put_line('sqlcode : '||sqlcode);
18 dbms_output.put_line('sqlerrm***: '||sqlerrm||'***sqlerrm');
19 dbms_output.put_line('outter :error happened...!');
20* end;
SQL> /
sqlcode : -6502
sqlerrm***: ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-01476:
除数为 0***sqlerrm
outter :error happened...!
PL/SQL 过程已成功完成。
1.6 应用RAISE_APPLICATION_ERROR
- 属于一个特殊的过程
- 可为特殊的异常定义具体内容,属于自定义异常
- 语法格式是:
RAISE_APPLICATION_ERROR(错误号,错误消息)
错误范围为:-20999~-20000
错误消息最多2048个字符
实例:
程序判断输入负数后,执行RAISE_APPLICATION_ERROR
自定义的异常来处理错误
SQL> l
1 declare
2 mgr_id number(6) :='&id';
3 count_num number;
4 my_exception exception;
5 begin
6 if mgr_id <0 then
7 raise_application_error
8 (-20001,'error happened...negative mgr_id not allowed here');
9 else
10 select count(*) into count_num from employees
11 where empno=mgr_id;
12 dbms_output.put_line('sum of '||mgr_id||' is ' ||count_num);
13 end if;
14* end;
SQL> /
输入 id 的值: 5
原值 2: mgr_id number(6) :='&id';
新值 2: mgr_id number(6) :='5';
sum of 5 is 0
PL/SQL 过程已成功完成。
SQL> /
输入 id 的值: -5
原值 2: mgr_id number(6) :='&id';
新值 2: mgr_id number(6) :='-5';
declare
*
第 1 行出现错误:
ORA-20001: error happened...negative mgr_id not allowed here
ORA-06512: 在 line 7
同样也可在异常处理部分使用RAISE_APPLICATION_ERROR
;
可以创建一个表user_def_errors
用于维护自定义错误号 与错误消息。
1.7 应用EXCEPTION_INIT
- Oracle异常存在错误号,但无错误名称
- 将Oracle错误号与用户定义错误的名称关联
实例:
QL> l
1 declare
2 t_deptno number(2) :=&deno;
3 begin
4 delete from dept
5 where deptno=t_deptno;
6 dbms_output.put_line('delete...1');
7* end;
SQL> /
输入 deno 的值: 20
原值 2: t_deptno number(2) :=&deno;
新值 2: t_deptno number(2) :=20;
declare
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录
ORA-06512: 在 line 4
删除表dept的记录时,因为表emp存在外键引用表dept中的里deptno
,所以会报”违反完整约束条件“。
使用EXCEPTION_INIT
编译,是的错误号ORA-02292
与用户定义异常关联。
使用语法:
DECLARE
EXCEPTION_NAME exception;
PRAGMA exception_init(exception_name,exception_number);
实例:
SQL> declare
2 t_deptno number(2) :=&deno;
3 constraint_exp exception;
4 pragma exception_init(constraint_exp,-2292);
5 begin
6 delete from dept
7 where deptno=t_deptno;
8 dbms_output.put_line('deleted...');
9 exception
10 when constraint_exp then
11 dbms_output.put_line('violate reference constraint');
12 end;
13 /
输入 deno 的值: 20
原值 2: t_deptno number(2) :=&deno;
新值 2: t_deptno number(2) :=20;
violate reference constraint
PL/SQL 过程已成功完成。
1.7 应用SQLCODE与SQLERRM
虽然用others可以处理所有不确定的异常,但是缺乏针对性,ORACLE提供了两个函数SQLCODE
与SQLERRM
来获得others异常中捕获的错误号和错误消息。
SQLCODE
获得错误号SQLERRM
获得错误消息,消息最大长度为512字节
上述实例中已有使用,不再赘述。