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

彭秋生

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

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

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

1 錯(cuò)誤的產(chǎn)生

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

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

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)建的兩個(gè)程序包狀態(tài)都是正常的，沒(méi)有任何問(wèn)題。接下來(lái)在SQL*Plus中登錄兩個(gè)會(huì)話（session）：會(huì)話A和會(huì)話B。會(huì)話B先連續(xù)一次或多次執(zhí)行包pkg_app的過(guò)程prt，都不會(huì)發(fā)生錯(cuò)誤。若會(huì)話A重建pkg_con包，會(huì)話B再執(zhí)行過(guò)程pkg_app.prt，則會(huì)發(fā)生錯(cuò)誤，如下實(shí)驗(yàn)1所示。

會(huì)話A

SQL>create or replace package pkg_con as

2 char_yes constant char:= ‘2’；

3 end pkg_con；

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

會(huì)話 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:無(wú)法在調(diào)用之前找到程序單元

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

ORA-06512:在line 1

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

2 對(duì)象依賴與自動(dòng)重新編譯機(jī)制

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

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

會(huì)話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)建。

會(huì)話B：

SQL>exec pkg_test.insdata；

PL/SQL過(guò)程已成功完成。

會(huì)話A：

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

會(huì)話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過(guò)程已成功完成。

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í)行了以下幾個(gè)步驟：

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

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

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

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

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

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

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

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

會(huì)話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)建。

會(huì)話 B：

SQL>set serverout on

SQL>exec pkg_add.add_num；g_num=101

PL/SQL過(guò)程已成功完成。

會(huì)話 C：

SQL>set serverout on

SQL>exec pkg_add.add_num；

g_num=101

PL/SQL過(guò)程已成功完成。

實(shí)驗(yàn)3中會(huì)話B修改了全局變量g_num的值對(duì)會(huì)話C沒(méi)有影響，因?yàn)槿肿兞吭诓煌臅?huì)話中保存在不同的會(huì)話存儲(chǔ)區(qū)，有各自獨(dú)立的副本。

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

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

另外一方面，每個(gè)會(huì)話都是按自己的邏輯來(lái)處理各自會(huì)話存儲(chǔ)區(qū)中全局變量的值，而自動(dòng)重編譯又會(huì)導(dǎo)致全局變量值的重新初始化，會(huì)覆蓋了本地的值。此時(shí)，oracle若再“默默”地自動(dòng)重編譯，會(huì)導(dǎo)致本地全局變量的值變得不可控，因?yàn)樗辉偈且郧暗闹盗耍潜恢刂贸闪顺跏贾怠Ｒ虼耍琽racle必須采取拋出錯(cuò)誤的辦法來(lái)顯式地通知用戶會(huì)話，否則，用戶沒(méi)辦法知道全局變量的值已發(fā)生了變化。從這個(gè)角度來(lái)說(shuō)，認(rèn)為“ORA-04068”錯(cuò)誤是一種警告而不是一種錯(cuò)誤的觀點(diǎn)也是有道理的，它的確更多的像一種警告。

4 解決辦法

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

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

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

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

對(duì)于全局常量，幾乎所有的系統(tǒng)都需要定義。如果系統(tǒng)中不允許定義常量，那么就會(huì)導(dǎo)致很差的代碼。但全局常量完全可以使用函數(shù)來(lái)替代，利用oracle自動(dòng)重編機(jī)制來(lái)避免出現(xiàn)“ORA-04068”錯(cuò)誤。我們將實(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無(wú)論怎樣重建，pkg_app都能被oracle自動(dòng)重編機(jī)制透明處理，不會(huì)出現(xiàn)任何問(wèn)題。這種方法的缺點(diǎn)是要維護(hù)更多的代碼，不像全局常量那么簡(jiǎn)潔。因此，可能需要對(duì)代碼改造的工作量需要進(jìn)行評(píng)估。若是系統(tǒng)中多處定義了全局常量，可能導(dǎo)致大量的代碼修改，這不是一蹴而就的事情。但這是一個(gè)一勞永逸的方案，即使在短時(shí)間內(nèi)無(wú)法全部改造完畢，也應(yīng)該把它作為一個(gè)“長(zhǎng)治久安”的首選策略。

2）集中定義全局變量和常量，盡量減少定義包的變動(dòng)

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

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

3）監(jiān)測(cè)ORA-0408錯(cuò)誤并重新執(zhí)行包的存儲(chǔ)過(guò)程

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

5 結(jié)束語(yǔ)

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

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

[2]chinak62.重編譯定義全局變量的包引發(fā)的ora-04068錯(cuò)誤[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]蓋國(guó)強(qiáng).深入淺出Oracle-DBA入門(mén)、進(jìn)階與診斷案例[M].北京:人民郵電出版社，2006:99-101.

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

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