---建立
create or replace procedure proc_exception_init
as
my_test_exception exception;
--pragma为编译器指令 exception_init有2个参数,各为异常名称和oracle错误号码,即把oracle错误号码和异常名称联系起来
--这样就可以根据异常名称在exception模块编写异常处理功能代码了
pragma exception_init(my_test_exception,-36100);
v_a pls_integer;
begin
v_a:=1;
if v_a=2 then
dbms_output.put_line(v_a);
exception
when my_test_exception then --可以直接在exception when引用上述声明定义的my_test_exception异常,而此异常对应一个oracle错误代码即ora-xxxxx
dbms_output.put_line('me defined exception');
end;
---exception_init第二参数的取值范围如下:
error_code
--oracle错误码可以是100或者> -10000000(但排除-1403)的任何错误代码
Error code to be associated with exception. error_code can be either 100
(the numeric code for "no data found" that "SQLCODE Function" returns) or any negative integer greater
than -10000000 except -1403 (another numeric code for "no data found").
------------下述测试oracle错误码的取值范围
---如果错误码不在上述的指定范围内,则报错
SQL> create or replace procedure proc_uniq
2 as
3 exception_test exception;
4 pragma exception_init(exception_test,1);
5 begin
6 insert into t_unique values(1);
7 commit;
8 exception
9 when exception_test then
10 dbms_output.put_line('insert same record,please change other record');
11 end;
12 /
Warning: Procedure created with compilation errors
SQL> show err
Errors for PROCEDURE TBL_BCK.PROC_UNIQ:
LINE/COL ERROR
-------- ------------------------------------------------------------------
3/1 PLS-00701: illegal ORACLE error number 1 for PRAGMA EXCEPTION_INIT
--错误码为100就ok
SQL> create or replace procedure proc_uniq
2 as
3 exception_test exception;
4 pragma exception_init(exception_test,100);
5 begin
6 insert into t_unique values(1);
7 commit;
8 exception
9 when exception_test then
10 dbms_output.put_line('insert same record,please change other record');
11 end;
12 /
Procedure created
--值为-10000000也报错
SQL> create or replace procedure proc_uniq
2 as
3 exception_test exception;
4 pragma exception_init(exception_test,-10000000);
5 begin
6 insert into t_unique values(1);
7 commit;
8 exception
9 when exception_test then
10 dbms_output.put_line('insert same record,please change other record');
11 end;
12 /
Warning: Procedure created with compilation errors
SQL> show err
Errors for PROCEDURE TBL_BCK.PROC_UNIQ:
LINE/COL ERROR
-------- --------------------------------------------------------------------------
3/1 PLS-00701: illegal ORACLE error number -10000000 for PRAGMA EXCEPTION_INIT
--经测试与官方描述不一致,负数最大界限为-1000000,如果小于它,则报错,而非官方说的-10000000
SQL> create or replace procedure proc_uniq
2 as
3 exception_test exception;
4 pragma exception_init(exception_test,-1000000);
5 begin
6 insert into t_unique values(1);
7 commit;
8 exception
9 when exception_test then
10 dbms_output.put_line('insert same record,please change other record');
11 end;
12 /
Procedure created
小结:
1,pragma exception_init位于存储过程声明定部分
2,真正使用在exception模块,直接引用它的异常即可,编写对应的异常处理功能代码
3,oracle对一些常用的异常:比如插入重复值或未找到数据,已经把异常名称与错误代码联系起来,而还有大量的oracle错误代码未
进行联系工作,此语句即此功能
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-762140/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-762140/