DB2 提供的強(qiáng)大功能可以讓開發(fā)人員創(chuàng)建出非常高效穩(wěn)定的存儲過程。但對于初學(xué)者來說，開發(fā)出這樣的程序并不輕易。本文主要討論開發(fā)高效穩(wěn)定的 DB2 存儲過程的一些常用技巧和方法。

讀者定位為具有一定開發(fā)經(jīng)驗的 DB2 開發(fā)經(jīng)驗的開發(fā)人員。

讀者可以從本文學(xué)習(xí)到如何編寫穩(wěn)定、高效的存儲過程。并可以直接使用文章中提供的 DB2 代碼，從而節(jié)省他們的開發(fā)和調(diào)試時間，提高效率。

本文以 DB2 開發(fā)人員的角度介紹了在 DB2 存儲過程開發(fā)中需要注重的事項和技巧。新手假如能夠按照本文介紹的最佳實踐來開發(fā)存儲過程，可以避免一些常見的錯誤，從而編寫出高效的程序。本文從初始化參數(shù)、游標(biāo)、異常處理、臨時表的使用以及如何尋找并 rebind 非法存儲過程等常見問題進(jìn)行了著重討論,并且給出了示例代碼。

在存儲過程中，開發(fā)人員能夠聲明和設(shè)置 SQL 變量、實現(xiàn)流程控制、處理異常、能夠?qū)?shù)據(jù)進(jìn)行插入、更新或者刪除。同時，客戶應(yīng)用（這里指調(diào)用存儲過程的應(yīng)用程序，它可以是 JDBC 的調(diào)用，也可以是 ODBC 和 CLI 等）和存儲過程之間可以傳遞參數(shù)，并且從存儲過程中返回結(jié)果集。其中，使用 SQL 編寫的 DB2 存儲過程是在開發(fā)中常見的一種存儲過程。本文主要討論此類存儲過程。

最佳實踐 1：在創(chuàng)建存儲過程語句中提供必要的參數(shù)

創(chuàng)建存儲過程語句（CREATE PROCEDURE）可以包含很多參數(shù)，雖然從語法角度講它們不是必須的，但是在創(chuàng)建存儲過程時提供它們可以提高執(zhí)行效率。下面是一些常用的參數(shù)

容許 SQL （allowed－SQL）

容許 SQL （allowed－SQL）子句的值指定了存儲過程是否會使用 SQL 語句，假如使用，其類型如何。它的可能值如下所示：

NO SQL： 表示存儲過程不能夠執(zhí)行任何 SQL 語句。

CONTAINS SQL： 表示存儲過程可以執(zhí)行 SQL 語句，但不會讀取 SQL 數(shù)據(jù)，也不會修改 SQL 數(shù)據(jù)。

READS SQL DATA： 表示在存儲過程中包含不會修改 SQL 數(shù)據(jù)的 SQL 語句。也就是說該儲存過程只從數(shù)據(jù)庫中讀取數(shù)據(jù)。

MODIFIES SQL DATA： 表示存儲過程可以執(zhí)行任何 SQL 語句。即可以對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行增加、刪除和修改。

假如沒有明確聲明 allowed-SQL，其默認(rèn)值是 MODIFIES SQL DATA。不同類型的存儲過程執(zhí)行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。假如存儲過程只是讀取數(shù)據(jù)，但是因為沒有聲明 allowed-SQL 使其被當(dāng)作對數(shù)據(jù)進(jìn)行修改的存儲過程來執(zhí)行，這顯然會降低程序的執(zhí)行效率。因此創(chuàng)建存儲過程時，應(yīng)當(dāng)明確聲明其 allowed-SQL。

返回結(jié)果集個數(shù)（DYNAMIC RESULT SETS n）

存儲過程能夠返回 0 個或者多個結(jié)果集。為了從存儲過程中返回結(jié)果集，需要執(zhí)行如下步驟：

在 CREATE PROCEDURE 語句的 DYNAMIC RESULT SETS 子句中聲明存儲過程將要返回的結(jié)果集的數(shù)量（number-of-result-sets）。假如這里聲明的返回結(jié)果集的數(shù)量小于存儲過程中實際返回的結(jié)果集數(shù)量，在執(zhí)行該存儲過程的時候，DB2 會返回一個警告。

使用 WITH RETURN 子句，在存儲過程體中聲明游標(biāo)。

為結(jié)果集打開游標(biāo)。當(dāng)存儲過程返回的時候，保持游標(biāo)打開。

在創(chuàng)建存儲過程時指定返回結(jié)果集的個數(shù)可以幫助程序員驗證存儲過程是否返回了所期待數(shù)量的結(jié)果集，提高了程序的完整性。

最佳實踐 2：對輸入?yún)?shù)進(jìn)行必要的的檢查和預(yù)處理

無論使用哪種編程語言，對輸入?yún)?shù)的判定都是必須的。正確的參數(shù)驗證是保證程序良好運行的前提。同樣的，在 DB2 中對輸入?yún)?shù)的驗證和處理也是很重要的。正確的驗證和預(yù)處理操作包括：

假如輸入?yún)?shù)錯誤，存儲過程應(yīng)返回一個明確的值告訴客戶應(yīng)用，然后客戶應(yīng)用可以根據(jù)返回的值進(jìn)行處理，或者向存儲過程提交新的參數(shù)，或者去調(diào)用其他的程序。

根據(jù)業(yè)務(wù)邏輯，對輸入?yún)?shù)作一定的預(yù)處理，如大小寫的轉(zhuǎn)換，NULL 與空字符串或 0 的轉(zhuǎn)換等。

在 DB2 儲存過程開發(fā)中，如需要碰到對空(NULL)進(jìn)行初始化，我們可以使用 COALESCE 函數(shù)。COALESCE函數(shù)返回第一個非空的參數(shù)，語法如下：

清單1：COALESCE 函數(shù)

.---------------.(1)　V　 |>>-COALESCE-------(--expression----,--expression-+--)----------><

COALESCE函數(shù)會依次檢查輸入的參數(shù)，返回第一個不是NULL的參數(shù)，只有當(dāng)傳入COALESCE函數(shù)的所有的參數(shù)都是NULL的時候，函數(shù)才會返回NULL。例如， COALESCE(piName,'')，假如變量piName為NULL，那么函數(shù)會返回''，否則就會返回piName本身的值。

下面的例子展示了如何對參數(shù)進(jìn)行檢查何初始化。

Person表用來存儲個人的基本信息，其定義如下：

表1： Person

下面是用于向表Person插入數(shù)據(jù)的存儲過程的參數(shù)預(yù)處理部分代碼：

SET poGenStatus = 0;　　　 SET piName　 = RTRIM(COALESCE(piName, ''));　 SET piRank　= COALESCE(piRank, 0);　-- make sure all required input parameters are not nullIF ( piNum IS NULL　 OR piName = ''　 OR piAge IS NULL )　THEN　SET poGenStatus = 34100;　RETURN poGenStatus;　END IF;

表Person中num、name和age都是非空字段。對于name字段，多個空格我們也認(rèn)為是空值，所以在進(jìn)行判定前我們調(diào)用RTRIM和COALESCE對其進(jìn)行處理，然后使用 piName = '',對其進(jìn)行非空判定；對于Rank字段，我們希望假如用戶輸入的NULL，我們把它設(shè)置成"0"，對其我們也使用COALESCE進(jìn)行初始化；對于"Age"和"Num" 我們直接使用 IS NULL進(jìn)行非空判定就可以了。

假如輸入?yún)?shù)沒有通過非空判定，我們就對輸出參數(shù)poGenStatus設(shè)置一個確定的值（例子中為 34100）告知調(diào)用者：輸入?yún)?shù)錯誤。

下面是對參數(shù)初始化規(guī)則的一個總結(jié)，供大家參考：

1. 輸入?yún)?shù)為字符類型，且答應(yīng)為空的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成'';

2. 輸入類型為整型，且答應(yīng)為空的，可以使用COALESCE(inputParameter,0)，把空轉(zhuǎn)換成0；

3. 輸入?yún)?shù)為字符類型，且是非空非空格的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成''，然后判定函數(shù)返回值是否為''；

4. 輸入類型為整型，且是非空的，不需要使用COALESCE函數(shù)，直接使用IS NULL進(jìn)行非空判定。

最佳實踐 3：正確設(shè)定游標(biāo)的返回類型

前面我們已經(jīng)討論了如何聲明存儲過程的返回結(jié)果集。這里我們討論一下結(jié)果集返回類型的問題。結(jié)果集的返回類型有兩種：調(diào)用者(CALLER) 和客戶應(yīng)用(CLIENT)。首先我們看一下聲明這兩種游標(biāo)的例子：CREATE PROCEDURE getPeople(IN piAge INTEGER)DYNAMIC RESULT SETS 2READS SQL DATALANGUAGE SQLBEGINDECLARE rs1 CURSOR WITH RETURN TO CLIENT FORSELECT name, age FROM personWHERE age<piAge;DECLARE rs2 CURSOR WITH RETURN TO CALLER FORSELECT NAME, age FROM personWHERE age>piAge;OPEN rs1;OPEN rs2;END

代碼中rs1游標(biāo)的DECLAER語句中包含WITH RETURN TO CLIENT子句，表示結(jié)果集返回給客戶應(yīng)用(CLIENT)。rs2游標(biāo)的DECLARE語句中包含WITH RETURN TO CALLER子句，表示結(jié)果集返回給調(diào)用者(CALLER)。

游標(biāo)返回給調(diào)用者(CALLER)表示由存儲過程的調(diào)用者接收結(jié)果集，而不考慮調(diào)用者是否是另一個存儲過程，還是客戶應(yīng)用。圖（1）中存儲過程PROZ假如聲明為WITH RETURN TO CALLER，那么結(jié)果集會返回給存儲過程PROY，Client Application是不會得到PROZ返回的結(jié)果集的。

圖1：存儲過程遞歸調(diào)用

游標(biāo)返回給客戶應(yīng)用（CLIENT）表示由發(fā)出最初 CALL 語句的客戶應(yīng)用接收結(jié)果集，即使結(jié)果集由嵌套層次中的 15 層深的嵌套存儲過程發(fā)出也是如此。圖1中存儲過程 PROZ 假如聲明為 WITH RETURN TO CLIENT，那么結(jié)果集會返回給 Client Application。返回給客戶應(yīng)用（CLIENT）的游標(biāo)聲明是我們經(jīng)常使用的，也是默認(rèn)的結(jié)果集類型。在聲明返回類型時，我們要認(rèn)真考慮一下，我們需要把結(jié)果集返回給誰，以免丟失返回集，導(dǎo)致程序錯誤。

最佳實踐 4：異常（condition）處理

在存儲過程執(zhí)行的過程中，經(jīng)常因為數(shù)據(jù)或者其他問題產(chǎn)生異常（condition）。根據(jù)業(yè)務(wù)邏輯，存儲過程應(yīng)該對異常進(jìn)行相應(yīng)處理或直接返回給調(diào)用者。此處暫且將condition譯為異常以方便讀者理解。實際上有些異常（condition）并非是由于錯誤引起的，下面將具體講述。

當(dāng)存儲過程中的語句返回的SQLSTATE值超過00000的時候，就表明在存儲過程中產(chǎn)生了一個異常（condition），它表示出現(xiàn)了錯誤、數(shù)據(jù)沒有找到或者出現(xiàn)了警告。為了響應(yīng)和處理存儲過程中出現(xiàn)的異常，我們必須在存儲過程體中聲明異常處理器（condition handler），它可以決定存儲過程怎樣響應(yīng)一個或者多個已定義的異常或者預(yù)定義異常組。聲明條件處理器的語法如下，它會位于變量聲明和游標(biāo)聲明之后：

清單4：聲明異常處理器DECLARE handler-type HANDLER FOR condition handler-action

異常處理器類型(handler-type)有以下幾種：

CONTINUE 在處理器操作完成之后，會繼續(xù)執(zhí)行產(chǎn)生這個異常語句之后的下一條語句。

EXIT 在處理器操作完成之后，存儲過程會終止，并將控制返回給調(diào)用者。

UNDO 在處理器操作執(zhí)行之前，DB2會回滾存儲過程中執(zhí)行的SQL操作。在處理器操作完成之后，存儲過程會終止，并將控制返回給調(diào)用者。

異常處理器可以處理基于特定SQLSTATE值的定制異常，或者處理預(yù)定義異常的類。預(yù)定義的3種異常如下所示：

NOT FOUND 標(biāo)識導(dǎo)致SQLCODE值為＋100或者SQLSATE值為02000的異常。這個異常通常在SELECT沒有返回行的時候出現(xiàn)。

SQLEXCEPTIOIN 標(biāo)識導(dǎo)致SQLCODE值為負(fù)的異常。

SQLWARNING 標(biāo)識導(dǎo)致警告異常或者導(dǎo)致＋100以外的SQLCODE正值的異常。

假如產(chǎn)生了NOT FOUND 或者SQLWARNING異常，并且沒有為這個異常定義異常處理器，那么就會忽略這個異常，并且將控制流轉(zhuǎn)向下一個語句。假如產(chǎn)生了SQLEXCEPTION異常，并且沒有為這個異常定義異常處理器，那么存儲過程就會失敗，并且會將控制流返回調(diào)用者。

以下示例聲明了兩個異常處理器。 EXIT處理器會在出現(xiàn)SQLEXCEPTION 或者SQLWARNING異常的時候被調(diào)用。EXIT處理器會在終止SQL程序之前，將名為stmt的變量設(shè)為"ABORTED",并且將控制流返回給調(diào)用者。UNDO處理器會將控制流返回給調(diào)用者之前，回滾存儲過程體中已經(jīng)完成的SQL操作。

清單5：異常處理器示例DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNINGSET stmt = 'ABORTED';DECLARE UNDO HANDLER FOR NOT FOUND;

假如預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個定制異常聲明處理器。語法如下：

清單6：定制異常處理器DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'

處理器可以由單獨的存儲過程語句定義，也可以使用由BEGIN…END塊界定的復(fù)合語句定義。注重在執(zhí)行符合語句的時候，SQLSATE和SQLCODE的值會被改變，假如需要保留異常前的SQLSATE和SQLCODE，就需要在執(zhí)行復(fù)合語句的第一個語句把SQLSATE和SQLCODE賦予本地變量或參數(shù)。

通常，我們會為存儲過程定義一個執(zhí)行狀態(tài)的輸出參數(shù)（例如：poGenStatus）。

根據(jù)這個輸出狀態(tài)，可以表明存儲過程是否正確執(zhí)行完畢。我們需要定義一些異常處理器為這個輸出參數(shù)賦值。下面是一個例子：

清單7：定義為輸出參數(shù)賦值的異常處理器

　-- Generic HandlerDECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUNDBEGIN NOT ATOMIC　　-- Capture SQLCODE & SQLSTATE　　SELECT　SQLCODE, SQLSTATE　　INTOhSqlcode, hSqlstate　FROMSYSIBM.SYSDUMMY1;　　　-- Use the poGenStatus variable to tell the procedure -- what type of　error occurred　CASE hSqlstate　WHEN '02000' THEN　SET poGenStatus=5000;　WHEN '42724' THEN　SET poGenStatus=3;　ELSE　　IF (hSqlCode < 0) THEN　SET poGenStatus=hSqlCode;　END IF;　 END CASE;　END;

上面的異常處理器會在出現(xiàn)SQLEXCEPTION, SQLWARNING, NOT FOUND異常的時候觸發(fā)。異常處理器會取出當(dāng)前的SQLCODE, SQLSTATE，然后根據(jù)它們的值來設(shè)置輸出參數(shù)（poGenStatus）的值。

我們還可以定制一些異常處理器。例如，我們可以定義一些對參數(shù)進(jìn)行初始化的異常處理器。這里，異常處理器可以看作是一個供存儲過程自己調(diào)用的內(nèi)部函數(shù)。下面是這種情況的一個例子：

清單8：供存儲過程自己調(diào)用的內(nèi)部函數(shù)

------------------------------------------------------- CONDITION declaration　------------------------------------------------------- (80100~80199) SQLCODE & SQLSTATEDECLARE sqlReset CONDITION for sqlstate '80100';　------------------------------------------------------- EXCEPTION HANDLER declaration　------------------------------------------------------- Handy Handler　DECLARE CONTINUE HANDLER FOR sqlResetBEGIN NOT ATOMIC　　SET hSqlcode　 = 0;　SET hSqlstate　= '00000';　　SET poGenStatus = 0;END;　…………　------------------------------------------------------- Procedure Body-----------------------------------------------------SIGNAL sqlreset;　-- insert the record　　…………

上面定制的異常處理器負(fù)責(zé)對參數(shù)hSqlcode，hSqlstate和poGenStatus初始化。當(dāng)我們在程序中需要對它們初始化時，我們只需要調(diào)用SIGNAL sqlreset就可以了。

最佳實踐 5：合理使用臨時表

我們在儲存過程開發(fā)中經(jīng)常使用臨時表。合理的使用臨時表可以簡化程序的編寫，提供執(zhí)行效率，然而濫用臨時表同樣也會使得程序運行效率降低。

臨時表一般在如下情況下使用：

1. 臨時表用于存儲程序運行中的臨時數(shù)據(jù)。例如，假如在一個程序中第一條查詢語句執(zhí)行的結(jié)果會被后續(xù)的查詢語句用到，那么我們可以把第一次查詢的結(jié)果存儲在一個臨時表中供后續(xù)查詢語句使用，而不是在后續(xù)查詢語句中重新查詢一次。假如第一條查詢語句非常復(fù)雜和耗時，那么上面的策略是非常有效的。

2. 臨時表可以用于存儲在一個程序中需要返回多次的結(jié)果集。例如，程序中有一個很耗資源的多表查詢，同時，該查詢在程序中需要執(zhí)行多次，那么就可以把第一次查詢的結(jié)果集存儲在臨時保中，后續(xù)的查詢只需要查臨時表就可以了。

3. 臨時表也可以用于讓SQL訪問非關(guān)系型數(shù)據(jù)庫。例如，可以編寫程序把非關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)插入到一個全局臨時表中，那么我們就可以對其數(shù)據(jù)進(jìn)行查詢。

我們可使用 DECLARE GLOBAL TEMPORARY TABLE 語句來定義臨時表。DB2的臨時表是基于會話的，且在會話之間是隔離的。當(dāng)會話結(jié)束時，臨時表的數(shù)據(jù)被刪除，臨時表被隱式卸下。對臨時表的定義不會在SYSCAT.TABLES中出現(xiàn) 下面是定義臨時表的一個示例：

清單9：定義臨時表DECLARE GLOBAL TEMPORARY TABLE gbl_tempLIKE personON COMMIT DELETE ROWSNOT LOGGEDIN usr_tbsp

此語句創(chuàng)建一個名為 gbl_temp 的用戶臨時表。定義此用戶臨時表 所使用的列的名稱和說明與 person 的列的名稱和說明完全相同。

清單10：創(chuàng)建有兩個字段的臨時表DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2(　 IDINTEGER default 3,　 NAME　CHAR(30))--WITH REPLACENOT LOGGED;--IN USER_TEMP_01;

此語句創(chuàng)建了一個有兩個字段的臨時表。

理論上臨時表是不需要顯示DROP的，因為它是基于會話的，當(dāng)臨時表基于的連接關(guān)閉的時候，臨時表也就不存在了。但是在實際開發(fā)中會有一些情況需要我們對臨時表加以注重。

一種情況就是被調(diào)用的存儲過程的返回值是一個基于臨時表的結(jié)果集。當(dāng)存儲過程執(zhí)行完畢的時候，臨時表并不會消失，因為返回的結(jié)果集相當(dāng)于一個指針，指向臨時表所在的內(nèi)存地址，此時臨時表是不會被DROP掉的。這種情況下，既不能在存儲過程中刪除這個臨時表，也不應(yīng)該由客戶應(yīng)用顯示的刪除臨時表，這就輕易出現(xiàn)一些問題。下面我們通過一個例子來說明這個問題。

下面示例代碼是返回臨時表的存儲過程（get_temp_table）：

清單11：返回臨時表的存儲過程

------------------------------------------------------- TEMPORARY TABLE & CURSOR declaration-----------------------------------------------------DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(　 ID INTEGER,　 NAME　CHAR(30))--WITH REPLACENOT LOGGED;　　 P2: BEGIN　 DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FORSELECT * FROM SESSION.TEMP　 FOR READ ONLY;　 INSERT INTO SESSION.TEMP VALUES(1,piName);　　 OPEN R_CRSR;　　 END P2;