Oracle對PL/SQL中的異常處理

目錄

• 一、異常處理
• 1、三種的異常類型：
• 2、異常的SQLCode 和 SQLERRM
• 二、處理預定義例外
• 常見錯誤預定義的名稱
• 三、處理非預定義例外(exception_init)
• 四、處理自定義例外（通過Raise ）
• 五、應用程序的異常處理。（通過Raise_Application_Error ）

一、異常處理

PL/SQL提供了良好的異常處理機制，當程序運行出現錯誤時就會觸發異常。異常被觸發時，程序執行即終止，在PL/SQL塊中提供了異常處理的部分，從而可以捕獲一個異常進行特殊處理。

1、三種的異常類型：

• 預定義 ( Predefined )錯誤：
  --ORACLE預定義的異常情況大約有24個。對這種異常情況的處理，無需在程序中定義，由ORACLE自動將其引發。
• 非預定義 ( Predefined )錯誤：
  --即其他標準的ORACLE錯誤。對這種異常情況的處理，需要用戶在程序中定義，然后由ORACLE自動將其引發。
• 用戶定義(User_define) 錯誤：
  --程序執行過程中，出現編程人員認為的非正常情況。對這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其引發。

2、異常的SQLCode 和 SQLERRM

EXCEPTIONWHEN first_exception THEN <code to handle first exception >WHEN second_exception THEN <code to handle second exception >WHEN OTHERS THEN <code to handle others exception >END;

由于ORACLE 的錯信息最大長度是512字節，為了得到完整的錯誤提示信息，我們可用 SQLERRM和 SUBSTR 函數一起得到錯誤提示信息，方便進行錯誤，特別是如果WHEN OTHERS異常處理器時更為方便。

• SQLCODE 返回遇到的Oracle 錯誤號
• SQLERRM 返回遇到的Oracle錯誤信息

二、處理預定義例外

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

常見錯誤預定義的名稱

• ACCESS_INTO_NULL(ORA-06530)：引用對象類型變量的屬性時，未對變量進行初始化；
• CASE_NOT_FOUND(ORA-06592)：使用case-when結構時，未使用else子句，并且處理了不包含的case條件；
• COLLECTION_IS_NULL(ORA-06531)：引用集合類型元素時，未對集合類型變量初始化；
• CURSOR_ALREADY_OPEN(ORA-06511)：使用open命令打開已經處于打開狀態的顯式游標；
• DUL_VAL_ON_INDEX(ORA-00001)：在唯一約束的列上插入重復的值時會觸發；
• INVALID_NUMBER(ORA-01722)：不能將字符轉換為合理的數字時會觸發；
• NO_DATA_FOUND(ORA-01403)：select語句未返回行或引用未初始化的索引表元素；
• TOO_MANY_ROWS(ORA-01422)：select into時，返回數據超過一行時即被觸發；
• ZERO_DEVIDE(ORA-01476)：除0異常；
• SUBSCRIPT_BEYOUND_COUNT(ORA-06533)：使用數組類型是，下標超出元素范圍；
• SUBSCRIPT_OUTSIDE_LIMIT(ORA-06532)：使用嵌套表或Varry元素時，使用了負數作為下標；
• VALUE_ERROR(ORA-06502)：賦值操作時，變量長度不足以容納實際數據長度
• ROWTYPE_MISMATCH(ORA-06504)：位游標變量賦值時，返回類型與游標定義類型不一致

--例1：更新指定員工工資，如工資小于1500，則加100；DECLARE   v_empno employees.employee_id%TYPE := &empno;   v_sal   employees.salary%TYPE;BEGIN   SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;   IF v_sal<=1500 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno; DBMS_OUTPUT.PUT_LINE(‘編碼為‘||v_empno||‘員工工資已更新!‘);        ELSEDBMS_OUTPUT.PUT_LINE(‘編碼為‘||v_empno||‘員工工資已經超過規定值!‘);   END IF;EXCEPTION   WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE(‘數據庫中沒有編碼為‘||v_empno||‘的員工‘);   WHEN TOO_MANY_ROWS THEN      DBMS_OUTPUT.PUT_LINE(‘程序運行錯誤!請使用游標‘);   WHEN OTHERS THEN      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);END;

三、處理非預定義例外(exception_init)

對于這類異常情況的處理，首先必須對非定義的ORACLE錯誤進行定義。步驟如下：

• 在PL/SQL 塊的定義部分定義異常情況：

<異常情況> EXCEPTION;

• 將系統異常轉為用戶定義異常：將其定義好的異常情況，與標準的ORACLE錯誤聯系起來，使用EXCEPTION_INIT語句：

PRAGMA EXCEPTION_INIT(<異常情況>, <錯誤代碼>)；

舉例：

--例2：刪除指定部門的記錄信息，以確保該部門沒有員工。 INSERT INTO departments VALUES(50, ‘FINANCE‘, ‘CHICAGO‘);DECLARE   v_deptno departments.department_id%TYPE := &deptno;   deptno_remaining EXCEPTION;--定義一個異常變量   PRAGMA EXCEPTION_INIT(deptno_remaining, –2292); /* -2292 是違反一致性約束的錯誤代碼 */BEGIN   DELETE FROM departments WHERE department_id = v_deptno;EXCEPTION   WHEN deptno_remaining THEN       DBMS_OUTPUT.PUT_LINE(‘違反數據完整性約束!‘);   WHEN OTHERS THEN      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);END;

四、處理自定義例外（通過Raise ）

當與一個異常錯誤相關的錯誤出現時，就會隱含觸發該異常錯誤。用戶定義的異常錯誤是通過顯式使用 RAISE 語句來觸發。當引發一個異常錯誤時，控制就轉向到 EXCEPTION塊異常錯誤部分，執行錯誤處理代碼。對于這類異常情況的處理，步驟如下：

• 在PL/SQL 塊的定義部分定義異常情況：

<異常情況> EXCEPTION;RAISE <異常情況>；

• 在PL/SQL 塊的異常情況處理部分對異常情況做出相應的處理。

--例3：更新指定員工工資，增加100； DECLARE   v_empno employees.employee_id%TYPE :=&empno;   no_result  EXCEPTION;-定義異常BEGIN   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;   IF SQL%NOTFOUND THEN      RAISE no_result;//觸發異常   END IF;EXCEPTION   WHEN no_result THEN //捕捉異常      DBMS_OUTPUT.PUT_LINE(‘你的數據更新語句失敗了!‘);   WHEN OTHERS THEN      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);

五、應用程序的異常處理。（通過Raise_Application_Error ）

調用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯誤消息，它為應用程序提供了一種與ORACLE交互的方法。

RAISE_APPLICATION_ERROR 的語法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

• error_number：從 –20,000 到 –20,999 之間的參數，
• error_message：是相應的提示信息(< 2048 字節)，
• keep_errors：可選，如果keep_errors =TRUE ,則新錯誤將被添加到已經引發的錯誤列表中。如果keep_errors=FALSE(缺省),則新錯誤將替換當前的錯誤列表。

例：創建一個函數get_salary, 該函數檢索指定部門的工資總和，其中定義了-20991和-20992號錯誤，分別處理參數為空和非法部門代碼兩種錯誤：

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER) #函數檢索指定部門的工資總和RETURN NUMBER AS  v_sal NUMBER; #函數get_salary返回v_sal值BEGIN  IF p_deptno IS NULL THEN    RAISE_APPLICATION_ERROR(–20991, "部門代碼為空"); #用戶定義的異常處理  ELSIF p_deptno<0 THEN    RAISE_APPLICATION_ERROR(–20992, "無效的部門代碼");  ELSE    SELECT SUM(employees.salary) INTO v_sal FROM employees     WHERE employees.department_id=p_deptno;    RETURN v_sal;  END IF;

調用函數

DECLARE   V_salary NUMBER(7,2);  V_sqlcode NUMBER;  V_sqlerr VARCHAR2(512);  Null_deptno EXCEPTION;  #定義異常情況  Invalid_deptno EXCEPTION; #定義異常情況  PRAGMA EXCEPTION_INIT(null_deptno,–20991);  #非預定義的異常，將它與oracle錯誤聯系起來  PRAGMA EXCEPTION_INIT(invalid_deptno, –20992); #非預定義的異常，將它與oracle錯誤聯系起來BEGIN  BEGIN    V_salary :=get_salary(–10);  EXCEPTION    WHEN invalid_deptno THEN      V_sqlcode :=SQLCODE;      V_sqlerr  :=SQLERRM;      INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr); #將錯誤SQLCODE及SQLERRM存入到表errlog中        COMMIT;  END ;

例：定義觸發器，使用RAISE_APPLICATION_ERROR阻止沒有員工姓名的新員式記錄插入：

CREATE OR REPLACE TRIGGER tr_insert_empBEFORE INSERT ON employeesFOR EACH ROWBEGIN  IF :new.first_name IS NULL OR :new.last_name is null THEN    RAISE_APPLICATION_ERROR(–20000,‘Employee must have a name.‘);  END IF;

到此這篇關于Oracle對PL/SQL異常處理的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。