ORACLE异常处理

例一

create table testerr(id number(10),name varchar2(10));

insert into testerr values(1,'test');

insert into testerr values(2,'test');

insert into testerr values(3,'test1');

--no_data_found和too_many_rows是经常发生的异常，所以对于plsql写这样的异常捕获是很好的

declare

a testerr%rowtype; --define a variable

begin

--这里的异常代码在自己的块中，发生异常不会影响其他块，只在本块内处理掉了。

--如果将有异常代码区的块不捕获异常，则自动传播到外层块。如果外层块处理，则不会打印1，如果继续

--不处理，则显示抛出。

begin

select * into a from testerr where name='test';

--start trap exception

exception

when no_data_found then

dbms_output.put_line('no data found in table testerr!');

when too_many_rows then

dbms_output.put_line('to many rows found in this query!'||sqlcode||','||sqlerrm);

when others then

dbms_output.put_line('others error occur!');

end;

begin

dbms_output.put_line(1);

end;

end;

--结果是：

to many rows found in this query!-1422,ORA-01422: 实际返回的行数超出请求的行数

例二

create table classes(id number(10) primary key,name varchar2(10));

create table classes_student(id number(10) primary key,name varchar2(10),

classes_id number(10) references classes(id));

insert into classes values(1,'s301');

insert into classes_student values(1,'dj',1);

commit;--系统非预定义异常

   

declare

cannot_dele exception;

--2292是违反完整性约束的错误

pragma exception_init(cannot_dele,-2292);

begin

delete from classes where id=1;

commit; --start exception handle

exception

when cannot_dele then

dbms_output.put_line('can''t delete classes_student!'||sqlcode||':'||sqlerrm);

end;

--can't delete classes_student!-2292:ORA-02292: 违反完整约束条件 (DINGJUN123.SYS_C0011750) - 已找到子记录