plsql異常處理

Exception概述

Exception是一種PL/SQL識別符號，當執行的PL/SQL塊出現錯誤或警告，則會觸發異常處理。為了提高程式的健壯性，可以在PL/SQL塊中引入異常處理部分，進行捕捉異常，並根據異常出現的情況進行相應的處理。
ORACLE異常分為兩種型別：系統異常、自定義異常。其中系統異常又分為：預定義異常和非預定義異常。
預定義異常
ORACLE定義了他們的錯誤編號和異常名字，常見的預定義異常處理如下

非預定義異常
ORACLE為它定義了錯誤編號，但沒有定義異常名字。我們使用的時候，先聲名一個異常名，透過偽過程PRAGMA EXCEPTION_INIT，將異常名與錯誤號關聯起來。
自定義異常
程式設計師從業務角度出發，制定的一些規則和限制。
異常處理
PL/SQL中，異常處理按個步驟進行：
定義異常

exception_name EXCEPTION;
丟擲異常

RAISE exception_name
捕獲及處理異常
EXCEPTION
    WHEN e_name1 [OR e_name2 ... ] THEN
        statements;
    WHEN e_name3 [OR e_name4 ... ] THEN
        statements;
        ......
    WHEN OTHERS THEN
        statements;
END;

預定義異常處理示例  
一個整除的異常
SQL>  declare
  2    v_n1 number := 50;
  3    v_n2 number := 0 ;
  4    v_n3 number;
  5  begin
  6    v_n3 := v_n1/v_n2;
  7    dbms_output.put_line('v_n3=' || v_n3);
  8  exception
  9    when zero_divide then
 10      dbms_output.put_line('v_n2 can' || '''' || 't be 0');
 11* end;

v_n2 can't be 0
PL/SQL procedure successfully completed.

VALUE_ERROR(錯誤號ORA-06502)

SQL> declare
  2    v_ename varchar2(3);
  3  begin
  4    select ename into v_ename from emp where empno = &eno;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when value_error then
  8      dbms_output.put_line('variable datatype length is small');
  9  end;
 10  /
Enter value for eno: 7788
old   4:   select ename into v_ename from emp where empno = &eno;
new   4:   select ename into v_ename from emp where empno = 7788;
variable datatype length is small
PL/SQL procedure successfully completed.
TOO_MANY_ROWS(對應Oracle錯誤號ORA-01422)
SQL>declare
  2    v_ename emp.ename%type;
  3  begin
  4    select ename into v_ename from emp where deptno = &deptno;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when too_many_rows then
  8      dbms_output.put_line('Too many rows are returned');
  9  end;
 10  /
Enter value for deptno: 30
old   4:   select ename into v_ename from emp where deptno = &deptno;
new   4:   select ename into v_ename from emp where deptno = 30;
Too many rows are returned
PL/SQL procedure successfully completed.
非預定義異常示例

非預定義異常使用的基本過程如下
a.定義一個異常名
b.將異常名與異常編號相關聯
c.在異常處理部分捕捉並處理異常

SQL> delete from dept where deptno = 10;
delete from dept where deptno = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
SQL> declare
  2    e_deptid exception;       --定義異常
  3    pragma exception_init(e_deptid,-2292);       --將異常和錯誤號關聯
  4  begin
  5    delete from dept where deptno = 10;
  6  exception
  7    when e_deptid then         --捕獲異常
  8      dbms_output.put_line('There is record at sub table');
  9  end;
 10  /
There is record at sub table
PL/SQL procedure successfully completed.
自定義異常示例
自定義異常與Oracle錯誤沒有任何關係，由開發人員為特定情況所定義的例外。下面的例子中，透過自定義異常，當僱員編號不存在時，PL/SQL程式碼能夠給出適當的提示
對於自定義的異常處理需要顯示的觸發，其步驟如下
a.定義異常(在declare部分進行定義)
b.顯示觸發異常(在執行BEGIN部分觸發異常，使用RAISE語句)
c.引用異常(在EXCEPTION部分捕捉並處理異常)
SQL> update emp set deptno=20 where empno=1111;
0 rows updated.
SQL> declare
  2    e_integrity exception;
  pragma exception_init(e_integrity,-2291);
  4    update emp set deptno = &dno where empno = &eno;
  e_no_employee exception;
begin
  update emp set deptno = &dno where empno = &eno;
  if sql%notfound then
    raise e_no_employee;
  end if;
 10  exception
 11    when e_integrity then
    dbms_output.put_line('The dept does not exists');
  when e_no_employee then
    dbms_output.put_line('The employess does not exists');
 15  end;
 16  /
Enter value for dno: 20
Enter value for eno: 1111
old   6:   update emp set deptno = &dno where empno = &eno;
new   6:   update emp set deptno = 20 where empno = 1111;
The employess does not exists
PL/SQL procedure successfully completed.
工資如果少於1500，則丟擲異常
SQL> declare
  2    v_empno emp.empno%type;
  3    v_sal   emp.sal%type;
  4    e_sal exception;
  5  begin
  6    v_empno := &empno;
  7    v_sal   := &sal;
  8    insert into emp(empno,sal) values(v_empno,v_sal);
  9    if v_sal < 1500 then
 10      raise e_sal;
 11    end if;
 12  exception
 13    when e_sal then
 14      rollback;
 15      dbms_output.put_line('Salary must be more then 1500');
 16  end;
 17  /
Enter value for empno: 8888
old   6:   v_empno := &empno;
new   6:   v_empno := 8888;
Enter value for sal: 1234
old   7:   v_sal   := &sal;
new   7:   v_sal   := 1234;
Salary must be more then 1500
PL/SQL procedure successfully completed.
使用異常函式處理異常          

SQLCODE與SQLERRM

SQLCODE與SQLERRM為異常處理函式。函式SQLCODE用於取得Oracle錯誤號，函式SQLERRM用於取得與錯誤號對應的相關錯誤訊息
SQL> declare
  2    v_ename emp.ename%type;
  3  begin
  4    select ename into v_ename from emp
  5    where sal = &sal;
  6    dbms_output.put_line('Employee Name:' || v_ename);
  7  exception
  8    when no_data_found then
  9      dbms_output.put_line('The employee does not exists');
 10    when others then
 11      dbms_output.put_line('Error No:' || SQLCODE);
 12      dbms_output.put_line(SQLERRM);
 13  end;
 14  /
Enter value for sal: 1250
old   5:   where sal = &sal;
new   5:   where sal = 1250;
Error No:-1422
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
RAISE_APPLICATION_ERROR
呼叫DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯誤訊息，它為應用程式提供了一種與ORACLE互動的方法。該函式用於在PL/SQL中定義錯誤訊息，且只能在資料庫端的子程式中使用(儲存過程、函式、包、觸發器)，不能在匿名塊和客戶端的子程式中使用
使用方法
RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
該函式內的錯誤程式碼和內容，都是用使用者自定義
error_number：用於定義錯誤號，且錯誤號從-20000 到-20999 之間，以避免與ORACLE 的任何錯誤程式碼發生衝突。
message：用於指定錯誤訊息，且訊息長度不能超過k，超出部分將被擷取
可選引數true,false:預設值為false，會替換先前的所有錯誤。當設定為true，則該錯誤會被放在先前錯誤堆疊中。
SQL> create or replace procedure raise_comm
  2  (v_no emp.empno%type,v_comm out emp.comm%type)
  3  as
  4  begin
  5    select comm into v_comm from emp where empno = v_no;
  6    if v_comm is null then
  7      raise_application_error(-20001,'This employee has no comm');
  8    end if;
  9  exception
 10    when no_data_found then
    dbms_output.put_line('The employee does not exists');
 12  end;
 13  /
Procedure created.
SQL> var g_sal number;
SQL> exec raise_comm(7788,:g_sal);
BEGIN raise_comm(7788,:g_sal); END;
*
ERROR at line 1:
ORA-20001: This employee has no comm
ORA-06512: at "SCOTT.RAISE_COMM", line 7
ORA-06512: at line 1
SQL> exec raise_comm(7499,:g_sal);
PL/SQL procedure successfully completed.
SQL> print g_sal;
     G_SAL
----------
       300
PL/SQL編譯警告
PL/SQL警告的分類
SEVERE: 用於檢查可能出現的不可預料結果或錯誤結果,例如引數的別名問題.
PERFORMANCE: 用於檢查可能引起效能問題,如在INSERT操作是為NUMBER列提供了VARCHAR2型別資料.
INFORMATIONAL: 用於檢查程式中的死程式碼.
ALL: 用於檢查所有警告.
控制PL/SQL警告訊息
透過設定初始化引數PLSQL_WARNINGS來啟用在編譯PL/SQL子程式時發出警告訊息，預設為DISABLE:ALL
設定警告訊息時有如下不同的範圍
系統級別
會話級別
ALTER PROCEDURE      ---只針對設定的過程有效
將特定的訊息號設定為錯誤,也可以啟用或禁止特定訊息號
SQL> show parameter plsql%ings;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings                       string      DISABLE:ALL      
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'
SQL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> alter session set plsql_warnings='enable:(5001,5002)';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
PL/SQL編譯告警示例
檢測死程式碼
在下面的程式碼中，ELSE子句永遠不會執行,應該避免出現類似的死程式碼.從Oracle 10g開始,在編寫PL/SQL子程式之前開發人員可以啟用警告檢查.

SQL> alter session set plsql_warnings='enable:informational';
Session altered.

SQL> show parameter plsql_warnings
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
plsql_warnings                       string                            ENABLE:INFORMATIONAL, DISABLE:
                                                                       PERFORMANCE, DISABLE:SEVERE
SQL> create or replace procedure dead_code
  2  as
  3  x number := 10;
  4  begin
  5    if x > 10 then
  6      x := 1;
  7    else
  8      x := 2;      --死程式碼
  9    end if;
 10  end ;
 11  /
SP2-0804: Procedure created with compilation warnings
檢測引起效能問題的程式碼
SQL>  alter session set plsql_warnings='enable:performance';
Session altered.
SQL>  create or replace procedure update_sal
  2   (no number,salary varchar2)
  3   as
  4   begin
  5     update emp set sal=salary where empno=no;
  6   end;
  7   /
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE UPDATE_SAL:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------
5/23     PLW-07202: bind type would result in conversion away from column  type