pl/sql编程（十三）

• 预定义异常　

　　查看预定义异常

select * from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%'

　　预定义异常的使用

declare 
v_mynum number(4);
begin
    v_mynum :=1/0;
    dbms_output.put_line('除数为零的操作结果：'|| v_mynum);    
    exception
         when ZERO_DIVIDE then
          dbms_output.put_line('除数为零！该参数非法，转入以下操作：');  
          dbms_output.put_line('这里您可以有自己的操作……');    
end;

when……others 用法

declare
v_goodsid varchar2(3);
begin
  select goodsid into v_goodsid from goods where goodsid = 10000;
  dbms_output.put_line('v_goodsid =' ||v_goodsid);
  exception
        when NO_DATA_FOUND then
           dbms_output.put_line('没有查到相关数据！！');
        when TOO_MANY_ROWS then
            dbms_output.put_line('查到的数据多于一行，请确认！！');
        when others then
             dbms_output.put_line('出现了未给出的异常，请用户检查脚本！！');
             dbms_output.put_line('错误编号为：' || substrb(SQLERRM,1,9));    
end;

• 非预定义异常

declare
ep_fk_02292 exception;
pragma exception_init(ep_fk_02292,-2292);
begin
    delete from goods where goodsid = 1;
    excepion
       when ep_fk_02292 then
            dbms_output.put_line('违反完整性约束条件，发现删除的记录被引用！');
             dbms_output.put_line('SQLERRM：' || SQLERRM); 
              dbms_output.put_line('SQLCODE：' || SQLCODE); 
     
end;

• 自定义异常

declare 
v_goodsid goods.goodsid%type := '&goodsid';
v_goodsremark goods.remark%type;
null_remark_exp exception;
pragma exception_init(null_remark_exp,-20010);
begin  
       select remark into v_goodsremark from goods where goodsid = v_goodsid;
       if(v_goodsremark is null) then
          raise null_remark_exp;
       end if;
       exception
         when null_remark_exp then
             dbms_output.put_line('该ID的备注为空，请查证！');  
end;