Exception

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;