（轉）pl/sql開發異常處理

polestar123發表於2009-05-31
SQL
異常處理
=========
為了提高應用程式的健壯性,開發人員必須考慮程式可能出現的各種錯誤,並進行相應的處理.
Oracle中異常分為預定義例外,非預定義例外和自定義例外三種.

## 處理預定義異常
預定義異常是指由PL/SQL所提供的系統異常.當PL/SQL應用程式違反了Oralce規則或系統限制時,則會隱含的觸發一個內部異常.

# PL/SQL為開發人員提供了二十多個預定義異常:

1.ACCESS_INTO_NULL
該異常對應於ORA-06530錯誤.當開發物件型別應用時,如果沒有初始化物件,直接為物件屬性賦值,該異常觸發.

DECLARE
emp emp_type;
BEGIN
emp.name:='SCOTT';
EXCEPTION
WHEN ACCESS_INTO_NULL THEN
DBMS_OUTPUT.PUT_LINE('首先初始化物件emp');
END;
/

2.CASE_NOT_FOUND
對應於ORA-06592錯誤.在CASE語句時,如果WHEN子句沒有包含必須的條件分支,並且沒有倉含ELSE子句,被觸發.

DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=&&no;
CASE
WHEN v_sal?1000 THEN
UPDATE emp SET sal=sal+100 WHERE empno=&no;
WHEN v_sal?2000 THEN
UPDATE emp SET sal=sal+150 WHERE empno=&no;
WHEN v_sal?3000 THEN
UPDATE emp SET sal=sal+200 WHERE empno=&no;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('在CASE語句中沒有與'||v_sal||'相關的條件.');
END;
/

3.COLLECTION_IS_NULL
對應於ORA-06531錯誤.在給集合元素(巢狀表或VARRAY型別)賦值前,必須首先寢化集合元素.否則觸發該異常.
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(2) FROM emp
WHERE empno=&no;
DBMS_OUTPUT.PUT_LINE('僱員名: '||ename_table(2));
EXCEPTION
WHEN COLLECTION_IS_NULL THEN
DBMS_OUTPUT.PUT_LINE('必須初始化集合元素.');
END;
/

4.CURSOR_ALREADY_OPEN
對應於ORA-06511錯誤.當重新開啟已經開啟的遊標時,會隱含地觸發該異常.
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
OPEN emp_cursor;
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('遊標已經開啟.');
END;
/

5.DUP_VAL_ON_INDEX
對應於ORA-00001錯誤,當在惟一索引所對應的列上鍵入重複值時觸發.
BEGIN
UPDATE dept SET deptno=&new_no WHERE deptno=&old_no;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('在deptno列上不能出現重複值.');
END;
/

6.INVALID_CURSOR
對應於ORA-01001錯誤.當試圖在不合法的遊標上執行操作時觸發.如從未開啟的遊標取資料,關閉未開啟的遊標等.
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
FETCH emp_cursor INTO emp_record;
CLOSE emp_cursor;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('請檢查遊標是否已經開啟.');
END;
/

7.INVALID_NUMBER
對應於ORA-01722錯誤.當內嵌SQL語句不能有效地將字元轉變成數字時觸發.如數值100被寫成"1oo".
BEGIN
UPDATE emp SET sal=sal+'100';
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('輸入的數字不正確.');
END;
/

8.NO_DATA_FOUND
對應於ORA-01403錯誤.當執行SELECT INOT未返回行,或引用了索引表未初始化元素時觸發.
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE lower(ename)=lower('&name');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('不存在該僱員.');
END;
/


9.TOO_MANY_ROWS
對應於ORA-01422錯誤,當執行SELECT INTO語句時,如果返回超過一行觸發該異常.
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE sal=&sal;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回多行.');
END;
/

10.ZERO_DIVIDE
對應於ORA-01476錯誤.當執行PL/SQL塊時,如果使用資料值除0觸發該異常.
DECLARE
num1 INT:=100;
num2 INT:=0;
num3 NUMBER(6,2);
BEGIN
num2:=num1/num2;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('分母不能為0.');
END;
/

11.SUBSCRIPT_BEYOND_COUNT
對應於ORA-06533錯誤.當使用巢狀表或VARRAY元素時,如果下標越界觸發該異常.
DECLARE
TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(10);
emp_array emp_array_type;
BEGIN
emp_array:=emp_array_type('SCOTT','MARY');
DBMS_OUTPUT.PUT_LINE(emp_array(3));
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
DBMS_OUTPUT.PUT_LINE('下標越界.');
END;
/

12.SUBSCRIPT_OUTSIDE_LIMIT
對應於ORA-06532錯誤.當使用巢狀表或VARRAY元素時,如果下標為負值觸發該異常.
DECLARE
TYPE emp_array_type IS varray(20) OF VARCHAR2(10);
emp_array emp_array_type;
BEGIN
emp_array:=emp_array_type('SCOTT','MARY');
DBMS_OUTPUT.PUT_LINE(emp_array(-1));
EXCEPTION
WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
DBMS_OUTPUT.PUT_LINE('下標不能是負數.');
END;
/

13. VALUE_ERROR
對應於ORA-06502錯誤.當在PL/SQL塊中執行賦值操作時,如果變數長度不足則觸發該異常.
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=&no;
DBMS_OUTPUT.PUT_LINE(v_ename);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('變數長度不夠.');
END;
/


# 其它預定義異常.

1.LONIN_DENIED
對應於ORA-01017錯誤.當PL/SQL應用程式要連線Oracle資料庫時,如果密碼錯誤則觸發該異常.

2.NOT_LOGGED_ON
對應於ORA-01012錯誤.如果程式沒連線Oracle資料庫,那麼執行PL/SQL訪問資料庫時觸發該異常.

3.PROGRAM_ERROR
對應於ORA-06501.如果出現該錯誤,則表示PL/SQL內部問題,使用者可能需要重新安裝資料字典和PL/SQL系統包.

4.ROWTYPE MISMATCH
對應於ORA-06504錯誤.賦值時,宿主遊標變數和PL/SQL遊標變數的返回型別不相容時觸發該異常.

5.SELF_IF_NULL
對應於ORA-30625.當使用物件型別時,如果在NULL例項上呼叫成員方法則觸發該異常.

6.STORAGE_ERROR
對應於ORA-06500錯誤.PL/SQL塊執行時,如果走出內在空間或內在被損壞則觸發該異常.

7.SYS_INVALID_ROWID
對應於ORA-01410錯誤.當將字串轉變為ROWID時,如果使用了無效的字串則觸發該異常.

8.TIMEOUT_ON_RESOURCE
對應於ORA-00051錯誤.Oracle在等待資源超出現超時錯誤時觸發該異常.


## 非預定義異常
使用預定義異常,只能處理21個Oracle錯誤.而當使用PL/SQL開發應用程式時,可能還遇到其他的上結錯誤.
使用非預定義異常的步驟如下:
定義異常 -> 關聯異常和錯誤 -> 引用例外

當定義Oracle錯誤和例外之間的關聯關係時,要使用偽過程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;
/




# 處理自定義異常
預定義異常和非預定義異常都與Oracle錯誤有關,並且出現Oracle錯誤時會自動觸發.
而自定義異常與Oracle錯誤沒有任何關聯,它是開發人員為特定情況所定義的異常.
自定義異常必須顯式觸發.使用步驟如下:
定義異常 -> 顯式觸發異常 -> 引用異常
首先要在定義部分(DECLARE)定義異常,然後在執行部分(BEGIN)觸發異常(RAISE語句),
最後在異常處理部分(EXCEPTION)捕捉處理.如下:
DECLARE
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
e_no_employee EXCEPTION;
BEGIN
UPDATE emp SET deptno=&dno WHERE empno=&eno;
IF SQL%NOTFOUND THEN
RAISE e_no_employee;
END IF;
EXCEPTION
WHEN e_integrity THEN
DBMS_OUTPUT.PUT_LINE('該部門不存在.');
WHEN e_no_employee THEN
DBMS_OUTPUT.PUT_LINE('該僱員不存在.');
END;
/


# 使用異常函式
在PL/SQL塊中出現Oracle錯誤時,透過使用例外函式可以取得錯誤號以及相關的錯誤訊息,函式SQLCODE用於取得Oracle錯誤號.
SQLERRM則用於取得與之相關的錯誤訊息.另外,在儲存過程,函式和包中使用RAISE_APPLICATION_ERROR可以自定義錯誤號和訊息.

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;
/

# 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;
/

## PL/SQL編譯警告
在Oracle 10g之前,編寫PL/SQL子程式時,只要子程式符合SQL和PL/SQL的語法及主義規則,Oracle就會成功地編譯子程式.
如下 (DEAD CODE):
CREATE OR REPLACE PROCEDURE dead_code AS
x number := 10;
BEGIN
IF x=10 THEN
x=20;
ELSE
x:=100; --死程式碼
END IF;
END dead_code;
/

如例,ELSE子句永遠不會執行,應該避免出現類似的死程式碼.Oracle 10g開始,
在編寫PL/SQL子程式之前開發人員可以啟用警告檢查.

1.PL/SQL警告分類
SEVERE: 用於檢查可能出現的不可預料結果或錯誤結果,例如引數的別名問題.
PERFORMANCE: 用於檢查可能引起效能問題,如在INSERT操作是為NUMBER列提供了VARCHAR2型別資料.
INFORMATIONAL: 用於檢查程式中的死程式碼.
ALL: 用於檢查所有警告.

2.控制PL/SQL警告訊息
為了使得資料庫可以在編譯PL/SQL子程式時發出警告訊息,需要設定寢化引數PLSQL_WARNINGS.
不僅可以在系統級或會話級設定,也可以在ALTER PROCEDURE命令是進行設定.
既可以啟用或禁止所有警告型別,也可以啟用或禁止特定訊息號.如:
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> ALTER PROCEDURE hello COMPILE;
2> PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE',
2> 'DISABLE:PERFORMANCE','ERROR:06002';

啟用警告檢查後,編譯子程式時用SHOW ERRORS命令顯示警告錯誤[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70612/viewspace-1022619/，如需轉載，請註明出處，否則將追究法律責任。

相關文章