一種PL/SQL程序包失效的解決方案

彭秋生

（廣東省郵政信息技術(shù)局 中國 廣州 510898）

在應(yīng)用程序與數(shù)據(jù)庫交互的過程中，當(dāng)數(shù)據(jù)量較大時，通常都提倡盡可能地使用存儲過程（stored procedure），以減少交互過程產(chǎn)生的通信消耗。在oracle中，存儲過程主要是指使用PL/SQL語言編寫的子程序（subprogram），它包含了過程（procedures）、函數(shù)（function）和程序包（package，下文簡稱包）。在實(shí)際的程序設(shè)計過程中，程序員使用最多的就是程序包。這不僅因?yàn)榘哂心K化、信息隱藏、效率高等優(yōu)勢，更重要的是它具有面向?qū)ο蟪绦蛟O(shè)計語言的特點(diǎn)[1]。包將邏輯相關(guān)的變量、常量、自定義數(shù)據(jù)類型、過程、函數(shù)、游標(biāo)等PL/SQL塊和域元素組織封裝在一起，使用起來相當(dāng)簡潔和方便，與面向?qū)ο笾械念惥哂挟惽ぶ睢?/p>

在使用包的過程中，很多人都會遇到的一個令人討厭的錯 誤 :“ORA-04068:existing state of packages has been discarded”。這個錯誤是Oracle認(rèn)為程序包狀態(tài)無效時拋出的。盡管oracle并不把它看作一種錯誤，而是更多地把它當(dāng)作一種警告[2]，但它的危害不小，需要仔細(xì)研究并加以解決。

1 錯誤的產(chǎn)生

我們先模擬錯誤的產(chǎn)生，整個模擬過程不妨稱為“實(shí)驗(yàn)1”。

在oracle數(shù)據(jù)庫中創(chuàng)建兩個包：pkg_con和 pkg_app，前者只有包說明（specification，即包頭），沒有包的實(shí)現(xiàn)（body，即包體）；后者既有包說明，又有包體[3]。pkg_app引用了pkg_con中的常量值。兩個程序包的創(chuàng)建過程分別如下：

SQL>create or replace package pkg_con as

2 char_yes constant char:='2'；

3 end pkg_con；

程序包已創(chuàng)建。

SQL>create or replace package pkg_app as

2 procedure prt；

3 end pkg_app；

程序包已創(chuàng)建。

SQL>create or replace package body pkg_app as

2 procedure prt is

3 begin

4 --輸出pkg_con中的值

5 dbms_output.put_line（pkg_con.char_yes）；

6 end prt；

7 end pkg_app；

程序包主體已創(chuàng)建。

顯然，剛剛新創(chuàng)建的兩個程序包狀態(tài)都是正常的，沒有任何問題。接下來在SQL*Plus中登錄兩個會話（session）：會話A和會話B。會話B先連續(xù)一次或多次執(zhí)行包pkg_app的過程prt，都不會發(fā)生錯誤。若會話A重建pkg_con包，會話B再執(zhí)行過程pkg_app.prt，則會發(fā)生錯誤，如下實(shí)驗(yàn)1所示。

會話A

SQL>create or replace package pkg_con as

2 char_yes constant char:= ‘2’；

3 end pkg_con；

程序包已創(chuàng)建。

會話 B：

SQL>exec pkg_app.prt；

BEGIN pkg_app.prt； END；

*

ERROR位于第1行:

ORA-04068:已丟棄程序包的當(dāng)前狀態(tài)

ORA-04061:package“SCOTT.PKG_CON”的當(dāng)前狀態(tài)失效

ORA-04065:未執(zhí)行，已更改或刪除 package"SCOTT.PKG_CON"

ORA-06508:PL/SQL:無法在調(diào)用之前找到程序單元

ORA-06512:在"SCOTT.PKG_APP"，line 4

ORA-06512:在line 1

從oracle給出錯誤描述看，發(fā)生錯誤的原因是當(dāng)前執(zhí)行包的狀態(tài)失效了。包的狀態(tài)可以在user_objects數(shù)據(jù)字典視圖中查詢到[4]。如果在重建包pkg_con之后，立即查一下包pkg_app的狀態(tài)，就會發(fā)現(xiàn)包體狀態(tài)已變成了無效（INVALID）。

2 對象依賴與自動重新編譯機(jī)制

正常情況下，除了本身的語法、語義問題，包失效最常見原因是它所引用的對象發(fā)生了變化，這是由oracle的對象依賴機(jī)制決定[5]。oracle數(shù)據(jù)字典會實(shí)時跟蹤各種對象以及對象之間相關(guān)性。包重建后，與之相關(guān)的其它對象的狀態(tài)會立即變?yōu)槭?；同時這種失效還會立即反映到其它會話中，導(dǎo)致其它會話中本地存儲的對象副本狀態(tài)也立即變?yōu)槭?。在?shí)驗(yàn)1中，包pkg_app引用了包pkg_con中的常量值，兩者之間存在依賴關(guān)系。會話A對包pkg_con進(jìn)行重建，數(shù)據(jù)字典實(shí)時跟蹤到這一情況，馬上將相關(guān)包pkg_app的狀態(tài)置為失效，同時立即找到pkg_app所在的會話B，將會話B中存儲在本地的副本也置為失效，最終導(dǎo)致了此次“ORA-04068”錯誤的發(fā)生。

但是，從另一個方面來說，oracle是具有自動重新編譯機(jī)制的[5]。在執(zhí)行程序包之前，oracle會先檢查包的狀態(tài)，若發(fā)現(xiàn)包的狀態(tài)為 “無效”（INVALID），oracle會自動先對包進(jìn)行重新編譯，再運(yùn)行程序包。若重新編譯成功，程序包仍會正確地運(yùn)行。如下實(shí)驗(yàn)2所示。

會話A：

SQL>create table test

（cstm_id varchar2（10））；

表已創(chuàng)建。

SQL>create package pkg_test as

2 procedure insdata；

3 end pkg_test；

程序包已創(chuàng)建。

SQL>create package body pkg_test as

2 procedure insdata is

3 begin

4 insert into test values （'0001'）；

5 commit；

6 end insdata；

7 end pkg_test；

程序包主體已創(chuàng)建。

會話B：

SQL>exec pkg_test.insdata；

PL/SQL過程已成功完成。

會話A：

SQL>alter table test add （cstm_nm varchar2（60））；表已更改。

會話B：

SQL>select object_name， object_type，timestamp， status

from user_objects where object_name='PKG_TEST'；

OBJECT_NAME OBJECT_TYPE STATUS

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

PKG_TEST PACKAGE BODY INVALID

SQL>exec pkg_test.insdata

PL/SQL過程已成功完成。

SQL>select object_name， object_type， status

2 from user_objects where object_name='PKG_TEST'；

OBJECT_NAME OBJECT_TYPE STATUS

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

PKG_TEST PACKAGE BODY VALID

在上述實(shí)驗(yàn)2中，我們順序執(zhí)行了以下幾個步驟：

1）會話A創(chuàng)建數(shù)據(jù)表 test和包pkg_test，包pkg_test包含一個過程insdata，往表test中插入一條記錄。顯然，包pkg_test是依賴于表test的。

2）會話B執(zhí)行pkg_test.insdata往表 test中插入一條數(shù)據(jù)，運(yùn)行正常。

3）會話A更改test的表結(jié)構(gòu)。此時，包pkg_test的包體狀態(tài)變成了“無效”。

4）會話B執(zhí)行pkg_test.insdata往表 test中插入一條數(shù)據(jù)，運(yùn)行正常，沒有因?yàn)榘w失效而拋出“ORA-04068”錯誤。此時包體的時間戳已更新，表明包自動進(jìn)行了重新編譯。

上述實(shí)驗(yàn)第3）4）步證實(shí)了oracle自動重編譯的機(jī)制的存在。但是，有了oracle自動重新編譯機(jī)制的保證，為什么實(shí)驗(yàn)1中還有報錯發(fā)生呢？這還與包的運(yùn)行機(jī)制以及包中的全局變量和全局常量有關(guān)。

3 帶全局變量/常量的包重新編譯機(jī)制

與單個SQL語句相對，包之所以運(yùn)行效率高，除了它大量減少了客戶端與服務(wù)器之間的通信等原因外，另外一個重要的原因是包執(zhí)行時不需要進(jìn)行語法檢查、語法檢查、中間代碼生成等解析過程。這一過程已經(jīng)在包創(chuàng)建時完成了，稱為包編譯過程。當(dāng)包第一次被調(diào)用（稱為實(shí)例化）時，oracle將從磁盤讀入該包的中間代碼并將其放入系統(tǒng)全局工作區(qū)（SGA）中的共享緩沖區(qū)（shared pool）[6]，但是，包的運(yùn)行狀態(tài)（即變量和游標(biāo)信息）存放在用戶全局區(qū)（UGA）中的會話存儲區(qū)。這就保證了每個會話都有自己程序包運(yùn)行狀態(tài)的副本，即使不同的會話同時運(yùn)行同一個程序包，也能做到互不干擾。

全局變量是指在包頭中聲明的變量，它作為程序包運(yùn)行狀態(tài)一部分，同樣存放在用戶全局區(qū)（UGA）的會話存儲區(qū)中，與會話具有相同的生命周期。在同一個會話中全局變量會維持它的最新值，但全局變量的值設(shè)置、修改、維護(hù)對其它的會話是不會有影響的。下面的實(shí)驗(yàn)3說明了這一情況。

會話A：

SQL>SQL>create or replace package pkg_add as

2 g_num number:=100；

3 procedure add_num；

4 end pkg_add；

程序包已創(chuàng)建。

SQL>create or replace package body pkg_add as

2 procedure add_num is

3 begin

4 g_num:=g_num+1；

5 dbms_output.put_line（ 'g_num='||g_num）；

6 end add_num；

7 end pkg_add；

程序包主體已創(chuàng)建。

會話 B：

SQL>set serverout on

SQL>exec pkg_add.add_num；g_num=101

PL/SQL過程已成功完成。

會話 C：

SQL>set serverout on

SQL>exec pkg_add.add_num；

g_num=101

PL/SQL過程已成功完成。

實(shí)驗(yàn)3中會話B修改了全局變量g_num的值對會話C沒有影響，因?yàn)槿肿兞吭诓煌臅捴斜４嬖诓煌臅挻鎯^(qū)，有各自獨(dú)立的副本。

但是，全局變量和全局常量有一個非常重要的特性：如果定義全局變量的包進(jìn)行了重建，那么這些全局變量的值將被重置為初始值。正是這個特性引起了“ORA-04068”錯誤，可以從兩個方面對此進(jìn)行說明。

一方面，由于對象依賴關(guān)系，一旦全局變量/常量所在包進(jìn)行了重建，那么所有與這個包相關(guān)的包也會失效。當(dāng)再次執(zhí)行相關(guān)的包時，oracle會進(jìn)行自動重新編譯，這就意味其它會話要重新獲取全局變量的值。

另外一方面，每個會話都是按自己的邏輯來處理各自會話存儲區(qū)中全局變量的值，而自動重編譯又會導(dǎo)致全局變量值的重新初始化，會覆蓋了本地的值。此時，oracle若再“默默”地自動重編譯，會導(dǎo)致本地全局變量的值變得不可控，因?yàn)樗辉偈且郧暗闹盗?，而是被重置成了初始值。因此，oracle必須采取拋出錯誤的辦法來顯式地通知用戶會話，否則，用戶沒辦法知道全局變量的值已發(fā)生了變化。從這個角度來說，認(rèn)為“ORA-04068”錯誤是一種警告而不是一種錯誤的觀點(diǎn)也是有道理的，它的確更多的像一種警告。

4 解決辦法

全局變量引起的錯誤影響可大可小，與所處理數(shù)據(jù)的重要性和數(shù)據(jù)量有關(guān)。如果“實(shí)驗(yàn)1”中過程pkg_app.prt是長時間的大量復(fù)雜的數(shù)據(jù)處理，會話B正好調(diào)用pkg_app.prt處理了一半的時候出錯，很可能會造成無法挽回的損失。在更壞情況下，若很多包都與全局變量或全局常量的所在包有依賴關(guān)系，同時都發(fā)生“ORA-04068”錯誤，可能產(chǎn)生災(zāi)難性的后果。

事實(shí)上，實(shí)驗(yàn)1中是全局常量，不存在值不可控的問題，oracle完全可忽略這種錯誤繼續(xù)處理下去。另外一方面，在程序設(shè)計過程中，全局常量應(yīng)該考慮避開這種出錯的風(fēng)險。 因此在什么情況下使用全局變量，怎么避免或降低錯誤的發(fā)生，成為急需解決的問題。

1）避免使用全局變量/常量，使用函數(shù)代替全局常量

對于全局變量，應(yīng)該盡量避免使用。如果確實(shí)需要用到全局變量，可以考慮通過數(shù)據(jù)表記錄的方式來解決。把所有全局變量采取鍵值對的方式記錄在數(shù)據(jù)表中。每個會話每次操作前先去數(shù)據(jù)表查詢一下全局變量的最新值，操作完成后將新的結(jié)果更新到數(shù)據(jù)庫表。全局變量是會話級別的，同一個會話的一般不會產(chǎn)生并發(fā)操作，數(shù)據(jù)庫表記錄的方式能有效的實(shí)現(xiàn)全局常量的功能。但缺點(diǎn)是，多個會話同時進(jìn)行操作時，每個會話都需建立獨(dú)立的鍵值對，否則不同會話間會相互影響。

對于全局常量，幾乎所有的系統(tǒng)都需要定義。如果系統(tǒng)中不允許定義常量，那么就會導(dǎo)致很差的代碼。但全局常量完全可以使用函數(shù)來替代，利用oracle自動重編機(jī)制來避免出現(xiàn)“ORA-04068”錯誤。我們將實(shí)驗(yàn)1中的pkg_con進(jìn)行改造，如下所示。

SQL>create or replace package pkg_con as

2 function char_yes return char；

3 end pkg_con；

程序包已創(chuàng)建。

SQL>create or replace package body pkg_con as

2 function char_yes return char is

3 begin

4 return'2'；

5 end char_yes；

6 end pkg_con；

程序包主體已創(chuàng)建。

只要函數(shù)名與原全局常量名一樣，相關(guān)的引用包完全不需要做任何更改就可以繼續(xù)使用。改造后的pkg_con無論怎樣重建，pkg_app都能被oracle自動重編機(jī)制透明處理，不會出現(xiàn)任何問題。這種方法的缺點(diǎn)是要維護(hù)更多的代碼，不像全局常量那么簡潔。因此，可能需要對代碼改造的工作量需要進(jìn)行評估。若是系統(tǒng)中多處定義了全局常量，可能導(dǎo)致大量的代碼修改，這不是一蹴而就的事情。但這是一個一勞永逸的方案，即使在短時間內(nèi)無法全部改造完畢，也應(yīng)該把它作為一個“長治久安”的首選策略。

2）集中定義全局變量和常量，盡量減少定義包的變動

首先，全局變量和常量應(yīng)該全部集中定義，盡量定義在一個或幾個專用包中。這跟C語言中的頭文件有點(diǎn)類似。全局變量或常量的集中定義有利于變量（常量）地統(tǒng)一組織、管理和維護(hù)，更重要的是能夠有效的降低“ORA-04068”錯誤發(fā)生的可能性。只有全局變量和常量的定義包發(fā)生變化才可能引發(fā)“ORA-04068”錯誤，其它引用包發(fā)生變化都會被oracle透明地自動重編譯處理。當(dāng)然，包頭中的函數(shù)或過程發(fā)生變化導(dǎo)致自動重編譯失敗時例外。集中定義的另一個好處就是用戶明確知道他正在修改的包是一個全局變量和常量定義包，必須記得而不是忘記對相關(guān)的引用包進(jìn)行處理。

其次，全局變量或常量定義包應(yīng)該避免頻繁的變動。全局變量/常量定義包的變化必然會導(dǎo)致引用包的失效，需要對所有這些引用包進(jìn)行重新編譯處理。而集中定義的結(jié)果會導(dǎo)致同一個定義包的引用包數(shù)量增多，影響加大，定義包變動導(dǎo)致需要重新編譯的包的數(shù)量也更多，工作量也很大。因此，最好能一次性定義好所有的全局變量和常量，盡量減少定義包的變動。

3）監(jiān)測ORA-0408錯誤并重新執(zhí)行包的存儲過程

這個解決方法將處理錯誤的責(zé)任放到了客戶端。它的思想是Oracle生成錯誤ORA-04068給客戶端時提供了關(guān)于包狀態(tài)已經(jīng)失效的信息，由客戶端來監(jiān)測這個錯誤以及做出反應(yīng)?？蛻舳丝梢赃x擇重新執(zhí)行這個存儲過程，如果它需要的話。這與oracle的建議是一致的。這里不再舉例說明。當(dāng)然，我們也可以考慮將包的調(diào)用處理封裝成一個獨(dú)立的過程或函數(shù)（c++/java中為類），專門對發(fā)生此類錯誤的包進(jìn)行重新執(zhí)行處理，以供其它過程或函數(shù)進(jìn)行透明調(diào)用。值得注意的是，程序員應(yīng)該對包重新執(zhí)行的可行性有充分的考慮，畢竟并不是所有的包在所有的情況下都是可重復(fù)執(zhí)行的。

5 結(jié)束語

本文由淺入深對全局變量引起包失效的錯誤原因進(jìn)行了較為深入的剖析，它不僅與oracle的對象依賴機(jī)制有關(guān)，還與自動重編機(jī)制以及程序包的運(yùn)行機(jī)制有關(guān)。 針對這種錯誤，本文提出了幾種可行的解決方法，每一個解決方法都有各自的優(yōu)點(diǎn)和不足，多種方法相結(jié)合才能達(dá)到滿意的效果。對于新開發(fā)的程序包，應(yīng)盡量避免使用全局變量，使用函數(shù)來代替全局常量；對于現(xiàn)有的程序包，應(yīng)逐步實(shí)施代碼改造，同時密切監(jiān)控錯誤的發(fā)生、定期檢測失效的程序包并自動進(jìn)行重新編譯處理。

[1]Feuerstein S，Pribyl B.Oracle PL/SQL程序設(shè)計[M].張曉明譯.5版.北京:人民郵電出版社，2011:593-594.

[2]chinak62.重編譯定義全局變量的包引發(fā)的ora-04068錯誤[EB/OL]. （2010） http://blog.itpub.net/180324/viewspace-660315/.

[3]Russell J，Portfolio T.PL/SQL User’s Guide and Reference Release 2（9.2）[M].California:Oracle Corporation，2002.

[4]蓋國強(qiáng).深入淺出Oracle-DBA入門、進(jìn)階與診斷案例[M].北京:人民郵電出版社，2006:99-101.

[5]Mcdonald C.精通Oracle PL/SQL[M].蔡偉毅，譯.北京:人民郵電出版社，2009:47-53.

[6]Alapati S R.Oracle10g數(shù)據(jù)庫管理藝術(shù)[M].鐘鳴，等譯.北京:人民郵電出版社，2007.