开发者社区> 技术小胖子> 正文

PL/SQL异常处理

简介:
+关注继续查看

异常处理语句块结构

定义区:定义异常,如果使用预定义异常,则不用在定义区定义异常

执行区:可以显式地触发异常,也可以由PL/SQL引擎触发异常

异常处理区:只要在执行过程中出现了异常,那么执行区中后续的语句将立即停止执行,语句执行流程 跳转到异常处理区

异常处理结构示例

declare

e_duplicate_name exception;

v_ename emp.ename%TYPE;

v_newname emp.ename%TYPE:='史密斯';

begin

select ename into v_ename from emp where empno=7369;

if v_ename=v_newname

then

RAISE e_duplicate_name;

end if;

insert into emp values(7881,v_newname,'职员',NULL,TRUNC(SYSDATE),2000,200,20);

exception

when e_duplicate_name

then

dbms_output.put_line('不能插入重复的员工名称');

end;


对于未处理的异常,可以通过when others then来提供一个统一的异常处理

declare

e_duplicate_name exception;

v_ename emp.ename%TYPE;

v_newname emp.ename%TYPE:='史密斯';

begin

select ename into v_ename from emp where empno=7369;

if v_ename=v_newname

then

RAISE e_duplicate_name;

end if;

insert into emp values(7881,v_newname,'职员',NULL,TRUNC(SYSDATE),2000,200,20);

exception

when e_duplicate_name then

dbms_output.put_line('不能插入重复的员工名称');

when others then

dbms_output.put_line('异常编码:'||SQLCODE||'异常信息:'||SQLERRM);

end;


预定义异常使用示例

declare

v_tmpstr varchar2(10);

begin

v_tmpstr:='这是临时句子';

exception

when VALUE_ERROR

then

dbms_output.put_line('出现了VALUE_ERROR错误'

||'错误编号:'

||SQLCODE

||'错误名称:'

||SQLERRM

);

end;



自定义异常


declare

e_nodeptno exception;

begin

NULL;

end;


异常是一种错误的表现形式,而不是一个真正的变量,因此不能再赋值语句或SQL语句中使用异常,但是异常和变量的作用范围和规则是相同的。


作用域范围

自定义异常和变量的作用域和规则相同。

1.在同一个块中不能声明一个异常超过两次,但是可以再不同的块中声明相同的异常。但是可以再不同的块中声明两个相同的异常。

2.内层块定义的异常不可以被外层使用,内层可以调用外层定义的异常

3.如果在子块重新声明外部块中同名的异常,将覆盖外部块中的全局异常,使得子块不能引用外部块中的全局异常,但是可以再在标签块中声明相同的异常



使用EXCEPTION_INIT

如果一些异常并没有异常名称,此时在when子句中无法使用具体的异常名称,必须要使用others异常处理器进行捕捉,通过EXCEPTION_INIT编译指示,可以为这些不在预定义异常范围之类的异常添加名称。

编译指示是指能在编译期而非运行时进行处理的编译指令


编译指令EXCEPTION_INIT将告诉编译器,将异常名称和错误编号关联起来,使得在PLSQL语句块中可以使用名称来引用所有的内部异常。

declare

e_missingnull exception;

pragma exception_init(e_missingnull,-1400);

begin

insert into emp(empno)values(null);

commit;

exception

when e_missingnull then

dbms_output.put_line('触发了ORA-1400错误!'||SQLERRM);

rollback;

end;



使用RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR在子程序内部使用时,能够帮助用户从存储子程序中抛出用户自定义的错误信息。


declare or replace procedure registeremployee (

p_empno in emp.empno%TYPE,

p_ename in emp.ename%TYPE,

p_sal in emp.sal%TYPE,

p_deptno in emp.deptno%TYPE

as

v_empcount number;

begin

if p_empno is null

then

raise_application_error(-20000,'员工编号不能为空');

else

select count(*) into v_empcount from emp where empno=p_empno;

if v_empcount>0

then

raise_application_error(-20001,

'员工编号为:'||p_empno

||'的员工已存在!'

);

end if;

end if;

if p_deptno is null

then

raise_application_error(-20002,'部门编号不能为空');

end if;

insert into emp(empno,ename,sal,deptno)values(p_empno,p_ename,p_sal,p_deptno);

exception

when others then

raise_application_error(-20003,'插入数据时出现错误!异常编码:'

||SQLCODE

||'异常描述'

||SQLERRM

);

end;



输出  begin

RegisterEmployee(7779,'李明',2000,NULL);

      end;

ORA-20003:插入数据时出现错误!异常编码:-20002异常描述ORA-20002;



抛出异常

使用RAISE语句抛出异常示例

declare

e_nocomm exception;

v_comm number(10,2);

v_empno number(4):=&empno;

begin

select comm into v_comm from emp where empno=v_empno;

if v_comm is null

then

RAISE e_nocomm;

end if;

exception

when e_nocomm then

dbms_output.put_line('选择的员工没有提成!');

end;



处理异常

可以使用OR关键字进行分隔即可。

declare

e_nocomm exception;

v_comm  number(10,2);

v_empno number(4):=&empno;

begin

select comm into v_comm from emp where empno=v_empno;

if v_comm is null

then

RAISE e_nocomm;

end if;

exception

when e_nocomm or no_data_found then

dbms_output.put_line('出现了异常!');

when others then

dbms_output.put_line('任何其他未处理的异常');

end;


使用SQLCODE和SQLERRM

尽管一个异常在同一时刻只能抛出一次,但是实际上错误的消息文本可能包含来自多个异常的消息这是因为异常具有传递特性。

SQLCODE函数返回当前的错误编码。

SQLERRM函数返回消息文本


declare

e_nocomm exception;

v_comm number(10,2);

v_empno number(4):=&empno;

begin

select comm into v_comm from emp where empno=v_empno;

if v_comm is null

then

RAISE e_nocomm;

end if;

exception

when others then

dbms_output.put_line('错误编码:'||SQLCODE||'错误消息:'||SQLERRM);

end;



SQLERRM函数还可以接受一个负数的单精度参数,它将返回与该数字相关的文本,否则将返回当前异常的错误消息。

when others then               必须单独出现

dbms_output.put_line('错误编码:'||SQLCODE||'错误消息:'||SQLERRM(0));

想返回NO_DATA_FOUND的错误消息,可以传递错误消息号100



异常的传递


异常的传递是指当异常被抛出时,执行立即跳转到exception语句块中的异常处理器,异常处理器中查找是否匹配的异常,如果在当前的PL/SQL块或子程序中没有找到对应的异常处理器,那么这个异常会向其PLSQL块的外层或子程序的调用方传递,直到没有可以搜索到的块为止,这里PLSQL会向PLSQL引擎抛出一个未处理的异常。


执行时异常传递

执行时异常传递是指在PLSQL块的执行部分抛出的异常的传递机制,当在执行部分抛出异常后,PLSQL使用下面的机制来确定使用哪一个异常处理器。

1.如果当前PL/SQL的异常处理部分具有一个匹配的异常处理器,则执行当前块的异常处理器,成功完成语句块,然后将控制权传递到外层语句块

2.如果当前PLSQL块中没有匹配的异常处理器,则在当前块中抛出的异常会被传递到外层的异常处理器,然后执行外层语句块中的步骤1中的匹配操作

3.如果已经到了顶层,没有外层语句块了,则异常将被传递到调用环境。

例如

declare

e_outerexception exception;

e_innerexception exception;

e_threeexception exception;

begin

begin

RAISE e_innerexception;

RAISE e_outerexception;

RAISE e_threeexception;

exception

when e_innerexception;

end;

exception

when e_outerexception then

end;


1.首先搜寻内层块的异常处理区中的异常,在本地块中的e_innerexception异常具有一个异常处理器,因此该异常将在本地块中被处理

2.其他的两个异常将向外传递,传送给外层PL/SQL块的异常处理区,在外层的异常处理区中具有一个e_outerexception的异常处理器,因此该异常将在外层块中被捕捉并处理

3.异常e_threeexception并没有任何处理器,该异常将被传递到调用环境,由调用环境进行处理。比如SQL*PLUS会弹出一个异常。




声明时异常传递

示例

begin

declare

v_ename varchar2(2):='ABC';

begin

dbms_output.put_line(v_ename);

exception

when others then

dbms_output.put_line('产生了异常');

end;

exception

when others then

dbms_output.put_line('错误编号:'

||SQLCODE||'错误消息:'

||SQLERRM);

end;


在声明一个变量v_ename,并赋予了初始值ABC,这个赋值产生了ORA-06502异常,因为varchar2(2)不能包含ABC这3个字符。尽管在子块的exception区具有when others then 异常处理语句,但是声明区中抛出的异常并不会被本地块所捕获,而是向外层传递,被外层的异常处理区中的others异常处理器捕获,因此最终可以看到如下所示:

错误编号:-6502 错误消息:ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小。


异常处理器中的异常

declare

e_outerexception exception;

e_innerexception exception;

e_threeexception exception;

begin

begin

RAISE e_innerexception;

RAISE e_outerexception;

RAISE e_threeexception;

exception

when e_innerexception then

RAISE e_outerexception;

when e_outerexception then

when others then

end;

exception

when e_outerexception then

end;

在嵌套的内层PL/SQL块中,在执行区触发了3个异常,在异常处理区中捕获取e_innerexception异常,在处理该异常的处理器中,使用RAISE语句又触发了e_outerexception.可以看到即便在内层块中包含了e_outerexception的异常处理器,但是异常并不会在内存块中的处理器中被捕获,而是跳转到了外层的异常处理器进行处理。


重新抛出异常

declare

e_nocomm exception;

v_comm number(10,2);

v_empno number(4):=&empno;

begin

select comm into v_comm from emp where empno=v_empno;

if v_comm is null

then

RAISE e_nocomm;

end if;

exception 

when others then

dbms_output.put_line('错误编码:'||SQLCODE||'错误消息:'||SQLERRM(100));

RAISE;

end;

当e_nocomm触发后,会被others异常处理器处理,在该异常处理器中,首先输出了异常信息,然后调用RAISE语句将异常重新抛出。此时由于已经是最外层的语句块,异常将被传递到调用环境,因此SQLPLUS将触发异常。

可以看到当不为RASIE指定任何异常名称时,程序就会把当前的异常重新抛出。

这种重新抛出异常的方式只允许在异常程序中这样做。



异常处理准则

要实现从异常恢复,可以将异常包装在其自己的子块中,在子块中编写对应的异常控制程序,这样一旦在子块中有错误发生,子块内部的异常处理器就能捕获并处理异常。当子块结束时,就可以继续执行外层块中的下一条语句,因此如下

declare

e_duplicate_name exception;

v_ename emp.ename%TYPE;

v_newname emp.ename%TYPE:='史密斯';

begin

begin

select ename into v_ename from where empno=7369;

if v_ename=v_newname

then

RAISE e_duplicate_name;

end if;

exception

when e_duplicate_name then

v_newname:='刘大夏';

end;

insert into emp values(7881,v_newname,'职员',NULL,TRUNC(SYSDATE),2000,200,20);

exception

when others then

dbms_output.put_line('异常编码:'||SQLCODE||'异常信息:'||SQLERRM);

end;

可以看到,现在查询并检测同名的逻辑被封装到一个PL/SQL子块中,在子块中柏涵了异常处理语句,当异常触发时,将v_newname更改为另一个名字,从内层块中退出后,会继续执行insert语句,直到PL/SQL语句块终止。


获取异常抛出的位置

示例

declare

v_empno1 number(4):=&empno1;

v_empno2 number(4):=&empno2;

v_empno3 number(4):=&empno3;

v_sal1 number(10,2);

v_sal2 number(10,2);

v_sal3 number(10,2);

begin

select sal into v_sal1 from emp where empno=v_empno1;

select sal into v_sal2 from emp where empno=v_empno2;

select sal into v_sal3 from emp where empno=v_empno3;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'SQLERRM);

end;

当触发了NO_DATA_FOUND异常时,如何知道是哪个select语句触发了异常?


方法一:使用递增的计数器标识SQL语句

declare

v_empno1 number(4):=&empno1;

v_empno2 number(4):=&empno2;

v_empno3 number(4):=&empno3;

v_sal1 number(10,2);

v_sal2 number(10,2);

v_sal3 number(10,2);

v_selectcounter number:=1;

begin

select sal into v_sal1 from emp where empno=v_empno1;

v_selectcounter:=2;

select sal into v_sal2 from emp where empno=v_empno2;

v_selectcounter:=3;

select sal into v_sal3 from emp where empno=v_empno3;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM||'触发异常的位置是:'|| v_selectcounter);

end;


方法二:将每一个select语句定义到一个子块中去,这样就可以知道哪个select语句触发了异常,当然这种方式将导致出现多个异常消息,因为没一条select语句都会被执行。

declare

v_empno1 number(4):=&empno1;

v_empno2 number(4):=&empno2;

v_empno3 number(4):=&empno3;

v_sal1 number(10,2);

v_sal2 number(10,2);

v_sal3 number(10,2);

begin

begin

select sal into v_sall from emp where empno=v_empno1;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM||'触发异常的位置是 1');

end;

begin

select sal into v_sal2 from emp where empno=v_empno2;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM||'触发异常的位置是 2');

end;

begin

select sal into v_sal3 from emp where empno=v_empno3;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM||'触发异常的位置 是3');

end;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM);

end;

方法三:可以使用DBMS_UTILITY.FORMAT_ERROR_BACKTRACE函数来获取错误位置,这个函数是oracle10g以后的版本提供的

declare

v_empno1 number(4):=&empno1;

v_empno2 number(4):=&empno2;

v_empno3 number(4):=&empno3;

v_sal1 number(10,2);

v_sal2 number(10,2);

v_sal3 number(10,2);

begin

select sal into v_sal1 from emp where empno=v_empno1;

select sal into v_sal2 from emp where empno=v_empno2;

select sal into v_sal3 from emp where empno=v_empno3;

exception

when NO_DATA_FOUND then

dbms_output.put_line('错误编号:'||SQLCODE||'错误消息:'||SQLERRM|| dbms_utility.format_backtrace);

end;

在发生错误时,oracle会为最近一次生成的异常设置一个栈,并跟踪它的传递过程。



异常与事务处理

抛出一个异常并不会终止一个事务,除非在异常处理器中显式地使用了ROLLBACK或者COMMIT语句,但是这里有一个问题,如果顶层的语句块存在一个未处理的异常,该异常将被传递到调用环境,那么事务将被服务端自动回滚。

如果想在异常发生后,不放弃事务,不进行回滚,重新再处理一次,可以按如下的3步来实现。

1.将事务放在一个子块中

2.把子块放入一个循环,重复执行事务

3.在开始事务之前标记一个保存点,如果事务执行成功,能提交事务并退出循环,如果执行失败,就将控制权交给异常处理程序,事务回滚到保存点,然后重新尝试执行事务。


在异常中重复事务执行代码

declare

e_duplicate_name EXCEPTION;

v_ename    emp.ename%TYPE;

v_newname        emp.ename%TYPE := '史密斯';

begin

loop

begin

savepoint 开始事务;

select ename into v_ename from emp where empno=7369;

if v_ename=v_newname

then

raise e_duplicate_name;

end if;

insert into emp values(7881,v_newname,'职员',NULL,TRUNC(SYSDATE),2000,200,20);

commit;

exit;

exception

when e_duplicate_name then

rollback to 开始事务;

v_newname:='刘大夏';

end;

end loop;

end;





      本文转自潘阔 51CTO博客,原文链接:http://blog.51cto.com/pankuo/1630253,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
处理动态SQL语句的参数
原文:处理动态SQL语句的参数 经常对SQL进行开发,写动态的SQL语句,是少之不了的,但是在使用动态语句中,常是因为有动态的参数的出现。参考下面代码示例:   正因为有了标记1的动态条件代码,而让SQL语句需要变成动态的SQL语句。
674 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27734 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
19982 0
SQL——处理行的累加
       背景:               现在有一张报表,其中上面的数据是商家的订单数据,按照天统计的,要从订单表查出每天的某个商家的订单量,然后去日收益表查出这天的这个商家的收益额,然后还要统计出在某天之前的所有收益额,然后从查出的这种表里筛选出某个时间段的记录。
710 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23526 0
21115
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载