Oracle exception

Oracle異常處理異常處理是考驗一個應用程式健壯性的最好方式，開發人員必須考慮程式中可能出現的各種錯誤，並進行相應的處理。

Oracle中異常分為：1.預定義異常 2.非預定義異常 3.自定義異常三種。

一、預定義異常

預定義異常是指由PL/SQL所提供的系統異常。當PL/SQL應用程式違反了Oracle規則或出現其它系統限制的情況時，將會隱含地觸發一個內部異常。以下是PL/SQL為我們預定義的異常(經常更新中)：

1.CURSOR_ALREADY_OPEN（cursor_already_open）該異常觸發ORA-06511錯誤。

當程式中的一個遊標已經執行了開啟操作，如果開發人員試圖再一次開啟這個已經開啟的遊標時，將觸發該異常。

示例：

    DECLARE
    CURSOR test_cursor IS
    SELECT SYSDATE FROM dual;
    BEGIN
    OPEN test_cursor;
    FOR test_cursor2 IN test_cursor LOOP
    dbms_output.put_line(test_cursor2.SYSDATE);
    END LOOP;
    EXCEPTION
    WHEN cursor_already_open THEN
    dbms_output.put_line('遊標已經開啟，不能再次對遊標執行開啟操作。');
    END;
    /

2. INCALID_CURSOR（incalid_cursor）該異常觸發ORA-01001錯誤。

當試圖對一個尚未開啟的遊標執行任何操作，如開啟該非法的遊標執行賦值操作，或者關閉未開啟的遊標時，將觸發該異常。

示例：

    DECLARE
    CURSOR test_cursor IS
    SELECT SYSDATE FROM dual;
    test_time DATE;
    BEGIN
    --OPEN test_cursor;
    FETCH test_cursor
    INTO test_time;
    dbms_output.put_line('當前時間為: ' || test_time);
    CLOSE test_cursor;
    EXCEPTION
    WHEN invalid_cursor THEN
    dbms_output.put_line('請檢查遊標是否已經開啟.');
    END;
    /

3.NO_DATA_FOUND（no_data_found）該異常觸發ORA-01403錯誤。

當讀取一個遊標進行賦值操作(SELECT INTO操作)時，如果未返回任何行，將觸發該異常。

示例：

    DECLARE
    test_owner DATE;
    BEGIN
    SELECT t.owner INTO test_owner FROM all_tables t WHERE t.table_name = 'test';
    dbms_output.put_line(test_owner);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No value.');
     
    END;
    /

4. TOO_MANY_ROWS（too_many_rows）該異常觸發ORA-01422錯誤。

上面是沒有返回記錄，這裡是返回對條記錄的時候會觸發該異常。

示例：

    DECLARE
    test_records dba_tables%ROWTYPE;
    BEGIN
    select * INTO test_records from dba_tables where owner='APPS' AND ROWNUM < 3;
    dbms_output.put_line(test_records.table_name);
    EXCEPTION
    WHEN too_many_rows THEN
    dbms_output.put_line('Too many values.');
    END;
    /

5. VALUE_ERROR（value_error）該異常觸發ORA-06502錯誤。

當在PL/SQL程式中執行賦值操作時，如果右邊值的長度大於左邊變數的長度，將觸發該異常。

示例：

    DECLARE
    v_test VARCHAR2(1);
    BEGIN
    SELECT 'WHAT' INTO v_test FROM dual;
    dbms_output.put_line(v_test);
    EXCEPTION
    WHEN value_error THEN
    dbms_output.put_line('變數的長度不夠.');
    END;
    /

6.DUP_VAL_ON_INDEX（dup_val_on_index）該異常觸發ORA-00001錯誤。

若表的每一列宣告為主鍵，或具有惟一性，如果對該列插入重複的值時將觸發該異常。

示例：

    CREATE TABLE test_table
    (
    v_id NUMBER PRIMARY KEY,
    v_name VARCHAR2(20)
    )
    INSERT INTO test_table VALUES(1,'test1');
    INSERT INTO test_table VALUES(2,'test2');
    SELECT * FROM test_table;
    BEGIN
    UPDATE test_table SET v_name = new_name WHERE v_id = &input_id;
    EXCEPTION
    WHEN dup_val_on_index THEN
    dbms_output.put_line('在deptno列上不能出現重複值.');
    END;
    /

7. CASE_NOT_FOUND（case_not_found）該異常觸發ORA-06592錯誤。

在CASE語句中，如果CASE語句的條件在WHEN子句中沒有找到對應的條件分支，且該CASE語句不包含ELSE分支，將觸發該異常。

示例：

    DECLARE
    v_test NUMBER;
    BEGIN
    SELECT val INTO v_test FROM dual;
    CASE
    WHEN v_test = 1 THEN
    dbms_output.put_line(v_test);
    WHEN v_test = 2 THEN
    dbms_output.put_line(v_test);
    WHEN v_test = 3 THEN
    dbms_output.put_line(v_test);
    --ELSE
    -- dbms_output.put_line(v_test);
    END CASE;
    EXCEPTION
    WHEN case_not_found THEN
    dbms_output.put_line('在CASE語句中沒有與' || v_test || '相關的條件.');
    END;
    /

8.ZERO_DIVIDE（zero_divide）該異常觸發ORA-01476錯誤。

當在程式中使用0作為除數進行運算時，將觸發該異常。

示例：

    DECLARE
    v_test1 NUMBER := 100;
    v_test2 NUMBER := 0;
    v_test3 NUMBER := 0;
    BEGIN
    v_test3 := v_test1 / v_test2;
    dbms_output.put_line(v_test3);
    EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('0不能作為除數.');
    END;
    /

9. INVALID_NUMBER（invalid_number）該異常觸發ORA-01722錯誤。

數字或值錯誤，或字元到數值的轉換錯誤。

示例：

    BEGIN
    UPDATE emp SET sal = sal + '1oo'; -- 1oo
    EXCEPTION
    WHEN invalid_number THEN
    dbms_output.put_line('輸入的數字不正確.');
    END;
    /
    DECLARE
    v_test NUMBER;
    v_test2 NUMBER;
    BEGIN
    v_test := '100';
    v_test2 := '1a';
    dbms_output.put_line(v_test);
    EXCEPTION
    WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('輸入的數字不正確.');
    END;
    /

10. ACCESS_INTO_NULL（access_into_null）該異常觸發ORA-06530錯誤。

當程式中的物件還沒有先進行物件初始化的操作，就直接為物件的屬性賦值，將觸發該異常。

示例：

    DECLARE
    v_test test_type;
    BEGIN
    v_test.v_name := 'test';
    EXCEPTION
    WHEN access_into_null THEN
    dbms_output.put_line('首先初始化物件v_test');
    END;
    /

11. COLLECTION IS NULL（collection is null）該異常觸發ORA-06531錯誤。

在給集合元素賦值前，必須先初始化該集合元素，否則觸發該異常。

示例：

    DECLARE
    TYPE emp_ssn_array IS TABLE OF NUMBER/* INDEX BY BINARY_INTEGER*/;
    best_employees emp_ssn_array;
    BEGIN
    best_employees(0) := '123456';
    dbms_output.put_line('best_employees(0): ' || best_employees(0));
    EXCEPTION
    WHEN collection_is_null THEN
    dbms_output.put_line('必須初始化集合元素.');
    END;
    /

12. SUBSCRIPT_BEYOND_COUNT（subscript_beyond_count）該異常觸發ORA-06533錯誤。

當使用複合資料型別時，如果下標越界觸發該異常。

示例：

    DECLARE
    TYPE test_array IS VARRAY(20) OF NUMBER;
    v_test test_array;
    BEGIN
    v_test := test_array(123456);
    dbms_output.put_line('v_test(1): ' || v_test(2));
    EXCEPTION
    WHEN subscript_beyond_count THEN
    dbms_output.put_line('下標越界.');
    END;
    /

13.SUBSCRIPT_OUTSIDE_LIMIT（subscript_outside_limit）該異常觸發ORA-06532錯誤

當使用複合資料型別時，如果下標為負值時觸發該異常。

示例：

    DECLARE
    TYPE test_array IS VARRAY(20) OF NUMBER;
    v_test test_array;
    BEGIN
    v_test := test_array(123456);
    dbms_output.put_line('v_test(-1): ' || v_test(-1));
    EXCEPTION
    WHEN subscript_outside_limit THEN
    dbms_output.put_line('下標不能是負數.');
    END;
    /

14.LONIN_DENIED該異常觸發ORA_01017錯誤。

當PL/SQL連線資料庫時，如果密碼錯誤，將觸發該異常。

15. NOT_LOGGED_ON該異常觸發ORA-01012錯誤。

如果PL/SQL沒有連線資料庫，程式執行將觸發該異常。

16 PROGRAM_ERROR該異常觸發ORA-06501錯誤。

如果出現該異常，則表示PL/SQL的內部問題。使用者可能需要重新安裝資料字典和PL/SQL系統包。

17 ROWTYPE MISMATCH該異常觸發ORA-06504錯誤。

在賦值時，如果宿主遊標變數和PL/SQL遊標變數的返回型別不相容，將觸發該異常。

18 SELF_IF_NULL該異常觸發ORA-30625錯誤。

在使用物件型別時，如果在NULL示例上呼叫成員方法將觸發該異常。

19 STORAGE_ERROR該異常觸發ORA-06500錯誤。

當PL/SQL塊執行時，如果走出內在空間或內在被損壞則觸發該異常。

20 SYS_INVALID_ROWID該異常觸發ORA-01410錯誤。

當將字串轉變為ROWID時，如果使用了無效的字串則觸發該異常。

21 TIMEOUT_ON_RESOURCE該異常觸發ORA-00051錯誤。

Oracle在等待資源時出現超時錯誤時將觸發該異常。

22 ORA-04021 Oracle在等待資源時出現超時錯誤，該資源可能被其它session鎖住，此時將觸發該異常。

23 ORA-01791 不是Selected表示式

SELECT DISTINCT goodsid,
    barcode,
    depotid,
    goodsname
    FROM sa_sale
    WHERE depotid = '11'
    ORDER BY selldate

這句話執行的時候就有錯誤， 但把排序換成 order by Goodsid 或其它 Barcode,DepotId,GoodsName的時候均沒有錯誤，這是因為 selldate不在查詢結果欄位中，而且這個語句是distinct語句。所以會出現這個錯誤

下邊給出正確的解決辦法：

    SELECT goodsid,
    barcode,
    depotid,
    goodsname
    FROM (SELECT DISTINCT goodsid,
    barcode,
    depotid,
    goodsname,
    selldate
    FROM sa_sale
    WHERE depotid = '11')
    ORDER BY selldate

24 ORA-01002: fetch out of sequence當遊標中資料集已經取完，然後再一次進行FETCH操作時將觸發該異常。

25 PLS-00382: expression is of wrong type表示式型別錯誤。即在程式碼中賦予操作的左右兩邊的型別不等。

二 非預定義異常

使用非預定義異常的步驟如下：

定義異常 -> 關聯異常和錯誤 -> 引用例外

當定義Oracle錯誤和例外之間的關聯關係時，要使用偽過程EXCEPTION_INIT。

下面以處理ORA-02291錯誤為例說明：

    DECLARE
    e_integrity EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_integrity, -2291);
    BEGIN
    UPDATE emp SET deptno = dno WHERE empno = &eno;
    EXCEPTION
    WHEN e_integrity THEN
    dbms_output.put_line('該部門不存在.');
    END;
    /

三 自定義異常

使用預定義異常和非預定義異常，程式可以捕獲Oracle錯誤，並且在出現Oracle錯誤時自動觸發對應的異常。但是在實際應用中，可能還會遇到其它錯誤，這時，可以為特定的情況自定義異常，不過需要顯示觸發該異常：通過定義異常，然後關聯異常和錯誤，顯示觸發異常，最後在EXCEPTION中處理該異常。

示例：

    DECLARE
    l_error_message VARCHAR2(200);
    e_user_exception EXCEPTION;
    BEGIN
    ** ** ** ** **;
    IF '出現錯誤' THEN
    l_error_message := '定義錯誤資訊';
    RAISE e_user_exception;
    END IF;
    EXCEPTION
    WHEN e_user_exception THEN
    raise_application_error(-20001, l_error_message);
    END;
    /

四 例外函式

1 SQLCODE，SQLERRM在PL/SQL塊中出現Oracle錯誤時,通過使用例外函式可以取得錯誤號以及相關的錯誤訊息。

SQLCODE 返回Oracle錯誤號。

SQLERRM 返回錯誤號對應的錯誤訊息。

示例：

    DECLARE
    v_ename emp.ename%TYPE;
    BEGIN
    SELECT ename INTO v_ename FROM emp WHERE sal = &v_sal;
    dbms_output.put_line('僱員名:' || v_ename);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('不存在工資為' || v_sal || '僱員');
    WHEN OTHERS THEN
    dbms_output.put_line('錯誤號' || SQLCODE);
    dbms_output.put_line(SQLERRM);
    END;
    /

2 RAISE_APPLICATION_ERROR在儲存過程,函式和包中使用RAISE_APPLICATION_ERROR可以自定義錯誤號和訊息。

raise_application_error：用於自定義錯誤訊息(用於程式段中)

語法：

raise_application_error(error_number,message[,{TRUE | FALSE}]);

error_number : 錯誤號,範圍是: -20000 ~ -20999之間的負整數;

message : 錯誤訊息,長度不能超過2048位元組;

第三個可靠選引數,如果TRUE,該錯誤會被放在先前錯誤堆疊中;如果FALSE(預設),則替換先前所有錯誤.

示例：

    CREATE OR REPLACE PROCEDURE raise_comm
    (
    eno NUMBER,
    commission NUMBER
    ) IS
    v_comm emp.comm%TYPE;
    BEGIN
    SELECT comm INTO v_comm FROM emp WHERE emp = eno;
    IF v_comm IS NULL THEN
    raise_application_error(-20001, '該員工無補助.');
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('該僱員不存在.');
    END;