pl/sql 提供了強大而靈活的手段來捕捉和處理程式產生的異常，從而使 oracle 的使用者遠離一些令人煩惱的 bug 。[@more@]

pl/sql 異常處理的概念和術語

在 oracle 中所有的錯誤都被認為是不應該發生的異常。一個異常可能是以下 3 種情況的一種：

u 由系統產生的錯誤（“ out of memory ”或“ duplicate value in index ”）

u 使用者行為導致的錯誤

u 應用程式給使用者的一個警告

pl/sql 用一種異常控制程式碼的結構來捕捉和響應錯誤。正是有了異常控制程式碼的存在，我們能很方便的分離異常處理程式碼與可執行程式碼。與線性的程式碼相比，為了處理異常，異常控制程式碼提供了一種類似事件驅動的模式；換句話說，就是不管一種特定的錯誤在何時何地發生，它都將被同一個程式碼處理。

當一個錯誤出現後，無論它是系統還是程式產生的，都將導致一個異常。之後，可執行程式被中斷，控制權轉移給異常處理程式碼。處理完異常後，程式將不會回到先前被中斷的位置，相反的，控制權被交給了當前程式的外圍模組（可能是程式，也可能是系統）。

procedure jimmy

is

new_value varchar(35)

begin

|--------new_value:=old_value || ‘-new’;

| if new_value like ‘like%’

| then

| …..

| end if;

| exception

|----- à when value_error

then

…..

end;

因為 old_value 是一個未被定義的變數，所以將產生一個錯誤，並將給異常處理模組處理。

從異常的可應用範圍出發，可將異常分為兩類：

系統異常：

由 oracle 定義並由 pl/sql runtime 引擎在檢測到錯誤時產生的異常。一些系統異常有名字，比如 NO_DATA_FOUND ，然而大多數的異常僅僅只有數字編號和描述。這些異常無論在哪個 pl/sql 中程式都能被應用。

共有 21 個命名的系統異常：

命名的系統異常	產生原因
ACCESS_INTO_NULL	未定義物件
CASE_NOT_FOUND	CASE 中若未包含相應的 WHEN ，並且沒有設定 ELSE 時
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	遊標已經開啟
DUP_VAL_ON_INDEX	唯一索引對應的列上有重複的值
INVALID_CURSOR	在不合法的遊標上進行操作
INVALID_NUMBER	內嵌的 sql 語句不能將字元轉換為數字
NO_DATA_FOUND	使用 select into 未返回行，或應用索引表未初始化的元素時
TOO_MANY_ROWS	執行 select iotn 時，返回超過一行
ZERO_DIVIDE	除數為 0
SUBSCRIPT_BEYOND_COUNT	元素下標超過巢狀表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用巢狀表或 VARRAY 時，將下標指定為負數
VALUE_ERROR	賦值時，變數長度不足以容納實際資料
LOGIN_DENIED	Pl/sql 應用程式連線到 oracle 資料庫時，提供了不正確的使用者名稱或密碼
NOT_LOGGED_ON	Pl/sql 應用程式在沒有連線 oralce 資料庫的情況下訪問資料
PROGRAM_ERROR	Pl/sql 內部問題，可能需要重灌資料字典＆ pl./sql 系統包
ROWTYPE_MISMATCH	宿主遊標變數與 pl/sql 遊標變數的返回型別不相容
SELF_IS_NULL	使用物件型別時，在 null 物件上呼叫物件方法
STORAGE_ERROR	執行 pl/sql 時，超出記憶體空間
SYS_INVALID_ID	無效的 ROWID 字串
TIMEOUT_ON_RESOURCE	Oracle 在等待資源時超時

由程式設計師定義的異常：

程式設計師在程式中定義的異常，它只是在特定的程式種有效。可以使用 EXCEPTION_INT 這個 pragma 將一個無名字的系統異常與一個程式設計師定義的名字相關聯。或者用 RAISE_APPLICATION_ERROE 來自己定義一個異常的數字編號和描述。

按異常生成方式可分為：

預定義異常：

就是上面表中的 21 種有名字的系統異常。

非預定義異常：

沒名字的系統異常，可以用 pragma exception_int 給它關聯一個名字。

自定義異常：

需要用 RAISE 或 RAISE_APPLICATION_ERROR 生成的異常。

下面是一些要用到的屬於 ;

Exception section （異常處理模組）

它是 pl/sql 語句塊種包含一個或多個異常控制程式碼的部分。 Exception section 的結構基本上與 case 相似。

Raise （產生）

透過通知 pl/sql runtime 引擎有錯誤來中止當前程式的執行。也可透過顯式的請求，如： RAISE 或 RAISE_APPLICATION_ERROR 來 RAISE 一個異常。

Handle （控制程式碼，某一個異常處理的程式碼）

在 exception section 中捕捉錯誤。可以在 handle 中編寫程式來處理異常，比如將錯誤記入 log 中，顯示一個錯誤資訊，將異常傳出當前程式快。

Scope （作用範圍）

一個異常從產生、被捕捉到處理整個過程所處的程式部分。

Propagation （傳遞）

如果一個異常沒有被處理，那麼它將被傳遞到但前塊的上一級，它有可能是另一個程式碼快，也可能是系統。

Unhandled exception （未被處理的異常）

如果一個異常沒有被處理，並一直被傳遞道理系統中，那麼它被稱為 unhandled exception 。

Un-named or anonymous excepttion （匿名異常）

（在異常型別中有介紹）

Named exception （命名異常）

包括系統異常中有名字的那部分和使用者定義的名字。

-------------------------------------------------------------------------

pl/sql 提供了強大而靈活的手段來捕捉和處理程式產生的異常，從而使 oracle 的使用者遠離一些令人煩惱的 bug 。

異常定義

在一個異常產生、被捕獲並處理之前，它必須被定義。 Oracle 定義了幾千個異常，絕大多數只有錯誤編號和相關描述，僅僅命名了若干個最常被用到的異常。這些名字被儲存在 STANDARD ， UTL_FILE ， DBMS_SQL 這幾個系統包中，詳情請見 oracle:pl/sql 異常處理（ 1 ）。

出自之外的絕大多數異常需要程式設計師命名。有 2 種命名異常的方法：

1 ：宣告一個自定義異常

在 STANDARD 中的命名了的異常基本山是與系統的錯誤相關的（當然那些只有 errorcode 的異常也是這樣），但在實際的應用中我們經常需要與特定的應用程式相關的異常，由程式設計師宣告的異常就是用於處理這種情況的。

Oracle 異常處理模組的方便的地方在於，它並沒有區別對待自定義的與預定義的異常。這使得我們可以像對待預定義異常一樣，捕捉和處理自定義異常，只是在此之前需要宣告它；同時對於一個自定義的異常，我們需要用 RAISE 來手動產生。

下面是一個宣告的例子：

procedure calc_ammul_sales

(company_id_in in company.company_id%tye)

is

invalid_company_id exception;

negative_balance excrption;

duplicate_company Boolean;

begin

/*body of executable statement*/

exception

when invalid_company_id

then /*handle exception*/

when no_data_found

then /*handle exception*/

/*…..*/

end;

需要注意的是處理定義的時候，只有兩個地方會出現自定義的異常：

ü raise exception ；

ü when exception then

2 ：為非預定義異常關聯一個名字

僅僅 21 個預定義異常對我們來說實在是太少了，還有幾千個異常只有 errorcode 和描述。另外，程式設計師也可以用 RAISE_APPLICATION_ERROR 定義一個含 errorcode 和描述的異常。

當然，只用 errorcode 也可以很好地完成工作，只要你不擔心會忘了那串數字代表的意思就行。比方說 ;

exception

when others

then

if sqlcode=-1843 then /*sqlcode 是內建的用於返回最近一次錯誤編號的函式 */

…..

這的確是一段讓人感到晦澀的程式碼，還是給它關聯個名字吧。

我們要用到的是 pragma exception_init(exception,integer) ，然後就可以像對待預定義異常一樣對待它了，我是說沒必要像上面的那種一樣用 raise 。 Exception_init 是一個編譯時執行的函式，它只能出現在程式碼的宣告部分，而異常名字必須在此之前被定義。下面用一個匿名過程舉個例子：

declare

invalid_company_id exception;

pragma exception_init(invalid_company_id, -1834);

要注意的時：

ü 不可以用 -1403 （ no_data_found ），用 100 ，事實上 exception_init 中的 integer 對應的是 sqlcode 返回的值。

ü 不能為 0 ，不能大於 100 ，不能小於 -1000000

一個例子：

procedure delete_company(company_id_in in number)

is

still_have_emplyee exception;

pragma exception(still_have_employee, -2293);

begin

delete from compamy

where company_id=company_id_in;

exception

when still_have_employee

then dbms_output.put_line(‘delete employees for company first’);

end;

在一下兩種情況下，我們有必要使用 exception_init ：

ü 一個非預定義異常是經常要被用到的。

ü 我們將用 raise_applocation_error 產生了一個自定義的 errorcode 時。

一種簡便的方法是將以上兩種情況中的異常定義在一個包中，這樣我們就沒有必要每次都重複定義了。

Create or replace package dynsql

Is

Invalid_table_name exception;

Pragma exception_init(invalid_table_name, -903);

Invalid_column_name exception;

Pragma exception_init(invalid_column_name, -904);

En_too_young const number:=-200001;

Exc_too_young exception;

Pragma exception_init(exc_too_young, -20001);

End;

有了上面這個包，就可以方便的處理異常了 ;

procedure validate_emp(birthdate in date)

is

min_tear const pls_integer:=18;

begin

if add_month(sysdate,min_year*12*-1)

then

raise_application_error(dynsql.en_too_young, ‘employee must be’ || min_year ||‘old’);

end if;

end;

除了 standard 包中的 21 個預定義異常外，還有一些包也定義了一些異常。但與 standard 包中異常不同的是，在使用這些異常時，需要帶上包的名字。如：

when dbms_lob.invalid_argval then ……

非常有用的一點是，可以在最外層的 pl/sql 塊的異常處理模組中加入 others ，這樣就可以把從內部傳遞出來的未被處理的剩餘異常全部處理掉了。

Exception

When others

Then ….

----------------------------------------------------------------------

生成錯誤

處理 oracle 系統自動生成系統異常外，可以使用 raise 來手動生成錯誤。

l Raise exception;

l Raise package.exception;

l Raise;

以上是 raise 的三種使用方法。第一種用於生成當前程式中定義的異常或在 standard 中的系統異常。

Declare

Invalid_id exception;

Id_values varchar(2);

Begin

Id_value:=id_for(‘smith’);

If substr(id_value,1,1)!=’x’

Then

Raise invalid_id;

End if;

Exception

When invalid_id

Then

Dbms_output.put_line(‘this is an invalid id!’);

End;

這是一個生成自定義異常的例子，當然也可以生成系統異常：

declare

employee_id_in number;

Begin

Select employee_id into employee_id_in from employ_list where employee_name=&n;

If employee_id_in=0

Then

Raise zero_devided;

End if;

Exception

When zero_devided

Then

Dbms_output.put_line(‘wrong!’);

End;

有一些異常是定義在非標準包中的，如 UTL_FILE ， DBMS_SQL 以及程式設計師建立的包中異常。可以使用 raise 的第二種用法來生成異常。

If day_overdue(isbn_in, browser_in) > 365

Then

Raise overdue_pkg.book_is_lost

End if;

在最後一種 raise 的形式中，不帶任何引數。這種情況只出現在希望將當前的異常傳到外部程式時。

Exception

When no_data_found

Then

Raise;

End;

Pl.sql 使用 raise_application_error 過程來生成一個有具體描述的異常。當使用這個過程時，當前程式被中止，輸入輸出引數被置為原先的值，但任何 DML 對資料庫所做的改動將被保留，可以在之後用 rollback 命令回滾。下面是該過程的原型：

Procedure raise_application_error(

Num binary_integer;

Msg varchar2;

Keeperrorstack Boolean default false

)

其中 num 是在 -20999 到 -20000 之間的任何數字（但事實上， DBMS_OUPUT 和 DBMS_DESCRIBLE 包使用了 -20005 到 -20000 的數字）； msg 是小於 2K 個字元的描述語，任何大於 2K 的字元都將被自動丟棄； keeperrorstack 預設為 false ，是指清空異常棧，再將當前異常入棧，如果指定 true 的話就直接將當前異常壓入棧中。

CREATE OR REPLACE PROCEDURE raise_by_language (code_in IN PLS_INTEGER)

IS

l_message error_table.error_string%TYPE;

BEGIN

SELECT error_string

INTO l_message

FROM error_table, v$nls_parameters v

WHERE error_number = code_in

AND string_language = v.VALUE

AND v.parameter = 'NLS_LANGUAGE';

RAISE_APPLICATION_ERROR (code_in, l_message);

END;

-----------------------------------------------------------------------

異常處理

當異常生成之後，程式被中止，控制權交給異常處理模組，異常處理模組捕獲當前異常控制程式碼，並交由相應的程式處理；如果，異常促裡模組沒有捕捉到異常控制程式碼，那麼它將被傳輸到當前程式的外圍。

除非由一些特殊的要求，一般情況下異常將再當前程式的異常處理模組中被處理。異常處理模組以 EXCEPTION 開始 END; 結尾。

Declare

/*…………*/

begin

/*…………*/

exception

when /* 異常名稱 */

then /* 異常處理 */

when other

then /* 異常處理 */

end;

異常處理模組的語法基本上以 CASE 一致，凡是在 when 中有定義的異常都將被處理，而沒有的則被傳輸。一個特殊的異常處理語句是 WHEN OTHERS 。就想在（ 3 ）中所說的，它會處理所有為被處理的異常，因此必須小心使用它，最好是在最外層的程式中。當然如果喜歡偷懶的，大可以在異常處理模組中只放一個 OTHERS 。注意，無論哪種情況， OTHERS 只能這隻在異常處理的最後一位。

有趣的是，可以在一個 when 中處理多個異常控制程式碼。

Exception

When no_data_found or invalid_employee_id or dbms_ldap.invalid session

Then /*………..*/

End;

/

在這個例子裡，有標準包的異常、自定義異常和非標準包中的異常。這些異常只能用 or 連線，不可以用 and ，因為只有一個異常能夠生成。

非 raise_application_error 生成的異常，如果沒有被處理而一直傳遞到系統環境中，那麼環境將視情況作出相應的反映。在 sqlplus 中， oracle 將回滾所有 DML 對資料所做的修改。在 sqlplus 環境中，因為有自動回滾的存在，我們可以保留出現未被處理的異常的可能性；而在另外的一些環境中，則需要仔細設計最外層程式。

ü 捕捉任何有可能傳出的異常。

ü 記錄錯誤以便於分析。

ü 給外部環境一個資訊，以便於其作出相應的處理。

對於自定義異常，因為 sqlcode 值永遠是 1 ，所以當它被傳出時，如果外圍程式中沒有定義相同名稱的異常，我們將不知道是什麼異常產生了。因此，不要將自定義異常傳遞出去。

在程式中處理幾個互相獨立的操作時，為了避免出現因為一個操作產生異常而使整個程式被中斷的情況，有必要將這些獨立的操作放在各自的虛擬塊中。

Procedure change_data is

Begin

Delete from employee where …..

Exception

When others then null;

End;

Begin

Update company set …….

Exception

When others then null;

End;

Begin

Insert into company_history select * from company where ….

Exception

When others then null;

End;

/

Pl/sql 提供了一些內建的函式來幫助我們確定、分析異常。

SQLCODE

這個函式在前面有提到過，它是一個用於返回當前模組中最近一次異常值的函式，或者說是非入棧程式的異常值。打個比方：如果在當前程式的異常模組中呼叫了另一個程式， oracle 將當前程式及相應的環境變數（包括異常值）壓入系統棧；在被呼叫程式中生成了一個值為 1 的異常，那麼 sqlcode 將返回 1 ；之後剛才的程式出棧， sqlcode 返回當前異常值。需要注意的是，不要在異常模組之外使用它，這樣不會有任何意義。當沒有異常或在異常模組之外使用時， SQLCODE 返回 0 ；返回值 1 是指自定義異常。

SQLERRM

接收異常值，返回相應的長度不超過 512 位元組的描述語。如果沒有傳入異常值，則返回當前異常描述。

Begin

Dbms_output.put_line( sqlerrm(-1403);

End;

Sql>/

Ora-1403: no data found

在需要體構長度超過 512 位元組的描述時， oracle 建議使用 dbms_utility.format_error_stack 。顯然，用這個函式來判斷一個異常是否為系統異常是很有用的，如果不是的話，將返回以下兩種情況的一種。

如果是一個負數：

ora-nnnnn: message not found,; product=rdbms; facility=ora

如果是一個正數：

-nnnnn: non-oracle exception

DBMS_UTILITY.FORMAT_ERROR_STACK

返回當前異常相應的描述，沒有字元長度限制。與 SQLCODE 相同的是，必須在異常處理模組中使用。雖然名稱中有一個 stack 在，但透過它並不能知道異常的最初生成處，需要的話就必須使用 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 。

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

系統為最近一次生成的異常設定了一個棧，並跟蹤它的傳遞過程，而這個函式使用這個棧，然後返回該異常的整個傳遞過程。這個函式對錯誤的定位和實施下一步處理起著至關重要的作用。

Create or replace procedure procl is

Begin

Dbms_output.put_line(‘running proc1’);

Raise no_data_found;

End;

/

create or replace procedure proc2 is

begin

dbms_output.put_line(‘calling proc1’);

proc1;

end;

/

create or replace procedure proc3 is

begin

dbms_output.put_line(‘calling proc2’);

proc2;

exception

when no_data_found

then

dbms_output.put_line(‘error stack at top level’);

dbms_output.put_line(dbms_utility.format_error_backtrace);

end;

/

現在可以執行 proc3 來看看結果。

Sql>set serveroutput on;

Sql>begin

2 dbms_output.put_line(‘proc3->proc2->proc1 backtrace’);

3 proc3;

4 end;

5 /

Proc3 -> Proc2 -> Proc1 backtrace

calling proc2

calling proc1

running proc1

Error stack at top level:

ORA-06512: at "SCOTT.PROC1", line 4

ORA-06512: at "SCOTT.PROC2", line 5

ORA-06512: at "SCOTT.PROC3", line 4

事實上，每次異常的產生都將重置這個異常棧，只是最後一次從系統棧出棧的是最外層的程式塊，所以可以清楚地看到異常生成的整個過程。上面這個程式的執行過程是這樣的：首先用 put_line 列印 Proc3 -> Proc2 -> Proc1 backtrace ，呼叫 proc3 ，當前程式入棧 => 列印 calling proc2 ，呼叫 proc2 ， proc3 入棧 => 列印 calling proc1 ，呼叫 proc1 ， proc2 入棧 => 列印 running proc1 ，生成 no_data_found 異常，該異常被壓入異常棧中 => proc2 出棧，並檢測到來自第 5 行呼叫傳遞過來的異常，將它在此壓入異常棧 => proc3 出棧，並檢測到來自第 4 行呼叫傳遞過來的異常，將它在此壓入異常棧， dbms_utility.format_error_backtrace 將異常棧中資訊反相列印出來 => 最外層程式出棧， end 。

以下是正確使用這個函式的一些注意事項：

ü 在當前程式的異常處理模組中呼叫這個函式。

ü 避免在中間程式中使用異常處理模組。

這樣異常就能被正確地傳輸到最外層程式中，並列印出這個過程了。

pl/sql 異常處理的概念和術語

相關文章