1,定义系统内自带错误编号
declare
e_insert_excep exception;
pragma exception_init(e_insert_excep, -1400);
begin
insert into departments(department_id,department_name) values(290,null);
exception when e_insert_excep then
dbms_output.put_line('Insert operation failed');
dbms_output.put_line(SQLERRM);
end;
2.显示抛出自定义异常
declare
v_deptno number := 500;
v_name varchar2(50) := 'Testing';
e_invalid_department exception;
begin
update departments set department_name = v_name
where department_id = v_deptno;
if SQL%notfound then
raise e_invalid_department;
end if;
commit;
exception when e_invalid_department then
dbms_output.put_line('No such department id');
end;
3.使用过程定义的异常
declare
v_deptno number := 500;
v_name varchar2(50) := 'Testing';
e_invalid_department exception;
begin
update departments set department_name = v_name
where department_id = v_deptno;
if SQL%notfound then
raise_application_error(-20188,'I write my error message here!');
end if;
commit;
exception when e_invalid_department then
dbms_output.put_line(SQLCODE || ' ==> ' || SQLERRM);
end;
4.避免出现异常后,以后程序不执行的情况
declare
sal_calc number(8,2);
begin
insert into emp_temp values(301,230,0);
begin
select (salary / commission_pct) into sal_calc from emp_temp where employee_id = 301;
exception when zero_divide then
saL_calc = 2500;
end;
insert into emp_temp values(302,sal_calc/100,1);
end;
5.重复事务提交
declare
name varchar2(20) := 'Smythe';
answer varchar2(3) := 'No';
suffix number := 1;
begin
for i in 1 .. 5 loop
begin
savepoint start_transaction;
delete from results where res_answer = 'No';
Insert into results values(name,answer);
commit;
exit;
exception when dup_val_on_index then
rollback to start_transaction;
suffix := suffix + 1;
name := name || to_char(suffix);
end;
end;