Oracle資料庫之PL/SQL異常處理

標籤：

Oracle資料庫之PL/SQL異常處理

異常指的是在程式運行過程中發生的例外狀況事件，通常是由硬體問題或者程式設計問題所導致的。

PL/SQL程式設計過程中，即使是寫得最好的程式也可能會遇到錯誤或未預料到的事件。一個健壯的程式都應該能夠正確處理各種異常情況，並儘可能從中恢複。

1. 異常處理

異常處理是用來處理正常執行過程中未預料的事件。PL/SQL程式塊一旦產生異常而沒有指出如何處理時，程式就會自動終止整個程式運行。

PL/SQL編程過程中，有三種類型的異常：

1.預定義異常

對這種異常情況的處理，無需在程式中定義，當PL/SQL程式違反Oracle規則或超越系統限制時隱式引發。

2.非預定義異常

其他標準的Oracle錯誤。對這種異常情況的處理，需要使用者在程式中定義，然後由Oracle自動將其引發。

3.使用者定義異常

程式執行過程中，出現編程人員認為的非正常情況。對這種異常情況的處理，需要使用者在程式中定義，然後顯式地在程式中將其引發。

異常處理通常放在PL/SQL程式的後部，文法結構為：

EXCEPTION    WHEN { exception [ OR exception ]... | OTHERS }        THEN statement [ statement ]...

2. 預定義的異常處理

常見預定義異常：

錯誤號碼	異常名稱	說明
ORA-00001	DUP_VAL_ON_INDEX	重複索引值，違反了唯一性限制，當在唯一索引所對應的列上鍵入重複值時觸發
ORA-01001	INVALID_CURSOR	試圖使用一個無效的遊標
ORA-01012	NOT_LOGGED_ON	沒有串連到ORACLE
ORA-01017	LOGIN_DENIED	無效的使用者名稱/口令
ORA-01403	NO_DATA_FOUND	沒有找到資料時觸發
ORA-01422	TOO_MANY_ROWS	返回多行
ORA-01722	INVALID_NUMBER	轉換為數字失敗時觸發
ORA-06511	CURSOR_ALREADY_OPEN	試圖開啟一個已處於開啟狀態的遊標
ORA-06592	CASE_NOT_FOUND	當case條件都不滿足時觸發

更多預定義異常見：http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS00703

對預定義異常的處理，只需在PL/SQL塊的異常處理部分，直接引用相應的異常情況名，並對其完成相應的異常錯誤處理即可。

樣本1：

DECLARE  stock_price   NUMBER := 9.73;  net_earnings  NUMBER := 0;  pe_ratio      NUMBER;BEGIN  pe_ratio := stock_price / net_earnings;  DBMS_OUTPUT.PUT_LINE(‘運算結果 = ‘ || pe_ratio);EXCEPTION  WHEN ZERO_DIVIDE THEN    DBMS_OUTPUT.PUT_LINE(‘/ by zero‘);    pe_ratio := NULL;END;

運行結果：

/ by zero

為避免除0異常，可以採用如下樣本2方式解決：

樣本2：

DECLARE  stock_price   NUMBER := 9.73;  net_earnings  NUMBER := 0;  pe_ratio      NUMBER;BEGIN  pe_ratio :=    CASE net_earnings      WHEN 0 THEN NULL      ELSE stock_price / net_earnings    END;END;

樣本3：

DECLARE  default_number NUMBER := 0;BEGIN  INSERT INTO t VALUES(TO_NUMBER(‘100.00‘, ‘9G999‘));EXCEPTION  WHEN INVALID_NUMBER THEN    DBMS_OUTPUT.PUT_LINE(‘使用預設值替換非法數字‘);    INSERT INTO t VALUES(default_number);END;

運行結果：

使用預設值替換非法數字

3. 非預定義的異常處理

非預定義異常有錯誤號碼沒有名字，處理的辦法是：自己定義一個名字，綁定到錯誤號碼，捕獲錯誤名。處理這類異常，首先必須對非預定義的Oracle異常進行定義。

如：

myexcp EXCEPTION;

然後使用EXCEPTION_INIT語句與標準的ORACLE錯誤聯絡起來，如：

PRAGMA EXCEPTION_INIT(myexcp,-02292);

說明：ORA-02292是違反完整性條件約束的錯誤碼。

樣本：

DECLARE    myexcp EXCEPTION;    PRAGMA EXCEPTION_INIT(myexcp,-02292);    dno scott.emp.deptno%TYPE;BEGIN    dno := &dept_no;    DELETE FROM scott.dept WHERE deptno=dno;    EXCEPTION         WHEN myexcp THEN             DELETE FROM scott.emp WHERE deptno=dno;            DELETE FROM scott.dept WHERE deptno=dno;           END;

4. 使用者定義異常處理

我們可以在任何PL/SQL匿名塊，子程式或包的聲明部分聲明自己的異常。使用者定義的異常是通過使用RAISE語句顯式觸發的。

一般使用者定義異常的處理流程為：定義異常->拋出異常->捕獲及處理異常。

樣本：

DECLARE    invalidCATEGORY EXCEPTION; -- 定義異常   category VARCHAR2(10);BEGIN   category := ‘&Category‘;   IF category NOT IN (‘附件‘,‘頂蓋‘,‘備件‘) THEN      RAISE invalidCATEGORY; -- 拋出異常   ELSE      DBMS_OUTPUT.PUT_LINE(‘您輸入的類別是‘|| category);   END IF;EXCEPTION   WHEN invalidCATEGORY THEN -- 捕獲及處理異常      DBMS_OUTPUT.PUT_LINE(‘無法識別該類別‘);END;

我們可以調用RAISE_APPLICATION_ERROR過程引發並傳播應用程式異常，這為應用程式提供了一種與ORACLE互動的方法。

RAISE_APPLICATION_ERROR過程可用於建立使用者定義的錯誤資訊，可以在可執行部分和異常處理部分使用，錯誤編號必須介於–20000 和–20999之間，錯誤訊息的長度可長達2048個位元組。

RAISE_APPLICATION_ERROR過程文法：

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

如果指定TRUE，PL/SQL把ERROR_CODE上的錯誤資訊添加到堆棧的頂部。指定FALSE，PL/SQL替換ERROR_CODE錯誤堆棧，預設值為FALSE。

樣本1：

DECLARE   empno employees.employee_id%TYPE;   no_such_row  EXCEPTION;BEGIN   empno := &empno;   UPDATE employees SET salary = salary+100 WHERE id = empno;   IF SQL%NOTFOUND THEN      RAISE no_such_row;   END IF;EXCEPTION   WHEN no_such_row THEN       RAISE_APPLICATION_ERROR(-20001, ‘沒有待修改的行‘);END;

樣本2：

BEGIN   UPDATE emp SET deptno=80 WHERE empno=1111;   IF SQL%NOTFOUND THEN      RAISE_APPLICATION_ERROR(-20001,‘該僱員不存在！‘);   END IF;   EXCEPTION     WHEN OTHERS THEN        dbms_output.put_line(SQLCODE||‘-->‘||SQLERRM);END;

SQLCODE用於取得Oracle錯誤號碼。

SQLERRM則用於取得與之相關的錯誤訊息。

 

Oracle資料庫之PL/SQL異常處理