oracle异常捕捉及处理

1、

declare
e_emps_remaining exception;
pragma exception_init(e_emps_remaining,-2292);
begin
delete from dept where deptno=20;
exception
when e_emps_remaining then
dbms_output.put_line('不能删除，因为emp表中有它的子记录');
end;

 

 

 

2、

create or replace procedure proc1(p1 char,p2 char,p3 date) is
invalid_date exception; -- 定义异常变量
pk_constraint exception;
pragma exception_init(pk_constraint,-0001);
aa char(8);
bb char(8);
begin
insert into student values(p1,p2,p3);
select substr(sid,7,8),to_char(sbirth,'yyyymmdd') into aa,bb from student where sid=p1;
if aa=bb then
commit;
dbms_output.put_line('插入成功');
else
rollback;
raise invalid_date; -- 升起异常
end if;
exception
when invalid_date then
dbms_output.put_line('身份证日期与填写的日期不一致');
when pk_constraint then
dbms_output.put_line('违反唯一键原则');
end;