• Oracle對PL/SQL中的異常處理

     更新時間:2022年05月06日 09:09:37   作者:springsnow  
    這篇文章介紹了Oracle對PL/SQL中異常處理的方法,文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下

    一、異常處理

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

    1、三種的異常類型:

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

    2、異常的SQLCode 和 SQLERRM

    EXCEPTION
    WHEN 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||‘員工工資已更新!‘);     
       ELSE
            DBMS_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_emp
    BEFORE INSERT ON employees
    FOR EACH ROW
    BEGIN
      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異常處理的文章就介紹到這了。希望對大家的學習有所幫助,也希望大家多多支持腳本之家。

    相關文章

    最新評論

    美丽人妻被按摩中出中文字幕