Oracle異常處理異常處理是考驗一個應(yīng)用程序健壯性的最好方式，開發(fā)人員必須考慮程序中可能出現(xiàn)的各種錯誤，并進行相應(yīng)的處理。
Oracle中異常分為：1.預(yù)定義異常2.非預(yù)定義異常3.自定義異常三種。
一、預(yù)定義異常預(yù)定義異常是指由PL/SQL所提供的系統(tǒng)異常。當PL/SQL應(yīng)用程序違反了Oracle規(guī)則或出現(xiàn)其它系統(tǒng)限制的情況時，將會隱含地觸發(fā)一個內(nèi)部異常。以下是PL/SQL為我們預(yù)定義的異常(經(jīng)常更新中)：
1.CURSOR_ALREADY_OPEN（cursor_already_open）該異常觸發(fā)ORA-06511錯誤。
當程序中的一個游標已經(jīng)執(zhí)行了打開操作，如果開發(fā)人員試圖再一次打開這個已經(jīng)打開的游標時，將觸發(fā)該異常。
示例：
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('游標已經(jīng)打開，不能再次對游標執(zhí)行打開操作。');
END;
/
2. INCALID_CURSOR（incalid_cursor）該異常觸發(fā)ORA-01001錯誤。
當試圖對一個尚未打開的游標執(zhí)行任何操作，如打開該非法的游標執(zhí)行賦值操作，或者關(guān)閉未打開的游標時，將觸發(fā)該異常。
示例：
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('請檢查游標是否已經(jīng)打開.');
END;
/
3.NO_DATA_FOUND（no_data_found）該異常觸發(fā)ORA-01403錯誤。
當讀取一個游標進行賦值操作(SELECT INTO操作)時，如果未返回任何行，將觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-01422錯誤。
上面是沒有返回記錄，這里是返回對條記錄的時候會觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-06502錯誤。
當在PL/SQL程序中執(zhí)行賦值操作時，如果右邊值的長度大于左邊變量的長度，將觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-00001錯誤。
若表的每一列聲明為主鍵，或具有惟一性，如果對該列插入重復(fù)的值時將觸發(fā)該異常。
示例：
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列上不能出現(xiàn)重復(fù)值.');
END;
/
7. CASE_NOT_FOUND（case_not_found）該異常觸發(fā)ORA-06592錯誤。
在CASE語句中，如果CASE語句的條件在WHEN子句中沒有找到對應(yīng)的條件分支，且該CASE語句不包含ELSE分支，將觸發(fā)該異常。
示例：
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 || '相關(guān)的條件.');
END;
/
8.ZERO_DIVIDE（zero_divide）該異常觸發(fā)ORA-01476錯誤。
當在程序中使用0作為除數(shù)進行運算時，將觸發(fā)該異常。
示例：
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不能作為除數(shù).');
END;
/
9. INVALID_NUMBER（invalid_number）該異常觸發(fā)ORA-01722錯誤。
數(shù)字或值錯誤，或字符到數(shù)值的轉(zhuǎn)換錯誤。
示例：
BEGIN
UPDATE emp SET sal = sal + '1oo'; -- 1oo
EXCEPTION
WHEN invalid_number THEN
dbms_output.put_line('輸入的數(shù)字不正確.');
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('輸入的數(shù)字不正確.');
END;
/
10. ACCESS_INTO_NULL（access_into_null）該異常觸發(fā)ORA-06530錯誤。
當程序中的對象還沒有先進行對象初始化的操作，就直接為對象的屬性賦值，將觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-06531錯誤。
在給集合元素賦值前，必須先初始化該集合元素，否則觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-06533錯誤。
當使用復(fù)合數(shù)據(jù)類型時，如果下標越界觸發(fā)該異常。
示例：
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）該異常觸發(fā)ORA-06532錯誤
當使用復(fù)合數(shù)據(jù)類型時，如果下標為負值時觸發(fā)該異常。
示例：
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('下標不能是負數(shù).');
END;
/
14.LONIN_DENIED該異常觸發(fā)ORA_01017錯誤。
當PL/SQL連接數(shù)據(jù)庫時，如果密碼錯誤，將觸發(fā)該異常。
15. NOT_LOGGED_ON該異常觸發(fā)ORA-01012錯誤。
如果PL/SQL沒有連接數(shù)據(jù)庫，程序運行將觸發(fā)該異常。
16 PROGRAM_ERROR該異常觸發(fā)ORA-06501錯誤。
如果出現(xiàn)該異常，則表示PL/SQL的內(nèi)部問題。用戶可能需要重新安裝數(shù)據(jù)字典和PL/SQL系統(tǒng)包。
17 ROWTYPE MISMATCH該異常觸發(fā)ORA-06504錯誤。
在賦值時，如果宿主游標變量和PL/SQL游標變量的返回類型不兼容，將觸發(fā)該異常。
18 SELF_IF_NULL該異常觸發(fā)ORA-30625錯誤。
在使用對象類型時，如果在NULL示例上調(diào)用成員方法將觸發(fā)該異常。
19 STORAGE_ERROR該異常觸發(fā)ORA-06500錯誤。
當PL/SQL塊運行時，如果走出內(nèi)在空間或內(nèi)在被損壞則觸發(fā)該異常。
20 SYS_INVALID_ROWID該異常觸發(fā)ORA-01410錯誤。
當將字符串轉(zhuǎn)變?yōu)镽OWID時，如果使用了無效的字符串則觸發(fā)該異常。
21 TIMEOUT_ON_RESOURCE該異常觸發(fā)ORA-00051錯誤。
Oracle在等待資源時出現(xiàn)超時錯誤時將觸發(fā)該異常。
22 ORA-04021Oracle在等待資源時出現(xiàn)超時錯誤，該資源可能被其它session鎖住，此時將觸發(fā)該異常。
23 ORA-01791 不是Selected表達式SELECT DISTINCT goodsid,
barcode,
depotid,
goodsname
FROM sa_sale
WHERE depotid = '11'
ORDER BY selldate
這句話執(zhí)行的時候就有錯誤， 但把排序換成order by Goodsid 或其它Barcode,DepotId,GoodsName的時候均沒有錯誤，這是因為selldate不在查詢結(jié)果字段中，而且這個語句是distinct語句。所以會出現(xiàn)這個錯誤
下邊給出正確的解決辦法：
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當游標中數(shù)據(jù)集已經(jīng)取完，然后再一次進行FETCH操作時將觸發(fā)該異常。
25 PLS-00382: expression is of wrong type表達式類型錯誤。即在代碼中賦予操作的左右兩邊的類型不等。
二 非預(yù)定義異常使用非預(yù)定義異常的步驟如下：
定義異常-> 關(guān)聯(lián)異常和錯誤-> 引用例外
當定義Oracle錯誤和例外之間的關(guān)聯(lián)關(guān)系時，要使用偽過程EXCEPTION_INTI。
下面以處理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;
/
三 自定義異常使用預(yù)定義異常和非預(yù)定義異常，程序可以捕獲Oracle錯誤，并且在出現(xiàn)Oracle錯誤時自動觸發(fā)對應(yīng)的異常。但是在實際應(yīng)用中，可能還會遇到其它錯誤，這時，可以為特定的情況自定義異常，不過需要顯示觸發(fā)該異常：通過定義異常，然后關(guān)聯(lián)異常和錯誤，顯示觸發(fā)異常，最后在EXCEPTION中處理該異常。
示例：
DECLARE
l_error_message VARCHAR2(200);
e_user_exception EXCEPTION;
BEGIN
** ** ** ** **;
IF '出現(xiàn)錯誤' THEN
l_error_message := '定義錯誤信息';
RAISE e_user_exception;
END IF;
EXCEPTION
WHEN e_user_exception THEN
raise_application_error(-20001, l_error_message);
END;
/
四 例外函數(shù)1 SQLCODE，SQLERRM在PL/SQL塊中出現(xiàn)Oracle錯誤時,通過使用例外函數(shù)可以取得錯誤號以及相關(guān)的錯誤消息。
SQLCODE 返回Oracle錯誤號。
SQLERRM 返回錯誤號對應(yīng)的錯誤消息。
示例：
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在存儲過程,函數(shù)和包中使用RAISE_APPLICATION_ERROR可以自定義錯誤號和消息。
raise_application_error：用于自定義錯誤消息(用于程序段中)
語法：
raise_application_error(error_number,message[,{TRUE | FALSE}]);
error_number : 錯誤號,范圍是: -20000 ~ -20999之間的負整數(shù);
message : 錯誤消息,長度不能超過2048字節(jié);
第三個可靠選參數(shù),如果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;
/
