【轉】Oracle 異常處理

控制PL/SQL錯誤 EXCEPTION，SQLCODE，SQLERRM --瀏覽時可透過查詢功能跳躍式的尋找自己要檢視的內容！

一、錯誤控制一覽

在PL/SQL中，警告或錯誤被稱為異常。異常可以是內部(執行時系統)定義的或是使用者定義的。內部定義的案例包括除零操作和記憶體溢位等。一些常見的內部異常都有一個預定義的名字，如ZERO_DIVIDE和STORAGE_ERROR等。對於其它的內部異常，我們可以手動為它們命名。

我們可以在PL/SQL塊、子程式或包的宣告部分自定義異常。例如，我們可以定義一個名為insufficient_funds的異常來標示帳戶透支的情況。與內部異常不同的是，使用者自定義異常必須有一個名字。

錯誤發生時，異常就會被丟擲。也就是說，正常的執行語句會被終止，控制權被轉到PL/SQL塊的異常控制部分或子程式的異常控制部分。內部異常會由執行時系統隱式地丟擲，而使用者定義異常必須顯式地用RAISE語句丟擲，RAISE語句也可以丟擲預定義異常。

為了控制被丟擲的異常，我們需要單獨編寫被稱為"exception handler"的異常控制程式。異常控制程式執行後，當前塊就會停止執行，封閉塊繼續執行下一條語句。如果沒有封閉塊，控制權會直接交給主環境。

下例中，我們為一家股票程式碼(Ticker Symbol)為XYZ的公司計算並儲存市盈率(price-to-earning)。如果公司的收入為零，預定義異常ZERO_DIVIDE就會被丟擲。這將導致正常的執行被終止，控制權被交給異常控制程式。可選的OTHERS處理器可以捕獲所有的未命名異常。

DECLARE
   pe_ratio   NUMBER (3, 1);
BEGIN
SELECT price / earnings
    INTO pe_ratio
    FROM stocks
   WHERE symbol = 'XYZ';   -- might cause division-by-zero error
INSERT INTO stats (symbol, ratio)
       VALUES ('XYZ', pe_ratio);
COMMIT;
EXCEPTION   -- exception handlers begin
WHEN ZERO_DIVIDE THEN   -- handles 'division by zero' error
    INSERT INTO stats (symbol, ratio)
         VALUES ('XYZ', NULL);
    COMMIT;
     ...
WHEN OTHERS THEN   -- handles all other errors
    ROLLBACK;
END;   -- exception handlers and block end here

上面的例子演示了異常控制，但對於INSERT語句的使用就有些低效了。使用下面的語句就要好一些：

INSERT INTO stats (symbol, ratio)
SELECT symbol, DECODE (earnings, 0, NULL, price / earnings)
    FROM stocks
   WHERE symbol = 'XYZ';

在下面這個例子中，子查詢為INSERT語句提供了資料。如果earnings是零的話，函式DECODE就會返回空，否則DECODE就會返回price與earnings的比值。

二、異常的優點

使用異常來控制錯誤有幾個優點。如果沒有異常控制的話，每次執行一條語句，我們都必須進行錯誤檢查：

BEGIN
SELECT ...
    -- check for ’no data found’ error
SELECT ...
    -- check for ’no data found’ error
SELECT ...
    -- check for ’no data found’ error

錯誤處理和正常的處理內容界限不明顯，導致程式碼混亂。如果我們不編寫錯誤檢查程式碼，一個錯誤就可能引起其它錯誤，有時還可能是一些無關錯誤。

但有了異常後，我們就能很方便的控制錯誤，而且不需要編寫多個檢查程式碼：

BEGIN
SELECT ...
SELECT ...
SELECT ...
   ...
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors

異常能把錯誤控制程式單獨分離出來，改善可讀性，主要的演算法不會受到錯誤恢復演算法影響。異常還可以提高可靠性。我們不需要在每一個可能出現錯誤的地方編寫錯誤檢查程式碼了，只要在PL/SQL塊中新增一個異常控制程式碼即可。這樣，如果有異常被丟擲，我們就可以確保它能夠被捕獲並處理。

三、預定義PL/SQL異常

當我們的PL/SQL程式與Oracle規則相沖突或超過系統相關(system-dependent)的限制時，內部異常就會被丟擲。每個Oracle錯誤都有一個錯誤編號，但異常只能按名稱捕獲，然後被處理。所以，PL/SQL把一些常見Oracle錯誤定義為異常。例如，如果SELECT INTO語句查詢不到資料時，PL/SQL就會丟擲預定義異常NO_DATA_FOUND。

要控制其它Oracle異常，我們可以使用OTHERS處理器。函式SQLCODE和SQLERRM在OTHERS處理器中特別有用，因為它們能返回Oracle錯誤編號和訊息。另外，我們還可以使用編譯指示(pragma)EXCEPTION_INIT把一個異常名稱和一個Oracle錯誤編號關聯起來。PL/SQL在STANDARD包中宣告瞭全域性預定義異常。所以，我們不需要自己宣告它們。我們可以為下面列表中命名的預定義異常編寫處理程式：

異常 Oracle錯誤號 SQLCODE值
ACCESS_INTO_NULL ORA-06530 -6530
CASE_NOT_FOUND ORA-06592 -6592
COLLECTION_IS_NULL ORA-06531 -6531
CURSOR_ALREADY_OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED ORA-01017 -1017
NO_DATA_FOUND ORA-01403 100
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
ROWTYPE_MISMATCH ORA-06504 -6504
SELF_IS_NULL ORA-30625 -30625
STORAGE_ERROR ORA-06500 -6500
SUBSCRIPT_BEYOND_COUNT ORA-06533 -6533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 -6532
SYS_INVALID_ROWID ORA-01410 -1410
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
VALUE_ERROR ORA-06502 -6502
ZERO_DIVIDE ORA-01476 -1476

預定義異常的簡要描述：

異常 丟擲時機
ACCESS_INTO_NULL 程式嘗試為一個未初始化(自動賦為null)物件的屬性賦值。
CASE_NOT_FOUND CASE語句中沒有任何WHEN子句滿足條件，並且沒有編寫ELSE子句。
COLLECTION_IS_NULL 程式嘗試呼叫一個未初始化(自動賦為null)巢狀表或變長陣列的集合方法(不包括EXISTS)，或者是程式嘗試為一個未初始化巢狀表或變長陣列的元素賦值。
CURSOR_ALREADY_OPEN 程式嘗試開啟一個已經開啟的遊標。一個遊標在重新開啟之前必須關閉。一個遊標FOR迴圈會自動開啟它所引用的遊標。所以，我們的程式不能在迴圈內部開啟遊標。
DUP_VAL_ON_INDEX 程式嘗試向一個有著唯一約束條件的資料庫欄位中儲存重複值。
INVALID_CURSOR 程式嘗試操作一個不合法的遊標，例如關閉一個未開啟的遊標。
INVALID_NUMBER 在一個SQL語句中，由於字串並不代表一個有效的數字，導致字串向數字轉換時會發生錯誤。(在過程化語句中，會丟擲異常VALUE_ERROR。)當FETCH語句的LIMIT子句表示式後面不是一個正數時，這個異常也會被丟擲。
LOGIN_DENIED 程式嘗試使用無效的使用者名稱和/或密碼來登入Oracle。
NO_DATA_FOUND SELECT INTO語句沒有返回資料，或者是我們的程式引用了一個巢狀表中被刪除了的元素或是索引表中未初始化的元素。SQL聚合函式，如AVG和SUM，總是能返回一個值或空。所以，一個呼叫聚合函式的SELECT INTO語句從來不會丟擲NO_DATA_FOUND異常。FETCH語句最終會取不到資料，當這種情況發生時，不會有異常丟擲的。
NOT_LOGGED_ON 程式沒有連線到Oracle就要呼叫資料庫。
PROGRAM_ERROR PL/SQL程式發生內部錯誤。
ROWTYPE_MISMATCH 賦值語句中使用的主遊標變數和PL/SQL遊標變數的型別不相容。例如，當一個開啟的主遊標變數傳遞到一個儲存子程式時，實參的返回型別和形參的必須一致。
SELF_IS_NULL 程式嘗試呼叫一個空例項的MEMBER方法。也就是內建引數SELF(它總是第一個傳遞到MEMBER方法的引數)是空。
STORAGE_ERROR PL/SQL執行時記憶體溢位或記憶體不足。
SUBSCRIPT_BEYOND_COUNT 程式引用一個巢狀表或變長陣列元素，但使用的下標索引超過巢狀表或變長陣列元素總個數。
SUBSCRIPT_OUTSIDE_LIMIT 程式引用一個巢狀表或變長陣列，但使用的下標索引不在合法的範圍內(如-1)。
SYS_INVALID_ROWID 從字串向ROWID轉換髮生錯誤，因為字串並不代表一個有效的ROWID。
TIMEOUT_ON_RESOURCE 當Oracle等待資源時，發生超時現象。
TOO_MANY_ROWS SELECT INTO語句返回多行資料。
VALUE_ERROR 發生算術、轉換、截位或長度約束錯誤。例如，當我們的程式把一個欄位的值放到一個字元變數中時，如果值的長度大於變數的長度，PL/SQL就會終止賦值操作並丟擲異常VALUE_ERROR。在過程化語句中，如果字串向數字轉換失敗，異常VALUE_ERROR就會被丟擲。(在SQL語句中，異常INVALID_NUMBER會被丟擲。)
ZERO_DIVIDE 程式嘗試除以0。

四、自定義PL/SQL異常

PL/SQL允許我們定義自己的異常。與預定義異常不同的是，使用者自定義異常必須宣告，並且需要用RAISE語句顯式地丟擲。

1、宣告PL/SQL異常

異常只能在PL/SQL塊、子程式或包的宣告部分宣告。下例中，我們宣告一個名為past_due的異常：

DECLARE
   past_due EXCEPTION;

異常和變數的宣告是相似的。但是要記住，異常是一種錯誤情況(error condition)，而不是資料項。與變數不同的是，異常不能出現在賦值語句或是SQL語句中。但是，變數的作用域規則也適用於異常。

2、PL/SQL異常的作用域規則

在同一個塊內，異常不能宣告兩次。但可以在不同的塊宣告相同的異常。

塊中宣告的異常對於當前塊來說是本地的，但對於當前塊的所有子塊來說是全域性的。因為塊只能引用本地或全域性的異常，所以封閉塊不能引用宣告在子塊中的異常。

如果我們在子塊中重新宣告瞭一個全域性的異常，本地宣告的異常的優先順序是要高於全域性的。所以，子塊就不能引用全域性的異常，除非全域性異常在它的所在塊中用標籤作了標記，這種情況下可以使用下面的語法來引用全域性異常：

block_label.exception_name

下例中演示了作用範圍規則：

DECLARE
   past_due   EXCEPTION;
   acct_num   NUMBER;
BEGIN
DECLARE   -- sub-block begins
     past_due   EXCEPTION;   -- this declaration prevails
     acct_num   NUMBER;
BEGIN
     ...
    IF ... THEN
      RAISE past_due;   -- this is not handled
    END IF;
END;   -- sub-block ends
EXCEPTION
WHEN past_due THEN   -- does not handle RAISEd exception
     ...
END;

上例中的封閉塊並不能捕獲丟擲來的異常，因為在子塊中宣告的past_due優先順序要高於封閉塊宣告的異常。雖然它們的名字相同，但實際上是兩個不同的past_due異常，就像兩個acct_num變數只是共享著相同的名字一樣，實際上它們是完全不同的兩個變數。因此，RAISE語句和WHEN子句所引用的是不同的異常。如果想讓封閉塊能捕獲到子塊中的past_due異常，我們就必須從子塊中刪除宣告，或是在封閉塊中新增OTHERS處理器。

3、把PL/SQL異常與編號關聯：編譯指示EXCEPTION_INIT

要想控制沒有預定義名稱的錯誤(通常為 ORA- 訊息)，我們就必須使用OTHERS處理器或編譯指示EXCEPTION_INIT。編譯指示就是能在編譯期而非執行時進行處理的編譯指令。

在PL/SQL中，編譯指示EXCPTION_INIT能告訴編譯器把異常名稱和錯誤編號關聯起來。這就能讓我們按名稱來引用所有的內部異常，併為它編寫特定的處理程式。在我們看到的錯誤棧或是錯誤訊息序列中，最頂層的就是我們能捕獲和處理的資訊。

我們可以把編譯指示EXCEPTION_INIT寫在PL/SQL塊、子程式或包的宣告部分，語法如下：

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

其中exception_name是已經宣告過的異常名稱，Oracle_error_number是Oracle錯誤編號。編譯指示必須和異常宣告處於同一個宣告中，並且只能在異常宣告之後出現。如下例所示：

DECLARE
   deadlock_detected   EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock_detected, -60);
BEGIN
   ...   -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN
    -- handle the error
     ...
END;

4、自定我們自己的錯誤訊息：過程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(預設值)，錯誤就會替代前面所有的錯誤。

RAISE_APPLICATION_ERROR是包DBMS_STANDARD的一部分，所以，我們對它的引用不需要新增限定修飾詞。

應用程式只能從一個正在執行的儲存子程式或方法中呼叫raise_application_error。在呼叫時，raise_application_error會結束子程式並把使用者定義的錯誤編號和訊息返回給應用程式。錯誤編號和訊息可以像其它的Oracle錯誤一樣被捕獲。

在下面的例子中，我們在僱員工資欄的內容為空的情況下呼叫raise_application_error：

CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS
   curr_sal   NUMBER;
BEGIN
SELECT sal
    INTO curr_sal
    FROM emp
   WHERE empno = emp_id;
IF curr_sal IS NULL THEN
     /* Issue user-defined error message. */
     raise_application_error (-20101, 'Salary is missing');
ELSE
    UPDATE emp
       SET sal = curr_sal + amount
     WHERE empno = emp_id;
END IF;
END raise_salary;

呼叫程式會得到一個PL/SQL異常，它能在OTHERS處理器中使用錯誤報告函式SQLCODE和SQLERRM來進行處理。同樣，我們也可以使用編譯指示EXCEPTION_INIT把raise_application_error返回的錯誤編號對映到異常本身。如下面的Pro*C例子所示：

EXEC SQL EXECUTE
   /* Execute embedded PL/SQL block using host
   variables my_emp_id and my_amount, which were
   assigned values in the host environment. */

DECLARE
   null_salary   EXCEPTION;
   /* Map error number returned by raise_application_error
   to user-defined exception. */
PRAGMA EXCEPTION_INIT (null_salary, -20101);
BEGIN
   raise_salary (:my_emp_id, :my_amount);
EXCEPTION
WHEN null_salary THEN
    INSERT INTO emp_audit
         VALUES (:my_emp_id, ...);
END;

END-EXEC;

這項技術能讓呼叫程式在特定的異常處理程式中控制錯誤。

5、重新宣告預定義異常

請記住，PL/SQL把預定義的異常作為全域性內容宣告在包STANDARD中，所以，我們沒有必要重新宣告它們。重新宣告預定義異常是錯誤的做法，因為我們的本地宣告會覆蓋掉全域性宣告。例如，如果我們宣告瞭一個invalid_number，當PL/SQL丟擲預定義異常INVALID_NUMBER時，我們為異常INVALID_NUMBER編寫的異常控制程式就無法正確地捕獲到它了。這種情況下，我們必須像下面這樣使用點標誌來指定預定義異常：

EXCEPTION
WHEN INVALID_NUMBER OR STANDARD.INVALID_NUMBER THEN
    -- handle the error
END;

五、如何丟擲PL/SQL異常

內部異常會由執行時系統隱式地丟擲，其中也包括使用編譯指示EXCEPTION_INIT與Oracle錯誤編號關聯起來的使用者自定義異常。但是，使用者自定義的異常就必須顯式地用RAISE語句丟擲。

1、使用RAISE語句丟擲異常

PL/SQL塊和子程式應該只在錯誤發生或無法完成正常程式處理的時候才丟擲異常。下例中，我們用RAISE語句丟擲一個使用者自定義的out_of_stack異常：

DECLARE
   out_of_stock     EXCEPTION;
   number_on_hand   NUMBER (4);
BEGIN
   ...
IF number_on_hand < 1 THEN
    RAISE out_of_stock;
END IF;
EXCEPTION
WHEN out_of_stock THEN
    -- handle the error
END;

我們也可以顯式地丟擲預定義異常。這樣，為預定義異常編寫的處理程式也就能夠處理其它錯誤了，示例如下：

DECLARE
   acct_type   INTEGER := 7;
BEGIN
IF acct_type NOT IN (1, 2, 3) THEN
    RAISE INVALID_NUMBER;   -- raise predefined exception
END IF;
EXCEPTION
WHEN INVALID_NUMBER THEN
    ROLLBACK;
END;
六、PL/SQL異常的傳遞

異常被丟擲時，如果PL/SQL在當前塊或子程式中沒有找到對應的異常控制程式，異常就會被繼續向上一級傳遞。也就是說異常會把它自身傳遞到後繼的封閉塊直到找到異常處理程式或是再也沒有可以搜尋到的塊為止。在後一種情況下，PL/SQL會向主環境丟擲一個未捕獲異常。

但是，異常是不能透過遠端過程呼叫(RPC)來傳遞的。因此，PL/SQL塊不能捕獲由遠端子程式丟擲的異常。
異常可以跨作用域傳遞，也就是說，它能夠超越宣告它的塊的範圍而存在。如下例所示：

BEGIN
   ...
DECLARE   -- sub-block begins
     past_due   EXCEPTION;
BEGIN
     ...
    IF ... THEN
      RAISE past_due;
    END IF;
END;   -- sub-block ends
EXCEPTION
   ...
WHEN OTHERS THEN
    ROLLBACK;
END;

因為異常past_due所在的塊並沒有專門針對它的處理程式，所以異常就被傳遞到封閉塊。但是，按照作用域規則，封閉塊是不能引用子塊宣告的異常。所以，只有OTHERS處理器才能捕獲到這個異常。如果沒有使用者定義異常的處理程式，呼叫這個程式就會得到下面的錯誤：

ORA-06510: PL/SQL: unhandled user-defined exception

七、重新丟擲PL/SQL異常

有時我們需要重新丟擲捕獲到異常，也就是說，我們想在本地處理之後再把它傳遞到封閉塊。比如，在異常發生的時候，我們可能需要回滾事務，然後在封閉塊中寫下錯誤日誌。

要重新丟擲異常，只要在本地處理程式中放置一個RAISE語句即可，示例如下：

DECLARE
   out_of_balance   EXCEPTION;
BEGIN
   ...
BEGIN   -- sub-block begins
     ...
    IF ... THEN
      RAISE out_of_balance;   -- raise the exception
    END IF;
EXCEPTION
    WHEN out_of_balance THEN
      -- handle the error
      RAISE;   -- reraise the current exception
END;   -- sub-block ends
EXCEPTION
WHEN out_of_balance THEN
    -- handle the error differently
     ...
END;

如果在RAISE語句中省略了異常名稱——只允許在異常處理程式中這樣做——程式就會把當前的異常重新丟擲。

八、處理PL/SQL異常

異常丟擲時，PL/SQL塊或子程式的正常執行就會停止，控制權轉到塊或子程式的異常處理部分，語法如下：

EXCEPTION
WHEN exception_name1 THEN   -- handler
     sequence_of_statements1
WHEN exception_name2 THEN   -- another handler
     sequence_of_statements2
     ...
WHEN OTHERS THEN   -- optional handler
     sequence_of_statements3
END;

為捕獲丟擲的異常，我們需要編寫異常處理程式。每個處理程式都由一個WHEN子句和語句序列組成。這些語句執行完畢後，塊或子程式就會結束，控制權不再返回異常被拋起的地方。換句話說，也就是我們不能再次返回異常發生的地方繼續執行我們的程式。

可選的OTHERS處理器總是塊或子程式的最後一個處理程式，它可以用於捕獲所有的未命名異常。因此，塊或子程式只能有一個OTHERS處理器。如下例所示，OTHERS處理器能夠保證所有的異常都會被控制：

EXCEPTION
WHEN ... THEN
    -- handle the error
WHEN ... THEN
    -- handle the error
WHEN OTHERS THEN
    -- handle all other errors
END;

如果我們想讓兩個或更多的異常執行同樣的語句序列，只需把異常名稱用關鍵字OR隔開，放在同一個WHEN子句中即可，如下例所示：

EXCEPTION
WHEN over_limit OR under_limit OR VALUE_ERROR THEN
-- handle the error

只要在WHEN子句的異常列表中有一項與被丟擲異常相匹配，相關的語句序列就會被執行。關鍵字OTHERS不能出現在異常名稱列表中；它只能單獨使用。我們可以有任意數量的異常處理程式，而且每個處理程式都與一個異常列表及其對應的語句序列相關聯。但是，異常名稱只能在塊或子程式的異常處理部分出現一次。

變數作用範圍的規則在這裡也同樣適用，所以我們可以在異常處理程式中引用本地或全域性變數。但是，當遊標FOR迴圈中有異常丟擲時，遊標就會在異常處理程式呼叫之前被隱式地關閉。因此，顯式遊標的屬性值在異常處理程式中就不再可用了。

1、宣告中控制異常

如果在宣告時使用了錯誤的初始化表示式也有可能引發異常。例如，下面的宣告就是因常量credit_limit不能儲存超過999的數字而丟擲了異常：

DECLARE
   credit_limit CONSTANT NUMBER(3) := 5000;   -- raises an exception
BEGIN
   ...
EXCEPTION
WHEN OTHERS THEN   -- cannot catch the exception
   ...
END;

當前塊中的處理程式並不能捕獲到丟擲的異常，這是因為宣告時丟擲的異常會被立即傳遞到最近的封閉塊中去。

2、異常控制程式碼中控制異常

在一個塊或子程式中，一次只能有一個異常被啟用。所以，一個被異常處理程式丟擲的異常會被立即傳遞到封閉塊，在那兒，封閉塊會為它查詢新的處理程式。從那一刻起，異常傳遞才開始正常化。參考下面的例子：

EXCEPTION
WHEN INVALID_NUMBER THEN
    INSERT INTO ...   -- might raise DUP_VAL_ON_INDEX
WHEN DUP_VAL_ON_INDEX THEN ...   -- cannot catch the exception
END;

3、異常分支

GOTO語句不能跳轉到異常控制程式。同樣，GOTO語句也不能從異常控制程式跳轉到當前塊。例如，下面的GOTO語句就是非法的：

DECLARE
   pe_ratio   NUMBER (3, 1);
BEGIN
DELETE FROM stats
        WHERE symbol = 'xyz';
SELECT price / NVL (earnings, 0)
    INTO pe_ratio
    FROM stocks
   WHERE symbol = 'xyz';

   <>
INSERT INTO stats (symbol, ratio)
       VALUES ('xyz', pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
     pe_ratio   := 0;
    GOTO my_label;   -- illegal branch into current block
END;

但是，GOTO語句可以從一個異常控制程式中跳轉到一個封閉塊。
4、獲取錯誤代號與訊息：SQLCODE和SQLERRM

在異常處理程式中，我們可以使用內建函式SQLCODE和SQLERRM來查出到底發生了什麼錯誤，並能夠獲取相關的錯誤資訊。對於內部異常來說，SQLCODE會返回Oracle錯誤編號。SQLCODE返回的總是一個負數，除非發生的Oracle錯誤是沒有找到資料，這時返回的是+100。SQLERRM會返回對應的錯誤訊息。訊息是以Oracle錯誤編號開頭的。

如果我們沒有使用編譯指令EXCEPTION_INIT把異常與編號關聯的話，SQLCODE和SQLERRM就會分別返回+1和訊息"User-Defined Exception"。Oracle錯誤訊息最大長度是512個字元，其中包括錯誤編號、巢狀訊息和具體表和欄位的名稱。

如果沒有異常丟擲，SQLCODE返回0，SQLERRM返回訊息"ORA-0000: normal, successful completion"。

我們可以把錯誤編號傳遞給SQLERRM，讓它返回對應的錯誤訊息。但是，一定要保證我們傳遞給SQLERRM的錯誤編號是負數。下例中，我們把一個正數傳遞給SQLERRM，結果就不是我們想要的那樣的了：

DECLARE
   err_msg   VARCHAR2(100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1 .. 9999 LOOP
     err_msg     := SQLERRM(err_num);   -- wrong; should be -err_num

    INSERT INTO ERRORS
         VALUES (err_msg);
END LOOP;
END;

把正數傳給SQLERRM時，如果傳遞的是+100，返回的結果是"no data found"，其他情況總是會返回訊息"user-defined exception"。把0傳遞給SQLERRM，就會返回訊息"normal, successful completion"。

我們不能直接在SQL語句中使用SQLCODE或SQLERRM。我們必須先把它們的值賦給本地變數，然後再在SQL中使用變數，如下例所示：

DECLARE
   err_num   NUMBER;
   err_msg   VARCHAR2(100);
BEGIN
   ...
EXCEPTION
WHEN OTHERS THEN
     err_num     := SQLCODE;
     err_msg     := SUBSTR(SQLERRM, 1, 100);

    INSERT INTO ERRORS
         VALUES (err_num, err_msg);
END;

字串函式SUBSTR可以保證用SQLERRM為err_msg賦值時不會引起VALUE_ERROR異常。函式SQLCODE和SQLERRM在OTHERS異常處理程式中特別有用，因為它們能讓我們知道哪個內部異常被丟擲。

注意：在使用編譯指示RESTRICT_REFERENCES判斷儲存函式的純度時，如果函式呼叫了SQLCODE和SQLERRM，我們就不能指定約束為WNPS和RNPS了。

5、捕獲未控制異常

記住，如果被丟擲的異常找不到合適的異常控制程式，PL/SQL會向主環境丟擲一個未捕獲的異常錯誤，然後由主環境決定如何處理。例如，在Oracle預編譯程式環境中，任何一個執行失敗的SQL語句或PL/SQL塊所涉及到的改動都會被回滾。

未捕獲也能影響到子程式。如果我們成功地從子程式中退出，PL/SQL就會把值賦給OUT引數。但是，如果我們因未捕獲異常而退出程式，PL/SQL就不會為OUT引數進行賦值。同樣，如果一個儲存子程式因異常而執行失敗，PL/SQL也不會回滾子程式所做的資料變化。

我們可以在每個PL/SQL程式的頂級使用OTHERS控制程式碼來捕獲那些沒有被子程式捕捉到的異常。

九、PL/SQL錯誤控制技巧

這裡，我們將學習三個提高程式靈活性的技巧。

1、模擬TRY..CATCH..塊

異常控制程式能讓我們在退出一個塊之前做一些恢復操作。但是在異常程式完成後，語句塊就會終止。我們不能從異常控制程式碼再重新回到當前塊。例如，如果下面的SELECT INTO語句引起了ZERO_DIVIDE異常，我們就不能執行INSERT語句了：

DECLARE
   pe_ratio   NUMBER(3, 1);
BEGIN
DELETE FROM stats
        WHERE symbol = 'XYZ';

SELECT price / NVL(earnings, 0)
    INTO pe_ratio
    FROM stocks
   WHERE symbol = 'XYZ';

INSERT INTO stats(symbol, ratio)
       VALUES ('XYZ', pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
     ...
END;

其實我們可以控制某一條語句引起的異常，然後繼續下一條語句。只要把可能引起異常的語句放到它自己的子塊中，並編寫對應的異常控制程式。一旦在子塊中有錯誤發生，它的本地異常處理程式就能捕獲並處理異常。當子塊結束時，封閉塊程式會繼續執行緊接著的下一條語句。如下例：

DECLARE
   pe_ratio   NUMBER(3, 1);
BEGIN
DELETE FROM stats
        WHERE symbol = 'XYZ';

BEGIN   -- sub-block begins
    SELECT price / NVL(earnings, 0)
      INTO pe_ratio
      FROM stocks
     WHERE symbol = 'XYZ';
EXCEPTION
    WHEN ZERO_DIVIDE THEN
       pe_ratio     := 0;
END;   -- sub-block ends

INSERT INTO stats(symbol, ratio)
       VALUES ('XYZ', pe_ratio);
EXCEPTION
WHEN OTHERS THEN
     ...
END;

在上面這個例子中，如果SELECT INTO語句丟擲了ZERO_DIVIDE異常，本地異常處理程式就會捕捉到它並把pe_ratio賦值為0。當處理程式完成時，子塊也就終止，INSERT語句就會被執行。

2、反覆執行的事務

異常發生後，我們也許還不想放棄我們事務，仍想重新嘗試一次。這項技術的實現方法就是：

把事務裝入一個子塊中。
把子塊放入一個迴圈，然後反覆執行事務
在開始事務之前標記一個儲存點。如果事務執行成功的話，就提交事務並退出迴圈。如果事務執行失敗，控制權就會交給異常處理程式，事務回滾到儲存點，然後重新嘗試執行事務。
如下例所示。當異常處理程式完成時，子塊終止，控制權被交給外圍塊的LOOP語句，子塊再次重新開始執行。而且，我們還可以用FOR或WHILE語句來限制重做的次數。

DECLARE
   NAME     VARCHAR2(20);
   ans1     VARCHAR2(3);
   ans2     VARCHAR2(3);
   ans3     VARCHAR2(3);
   suffix   NUMBER        := 1;
BEGIN
   ...
LOOP   -- could be FOR i IN 1..10 LOOP to allow ten tries
    BEGIN   -- sub-block begins
      SAVEPOINT start_transaction;   -- mark a savepoint

      /* Remove rows from a table of survey results. */
      DELETE FROM results
            WHERE answer1 = ’no’;

      /* Add a survey respondent’s name and answers. */
      INSERT INTO results
           VALUES (NAME, ans1, ans2, ans3);

      -- raises DUP_VAL_ON_INDEX if two respondents have the same name
      COMMIT;
      EXIT;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK TO start_transaction;   -- undo changes
         suffix     := suffix + 1;   -- try to fix problem
         NAME       := NAME || TO_CHAR(suffix);
    END;   -- sub-block ends
END LOOP;
END;

3、使用定位變數標記異常發生點

只用一個異常控制程式碼來捕獲一系列語句的話，可能無法知道到底是哪一條語句產生了錯誤：

BEGIN
SELECT ...
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN ...
-- Which SELECT statement caused the error?
END;

要想解決這個問題，我們可以使用一個定位變數來跟蹤執行語句，例如：

DECLARE
   stmt INTEGER := 1;   -- designates 1st SELECT statement
BEGIN
SELECT ...
   stmt := 2;   -- designates 2nd SELECT statement
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
    INSERT INTO errors VALUES ('Error in statement ' || stmt);
END;