db2 存儲(chǔ)過(guò)程開發(fā)最佳實(shí)踐

本文以DB2開發(fā)人員的角度介紹了在DB2存儲(chǔ)過(guò)程開發(fā)中需要注意的事項(xiàng)和技巧。新手如果能夠按照本文介紹的最佳實(shí)踐來(lái)開發(fā)存儲(chǔ)過(guò)程，可以避免一些常見(jiàn)的錯(cuò)誤，從而編寫出高效的程序。本文從初始化參數(shù)、游標(biāo)、異常處理、臨時(shí)表的使用以及如何尋找并rebind非法存儲(chǔ)過(guò)程等常見(jiàn)問(wèn)題進(jìn)行了著重討論，并且給出了示例代碼。DB2提供的強(qiáng)大功能可以讓開發(fā)人員創(chuàng)建出非常高效穩(wěn)定的存儲(chǔ)過(guò)程。但對(duì)于初學(xué)者來(lái)說(shuō)，開發(fā)出這樣的程序并不容易。本文主要討論開發(fā)高效穩(wěn)定的DB2存儲(chǔ)過(guò)程的一些常用技巧和方法。讀者定位為具有一定開發(fā)經(jīng)驗(yàn)的DB2開發(fā)經(jīng)驗(yàn)的開發(fā)人員。讀者可以從本文學(xué)習(xí)到如何編寫穩(wěn)定、高效的存儲(chǔ)過(guò)程。并可以直接使用文章中提供的DB2代碼，從而節(jié)省他們的開發(fā)和調(diào)試時(shí)間，提高效率。本文以DB2開發(fā)人員的角度介紹了在DB2存儲(chǔ)過(guò)程開發(fā)中需要注意的事項(xiàng)和技巧。新手如果能夠按照本文介紹的最佳實(shí)踐來(lái)開發(fā)存儲(chǔ)過(guò)程，可以避免一些常見(jiàn)的錯(cuò)誤，從而編寫出高效的程序。本文從初始化參數(shù)、游標(biāo)、異常處理、臨時(shí)表的使用以及如何尋找并rebind非法存儲(chǔ)過(guò)程等常見(jiàn)問(wèn)題進(jìn)行了著重討論，并且給出了示例代碼。在存儲(chǔ)過(guò)程中，開發(fā)人員能夠聲明和設(shè)置SQL變量、實(shí)現(xiàn)流程控制、處理異常、能夠?qū)?shù)據(jù)進(jìn)行插入、更新或者刪除。同時(shí)，客戶應(yīng)用(這里指調(diào)用存儲(chǔ)過(guò)程的應(yīng)用程序，它可以是JDBC的調(diào)用，也可以是ODBC和CLI等)和存儲(chǔ)過(guò)程之間可以傳遞參數(shù)，并且從存儲(chǔ)過(guò)程中返回結(jié)果集。其中，使用SQL編寫的DB2存儲(chǔ)過(guò)程是在開發(fā)中常見(jiàn)的一種存儲(chǔ)過(guò)程。本文主要討論此類存儲(chǔ)過(guò)程。最佳實(shí)踐1：在創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句中提供必要的參數(shù)創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句(CREATEPROCEDURE)可以包含很多參數(shù)，雖然從語(yǔ)法角度講它們不是必須的，但是在創(chuàng)建存儲(chǔ)過(guò)程時(shí)提供它們可以提高執(zhí)行效率。下面是一些常用的參數(shù)容許SQL(allowed—SQL)容許SQL(allowed—SQL)子句的值指定了存儲(chǔ)過(guò)程是否會(huì)使用SQL語(yǔ)句，如果使用，其類型如何。它的可能值如下所示：NOSQL：表示存儲(chǔ)過(guò)程不能夠執(zhí)行任何SQL語(yǔ)句。CONTAINSSQL：表示存儲(chǔ)過(guò)程可以執(zhí)行SQL語(yǔ)句，但不會(huì)讀取SQL數(shù)據(jù)，也不會(huì)修改SQL數(shù)據(jù)。READSSQLDATA：表示在存儲(chǔ)過(guò)程中包含不會(huì)修改SQL數(shù)據(jù)的SQL語(yǔ)句。也就是說(shuō)該儲(chǔ)存過(guò)程只從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)。MODIFIESSQLDATA：表示存儲(chǔ)過(guò)程可以執(zhí)行任何SQL語(yǔ)句。即可以對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行增加、刪除和修改。如果沒(méi)有明確聲明allowed-SQL，其默認(rèn)值是MODIFIESSQLDATA。不同類型的存儲(chǔ)過(guò)程執(zhí)行的效率是不同的，其中NOSQL效率最好，MODIFIESSQLDATA最差。如果存儲(chǔ)過(guò)程只是讀取數(shù)據(jù)，但是因?yàn)闆](méi)有聲明allowed-SQL使其被當(dāng)作對(duì)數(shù)據(jù)進(jìn)行修改的存儲(chǔ)過(guò)程來(lái)執(zhí)行，這顯然會(huì)降低程序的執(zhí)行效率。因此創(chuàng)建存儲(chǔ)過(guò)程時(shí)，應(yīng)當(dāng)明確聲明其allowed-SQLo返回結(jié)果集個(gè)數(shù)(DYNAMICRESULTSETSn)存儲(chǔ)過(guò)程能夠返回0個(gè)或者多個(gè)結(jié)果集。為了從存儲(chǔ)過(guò)程中返回結(jié)果集，需要執(zhí)行如下步驟：?在CREATEPROCEDURE語(yǔ)句的DYNAMICRESULTSETS子句中聲明存儲(chǔ)過(guò)程將要返回的結(jié)果集的數(shù)量(number-of-result-sets)。如果這里聲明的返回結(jié)果集的數(shù)量小于存儲(chǔ)過(guò)程中實(shí)際返回的結(jié)果集數(shù)量，在執(zhí)行該存儲(chǔ)過(guò)程的時(shí)候，DB2會(huì)返回一個(gè)警告。?使用WITHRETURN子句，在存儲(chǔ)過(guò)程體中聲明游標(biāo)。?為結(jié)果集打開游標(biāo)。當(dāng)存儲(chǔ)過(guò)程返回的時(shí)候，保持游標(biāo)打開。在創(chuàng)建存儲(chǔ)過(guò)程時(shí)指定返回結(jié)果集的個(gè)數(shù)可以幫助程序員驗(yàn)證存儲(chǔ)過(guò)程是否返回了所期待數(shù)量的結(jié)果集，提高了程序的完整性。守回頁(yè)首最佳實(shí)踐2：對(duì)輸入?yún)?shù)進(jìn)行必要的的檢查和預(yù)處理無(wú)論使用哪種編程語(yǔ)言，對(duì)輸入?yún)?shù)的判斷都是必須的。正確的參數(shù)驗(yàn)證是保證程序良好運(yùn)行的前提。同樣的，在DB2中對(duì)輸入?yún)?shù)的驗(yàn)證和處理也是很重要的。正確的驗(yàn)證和預(yù)處理操作包括：?如果輸入?yún)?shù)錯(cuò)誤，存儲(chǔ)過(guò)程應(yīng)返回一個(gè)明確的值告訴客戶應(yīng)用，然后客戶應(yīng)用可以根據(jù)返回的值進(jìn)行處理，或者向存儲(chǔ)過(guò)程提交新的參數(shù)，或者去調(diào)用其他的程序。?根據(jù)業(yè)務(wù)邏輯，對(duì)輸入?yún)?shù)作一定的預(yù)處理，如大小寫的轉(zhuǎn)換，NULL與空字符串或0的轉(zhuǎn)換等。在DB2儲(chǔ)存過(guò)程開發(fā)中，如需要遇到對(duì)空(NULL)進(jìn)行初始化，我們可以使用COALESCE函數(shù)。COALESCE函數(shù)返回第一個(gè)非空的參數(shù)，語(yǔ)法如下：清單1：COALESCE函數(shù)(1) V |>>-COALESCE (—expression ,—expression-*—) ><COALESCE函數(shù)會(huì)依次檢查輸入的參數(shù)，返回第一個(gè)不是NULL的參數(shù)，只有當(dāng)傳入COALESCE函數(shù)的所有的參數(shù)都是NULL的時(shí)候，函數(shù)才會(huì)返回NULL。例如，COALESCE(piName,'')，如果變量piName為NULL，那么函數(shù)會(huì)返回''，否則就會(huì)返回piName本身的值。下面的例子展示了如何對(duì)參數(shù)進(jìn)行檢查何初始化。Person表用來(lái)存儲(chǔ)個(gè)人的基本信息，其定義如下：表1：Person列名'參數(shù)定義說(shuō)明NumpiNumINTEGERNOTNULJ-人員編號(hào)，非空，不允許為”NamepiNameVARCHARj(20\NOTNULL人員名字，非空AgepiAgeINTEGERNOTNULL年齡:非空RjankpiRankINTEGER.等綴.，缺省為。piComnuentVARCHARjflOO)備注卜面是用于向表Person插入數(shù)據(jù)的存儲(chǔ)過(guò)程的參數(shù)預(yù)處理部分代碼:SETpoGenStatus=0;SETpiName=RTRIM(COALESCE(piName,''));SETpiRank=COALESCE(piRank,0);--makesureallrequiredinputparametersarenotnullIF(piNumISNULLORpiName=''ORpiAgeISNULL)THENSETpoGenStatus=34100;RETURNpoGenStatus;ENDIF;表Person中num、name和age都是非空字段。對(duì)于name字段，多個(gè)空格我們也認(rèn)為是空值，所以在進(jìn)行判斷前我們調(diào)用RTRIM和COALESCE對(duì)其進(jìn)行處理，然后使用piName=’’，對(duì)其進(jìn)行非空判斷；對(duì)于Rank字段，我們希望如果用戶輸入的NULL，我們把它設(shè)置成〃0〃，對(duì)其我們也使用COALESCE進(jìn)行初始化；對(duì)于"Age"和〃Num"我們直接使用ISNULL進(jìn)行非空判斷就可以了。如果輸入?yún)?shù)沒(méi)有通過(guò)非空判斷，我們就對(duì)輸出參數(shù)poGenStatus設(shè)置一個(gè)確定的值(例子中為34100)告知調(diào)用者：輸入?yún)?shù)錯(cuò)誤。下面是對(duì)參數(shù)初始化規(guī)則的一個(gè)總結(jié)，供大家參考：輸入?yún)?shù)為字符類型，且允許為空的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成''；輸入類型為整型，且允許為空的，可以使用COALESCE(inputParameter,0)，把空轉(zhuǎn)換成0；輸入?yún)?shù)為字符類型，且是非空非空格的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成'’，然后判斷函數(shù)返回值是否為；輸入類型為整型，且是非空的，不需要使用COALESCE函數(shù)，直接使用ISNULL進(jìn)行非空判斷。守回頁(yè)首最佳實(shí)踐3：正確設(shè)定游標(biāo)的返回類型前面我們已經(jīng)討論了如何聲明存儲(chǔ)過(guò)程的返回結(jié)果集。這里我們討論一下結(jié)果集返回類型的問(wèn)題。結(jié)果集的返回類型有兩種：調(diào)用者(CALLER)和客戶應(yīng)用(CLIENT)。首先我們看一下聲明這兩種游標(biāo)的例子：CREATEPROCEDUREgetPeople(INpiAgeINTEGER)DYNAMICRESULTSETS2READSSQLDATALANGUAGESQLBEGINDECLARErs1CURSORWITHRETURNTOCLIENTFORSELECTname,ageFROMpersonWHEREagepiAge;OPENrs1;OPENrs2;END代碼中rs1游標(biāo)的DECLAER語(yǔ)句中包含WITHRETURNTOCLIENT子句，表示結(jié)果集返回給客戶應(yīng)用（CLIENT）。rs2游標(biāo)的DECLARE語(yǔ)句中包含WITHRETURNTOCALLER子句，表示結(jié)果集返回給調(diào)用者（CALLER）。游標(biāo)返回給調(diào)用者（CALLER）表示由存儲(chǔ)過(guò)程的調(diào)用者接收結(jié)果集，而不考慮調(diào)用者是否是另一個(gè)存儲(chǔ)過(guò)程，還是客戶應(yīng)用。圖（1）中存儲(chǔ)過(guò)程PROZ如果聲明為WITHRETURNTOCALLER，那么結(jié)果集會(huì)返回給存儲(chǔ)過(guò)程PROY，ClientApplication是不會(huì)得到PROZ返回的結(jié)果集的。圖1：存儲(chǔ)過(guò)程遞歸調(diào)用游標(biāo)返回給客戶應(yīng)用（CLIENT）表示由發(fā)出最初CALL語(yǔ)句的客戶應(yīng)用接收結(jié)果集，即使結(jié)果集由嵌套層次中的15層深的嵌套存儲(chǔ)過(guò)程發(fā)出也是如此。圖1中存儲(chǔ)過(guò)程PROZ如果聲明為WITHRETURNTOCLIENT，那么結(jié)果集會(huì)返回給ClientApplication。返回給客戶應(yīng)用（CLIENT）的游標(biāo)聲明是我們經(jīng)常使用的，也是默認(rèn)的結(jié)果集類型。在聲明返回類型時(shí)，我們要認(rèn)真考慮一下，我們需要把結(jié)果集返回給誰(shuí)，以免丟失返回集，導(dǎo)致程序錯(cuò)誤。最佳實(shí)踐4：異常(condition)處理*回頁(yè)首在存儲(chǔ)過(guò)程執(zhí)行的過(guò)程中，經(jīng)常因?yàn)閿?shù)據(jù)或者其他問(wèn)題產(chǎn)生異常(condition)。根據(jù)業(yè)務(wù)邏輯，存儲(chǔ)過(guò)程應(yīng)該對(duì)異常進(jìn)行相應(yīng)處理或直接返回給調(diào)用者。此處暫且將condition譯為異常以方便讀者理解。實(shí)際上有些異常(condition)并非是由于錯(cuò)誤引起的，下面將詳細(xì)講述。當(dāng)存儲(chǔ)過(guò)程中的語(yǔ)句返回的SQLSTATE值超過(guò)00000的時(shí)候，就表明在存儲(chǔ)過(guò)程中產(chǎn)生了一個(gè)異常(condition)，它表示出現(xiàn)了錯(cuò)誤、數(shù)據(jù)沒(méi)有找到或者出現(xiàn)了警告。為了響應(yīng)和處理存儲(chǔ)過(guò)程中出現(xiàn)的異常，我們必須在存儲(chǔ)過(guò)程體中聲明異常處理器(conditionhandler)，它可以決定存儲(chǔ)過(guò)程怎樣響應(yīng)一個(gè)或者多個(gè)已定義的異?；蛘哳A(yù)定義異常組。聲明條件處理器的語(yǔ)法如下，它會(huì)位于變量聲明和游標(biāo)聲明之后：清單4：聲明異常處理器DECLAREhandler-typeHANDLERFORconditionhandler-action異常處理器類型(handler-type)有以下幾種：CONTINUE在處理器操作完成之后，會(huì)繼續(xù)執(zhí)行產(chǎn)生這個(gè)異常語(yǔ)句之后的下一條語(yǔ)句。EXIT在處理器操作完成之后，存儲(chǔ)過(guò)程會(huì)終止，并將控制返回給調(diào)用者。UNDO在處理器操作執(zhí)行之前，DB2會(huì)回滾存儲(chǔ)過(guò)程中執(zhí)行的SQL操作。在處理器操作完成之后，存儲(chǔ)過(guò)程會(huì)終止，并將控制返回給調(diào)用者。異常處理器可以處理基于特定SQLSTATE值的定制異常，或者處理預(yù)定義異常的類。預(yù)定義的3種異常如下所示：NOTFOUND標(biāo)識(shí)導(dǎo)致SQLCODE值為+100或者SQLSATE值為02000的異常。這個(gè)異常通常在SELECT沒(méi)有返回行的時(shí)候出現(xiàn)。SQLEXCEPTIOIN標(biāo)識(shí)導(dǎo)致SQLCODE值為負(fù)的異常。SQLWARNING標(biāo)識(shí)導(dǎo)致警告異?；蛘邔?dǎo)致+100以外的SQLCODE正值的異常。如果產(chǎn)生了NOTFOUND或者SQLWARNING異常，并且沒(méi)有為這個(gè)異常定義異常處理器，那么就會(huì)忽略這個(gè)異常，并且將控制流轉(zhuǎn)向下一個(gè)語(yǔ)句。如果產(chǎn)生了SQLEXCEPTION異常，并且沒(méi)有為這個(gè)異常定義異常處理器，那么存儲(chǔ)過(guò)程就會(huì)失敗，并且會(huì)將控制流返回調(diào)用者。以下示例聲明了兩個(gè)異常處理器。EXIT處理器會(huì)在出現(xiàn)SQLEXCEPTION或者SQLWARNING異常的時(shí)候被調(diào)用°EXIT處理器會(huì)在終止SQL程序之前，將名為stmt的變量設(shè)為〃ABORTED”,并且將控制流返回給調(diào)用者。UNDO處理器會(huì)將控制流返回給調(diào)用者之前，回滾存儲(chǔ)過(guò)程體中已經(jīng)完成的SQL操作。清單5：異常處理器示例DECLAREEXITHANDLERFORSQLEXCEPTION,SQLWARNINGSETstmt=,ABORTED';DECLAREUNDOHANDLERFORNOTFOUND;如果預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個(gè)定制異常聲明處理器。語(yǔ)法如下：清單6：定制異常處理器DECLAREunique-nameCONDITIONFORSQLSATE'sqlstate'處理器可以由單獨(dú)的存儲(chǔ)過(guò)程語(yǔ)句定義，也可以使用由BEGIN-END塊界定的復(fù)合語(yǔ)句定義。注意在執(zhí)行符合語(yǔ)句的時(shí)候，SQLSATE和SQLCODE的值會(huì)被改變，如果需要保留異常前的SQLSATE和SQLCODE，就需要在執(zhí)行復(fù)合語(yǔ)句的第一個(gè)語(yǔ)句把SQLSATE和SQLCODE賦予本地變量或參數(shù)。通常，我們會(huì)為存儲(chǔ)過(guò)程定義一個(gè)執(zhí)行狀態(tài)的輸出參數(shù)（例如：poGenStatus）。根據(jù)這個(gè)輸出狀態(tài)，可以表明存儲(chǔ)過(guò)程是否正確執(zhí)行完畢。我們需要定義一些異常處理器為這個(gè)輸出參數(shù)賦值。下面是一個(gè)例子：清單7：定義為輸出參數(shù)賦值的異常處理器--GenericHandlerDECLARECONTINUEHANDLERFORSQLEXCEPTION,SQLWARNING,NOTFOUNDBEGINNOTATOMIC--CaptureSQLCODE&SQLSTATESELECTSQLCODE,SQLSTATEINTO hSqlcode,hSqlstateFROM SYSIBM.SYSDUMMY1;--UsethepoGenStatusvariabletotelltheprocedure--whattypeoferroroccurredCASEhSqlstateWHEN'02000'THENSETpoGenStatus=5000;WHEN'42724'THENSETpoGenStatus=3;ELSEIF(hSqlCode<0)THENSETpoGenStatus二hSqlCode;ENDIF;ENDCASE;END;上面的異常處理器會(huì)在出現(xiàn)SQLEXCEPTION,SQLWARNING,NOTFOUND異常的時(shí)候觸發(fā)。異常處理器會(huì)取出當(dāng)前的SQLCODE,SQLSTATE，然后根據(jù)它們的值來(lái)設(shè)置輸出參數(shù)(poGenStatus)的值。我們還可以定制一些異常處理器。例如，我們可以定義一些對(duì)參數(shù)進(jìn)行初始化的異常處理器。這里，異常處理器可以看作是一個(gè)供存儲(chǔ)過(guò)程自己調(diào)用的內(nèi)部函數(shù)。下面是這種情況的一個(gè)例子：清單8：供存儲(chǔ)過(guò)程自己調(diào)用的內(nèi)部函數(shù)--CONDITIONdeclaration--(80100~80199)SQLCODE&SQLSTATEDECLAREsqlResetCONDITIONforsqlstate'80100';--EXCEPTIONHANDLERdeclaration--HandyHandlerDECLARECONTINUEHANDLERFORsqlResetBEGINNOTATOMICSEThSqlcode=0;SEThSqlstate='00000';SETpoGenStatus=0;END;--ProcedureBody—SIGNALsqlreset;--inserttherecord上面定制的異常處理器負(fù)責(zé)對(duì)參數(shù)hSqlcode,hSqlstate和poGenStatus初始化。當(dāng)我們?cè)诔绦蛑行枰獙?duì)它們初始化時(shí)，我們只需要調(diào)用SIGNALsqlreset就可以了。守回頁(yè)首最佳實(shí)踐5：合理使用臨時(shí)表我們?cè)趦?chǔ)存過(guò)程開發(fā)中經(jīng)常使用臨時(shí)表。合理的使用臨時(shí)表可以簡(jiǎn)化程序的編寫，提供執(zhí)行效率，然而濫用臨時(shí)表同樣也會(huì)使得程序運(yùn)行效率降低。臨時(shí)表一般在如下情況下使用：臨時(shí)表用于存儲(chǔ)程序運(yùn)行中的臨時(shí)數(shù)據(jù)。例如，如果在一個(gè)程序中第一條查詢語(yǔ)句執(zhí)行的結(jié)果會(huì)被后續(xù)的查詢語(yǔ)句用到，那么我們可以把第一次查詢的結(jié)果存儲(chǔ)在一個(gè)臨時(shí)表中供后續(xù)查詢語(yǔ)句使用，而不是在后續(xù)查詢語(yǔ)句中重新查詢一次。如果第一條查詢語(yǔ)句非常復(fù)雜和耗時(shí)，那么上面的策略是非常有效的。臨時(shí)表可以用于存儲(chǔ)在一個(gè)程序中需要返回多次的結(jié)果集。例如，程序中有一個(gè)很耗資源的多表查詢，同時(shí)，該查詢?cè)诔绦蛑行枰獔?zhí)行多次，那么就可以把第一次查詢的結(jié)果集存儲(chǔ)在臨時(shí)保中，后續(xù)的查詢只需要查臨時(shí)表就可以了。臨時(shí)表也可以用于讓SQL訪問(wèn)非關(guān)系型數(shù)據(jù)庫(kù)。例如，可以編寫程序把非關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)插入到一個(gè)全局臨時(shí)表中，那么我們就可以對(duì)其數(shù)據(jù)進(jìn)行查詢。我們可使用DECLAREGLOBALTEMPORARYTABLE語(yǔ)句來(lái)定義臨時(shí)表。DB2的臨時(shí)表是基于會(huì)話的，且在會(huì)話之間是隔離的。當(dāng)會(huì)話結(jié)束時(shí)，臨時(shí)表的數(shù)據(jù)被刪除，臨時(shí)表被隱式卸下。對(duì)臨時(shí)表的定義不會(huì)在SYSCAT.TABLES中出現(xiàn)下面是定義臨時(shí)表的一個(gè)示例：清單9：定義臨時(shí)表DECLAREGLOBALTEMPORARYTABLEgbl_tempLIKEpersonONCOMMITDELETEROWSNOTLOGGEDINusr_tbsp此語(yǔ)句創(chuàng)建一個(gè)名為gbl_temp的用戶臨時(shí)表。定義此用戶臨時(shí)表所使用的列的名稱和說(shuō)明與person的列的名稱和說(shuō)明完全相同。清單10：創(chuàng)建有兩個(gè)字段的臨時(shí)表DECLAREGLOBALTEMPORARYTABLESESSION.TEMP2(IDINTEGERdefault3,NAMECHAR(30))--WITHREPLACENOTLOGGED;--INUSER_TEMP_01;此語(yǔ)句創(chuàng)建了一個(gè)有兩個(gè)字段的臨時(shí)表。理論上臨時(shí)表是不需要顯示DROP的，因?yàn)樗腔跁?huì)話的，當(dāng)臨時(shí)表基于的連接關(guān)閉的時(shí)候，臨時(shí)表也就不存在了。但是在實(shí)際開發(fā)中會(huì)有一些情況需要我們對(duì)臨時(shí)表加以注意。一種情況就是被調(diào)用的存儲(chǔ)過(guò)程的返回值是一個(gè)基于臨時(shí)表的結(jié)果集。當(dāng)存儲(chǔ)過(guò)程執(zhí)行完畢的時(shí)候，臨時(shí)表并不會(huì)消失，因?yàn)榉祷氐慕Y(jié)果集相當(dāng)于一個(gè)指針，指向臨時(shí)表所在的內(nèi)存地址，此時(shí)臨時(shí)表是不會(huì)被DROP掉的。這種情況下，既不能在存儲(chǔ)過(guò)程中刪除這個(gè)臨時(shí)表，也不應(yīng)該由客戶應(yīng)用顯示的刪除臨時(shí)表，這就容易出現(xiàn)一些問(wèn)題。下面我們通過(guò)一個(gè)例子來(lái)說(shuō)明這個(gè)問(wèn)題。下面示例代碼是返回臨時(shí)表的存儲(chǔ)過(guò)程(get_temp_table)：清單11：返回臨時(shí)表的存儲(chǔ)過(guò)程--TEMPORARYTABLE&CURSORdeclarationDECLAREGLOBALTEMPORARYTABLESESSION.TEMP(ID INTEGER,NAMECHAR(30))--WITHREPLACENOTLOGGED;P2:BEGINDECLARER_CRSRCURSORWITHRETURNTOCLIENTFORSELECT*FROMSESSION.TEMPFORREADONLY;INSERTINTOSESSION.TEMPVALUES(1,piName);OPENR_CRSR;ENDP2;存儲(chǔ)過(guò)程中聲明了有兩個(gè)字段的臨時(shí)表TEMP,聲明了一個(gè)游標(biāo)R_CRSR返回臨時(shí)表中所有記錄，最后在臨時(shí)表中插入兩條記錄。程序第一次執(zhí)行的結(jié)果如下：圖2：程序第一次執(zhí)行的結(jié)果可以從圖中看出，運(yùn)行結(jié)果是我們期望的。那么如果我們?cè)龠\(yùn)行一次，會(huì)有什么結(jié)果呢？下圖是其運(yùn)行結(jié)果：

圖3：程序再次執(zhí)行的結(jié)果第二次執(zhí)行的時(shí)候程序卻出錯(cuò)了，這是因?yàn)樵谕粋€(gè)連接中，臨時(shí)表并沒(méi)有被DROP掉，所以在第二次調(diào)用存儲(chǔ)過(guò)程的時(shí)候就會(huì)出現(xiàn)臨時(shí)表已經(jīng)存在的錯(cuò)誤。另外一種情況，就是很多時(shí)候例如在websphere中通過(guò)JDBC連接數(shù)據(jù)庫(kù)時(shí)使用了連接池的技術(shù)，這帶來(lái)了一些效率的提升，同時(shí)在某些情況下也容易讓人誤解?？蛻魬?yīng)用程序中關(guān)閉了數(shù)據(jù)庫(kù)連接，但是并不一定真正關(guān)閉了數(shù)據(jù)庫(kù)連接，如果客戶應(yīng)用程序使用了臨時(shí)表而數(shù)據(jù)庫(kù)連接并沒(méi)有關(guān)閉，那么臨時(shí)表就不會(huì)被DROP。當(dāng)連接池把這個(gè)連接分給另一個(gè)客戶程序的時(shí)候，新的客戶程序仍然可以使用舊的臨時(shí)表，這不是我們希望的。如果想避免上述問(wèn)題，可以在創(chuàng)建臨時(shí)表時(shí)，加上WITHREPLACE；或者根據(jù)業(yè)務(wù)邏輯在合適的地方顯示的DROP臨時(shí)表。卜面是使用WITHREPLACE創(chuàng)建臨時(shí)表的執(zhí)行情況。

圖4：使用WITHREPLACE創(chuàng)建臨時(shí)表可以看出在一個(gè)連接里面，多次調(diào)用存儲(chǔ)過(guò)程get_temp_table，也不會(huì)出現(xiàn)問(wèn)題。臨時(shí)表在某些情況下也是需要避免使用的。大家知道臨時(shí)表是存放在內(nèi)存中的，如果一個(gè)臨時(shí)表有上萬(wàn)或者十幾萬(wàn)條記錄，同時(shí)程序的并發(fā)數(shù)很大，那么在內(nèi)存中建立的臨時(shí)表耗費(fèi)的資源就很龐大，此時(shí)數(shù)據(jù)庫(kù)的性能會(huì)急劇下降，甚至?xí)?dǎo)致數(shù)據(jù)庫(kù)崩潰。因此，大家在使用臨時(shí)表的時(shí)候，需要考慮它對(duì)資源的耗費(fèi)，避免盲目使用臨時(shí)表。十回頁(yè)首最佳實(shí)踐6：尋找并rebind非法的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程會(huì)因?yàn)槠渖婕昂鸵玫膶?duì)象發(fā)生了改變而導(dǎo)致其非法(invalid)，例如：修改了表結(jié)構(gòu)，導(dǎo)致引用該表的存儲(chǔ)過(guò)程非法，或者重新編譯一個(gè)存儲(chǔ)過(guò)程，會(huì)使調(diào)用這個(gè)存儲(chǔ)過(guò)程的父存儲(chǔ)過(guò)程非法。此時(shí)我們需要對(duì)非法的存儲(chǔ)過(guò)程重新編譯(rebind)。但是，對(duì)非法的存儲(chǔ)過(guò)程進(jìn)行rebind的時(shí)候，需要確定其引用的對(duì)象是合法的，否則非法的存儲(chǔ)過(guò)程也不能rebind成功。這里我們介紹一下發(fā)現(xiàn)和rebind非法存儲(chǔ)過(guò)程的方法。我們是通過(guò)判斷SYSCAT.routines中VALID字段的值來(lái)查找非法存儲(chǔ)過(guò)程的。下面是查找非法存儲(chǔ)過(guò)程的一