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

讀者定位為具有一定開發經驗的 DB2 開發經驗的開發人員。

讀者可以從本文學習到如何編寫穩定、高效的存儲過程。并可以直接使用文章中提供的 DB2 代碼，從而節省他們的開發和調試時間，提高效率。

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

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

最佳實踐 1：在創建存儲過程語句中提供必要的參數

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

容許 SQL （allowed－SQL）

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

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

CONTAINS SQL： 表示存儲過程可以執行 SQL 語句，但不會讀取 SQL 數據，也不會修改 SQL 數據。

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

MODIFIES SQL DATA： 表示存儲過程可以執行任何 SQL 語句。即可以對數據庫中的數據進行增加、刪除和修改。

假如沒有明確聲明 allowed-SQL，其默認值是 MODIFIES SQL DATA。不同類型的存儲過程執行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。假如存儲過程只是讀取數據，但是因為沒有聲明 allowed-SQL 使其被當作對數據進行修改的存儲過程來執行，這顯然會降低程序的執行效率。因此創建存儲過程時，應當明確聲明其 allowed-SQL。

返回結果集個數（DYNAMIC RESULT SETS n）

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

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

使用 WITH RETURN 子句，在存儲過程體中聲明游標。

為結果集打開游標。當存儲過程返回的時候，保持游標打開。

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

最佳實踐 2：對輸入參數進行必要的的檢查和預處理

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

假如輸入參數錯誤，存儲過程應返回一個明確的值告訴客戶應用，然后客戶應用可以根據返回的值進行處理，或者向存儲過程提交新的參數，或者去調用其他的程序。

根據業務邏輯，對輸入參數作一定的預處理，如大小寫的轉換，NULL 與空字符串或 0 的轉換等。

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

清單1：COALESCE 函數

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

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

下面的例子展示了如何對參數進行檢查何初始化。

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

表1： Person

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

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字段，多個空格我們也認為是空值，所以在進行判定前我們調用RTRIM和COALESCE對其進行處理，然后使用 piName = '',對其進行非空判定；對于Rank字段，我們希望假如用戶輸入的NULL，我們把它設置成"0"，對其我們也使用COALESCE進行初始化；對于"Age"和"Num" 我們直接使用 IS NULL進行非空判定就可以了。

假如輸入參數沒有通過非空判定，我們就對輸出參數poGenStatus設置一個確定的值（例子中為 34100）告知調用者：輸入參數錯誤。

下面是對參數初始化規則的一個總結，供大家參考：

1. 輸入參數為字符類型，且答應為空的，可以使用COALESCE(inputParameter,'')把NULL轉換成'';

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

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

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

最佳實踐 3：正確設定游標的返回類型

前面我們已經討論了如何聲明存儲過程的返回結果集。這里我們討論一下結果集返回類型的問題。結果集的返回類型有兩種：調用者(CALLER) 和客戶應用(CLIENT)。首先我們看一下聲明這兩種游標的例子：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游標的DECLAER語句中包含WITH RETURN TO CLIENT子句，表示結果集返回給客戶應用(CLIENT)。rs2游標的DECLARE語句中包含WITH RETURN TO CALLER子句，表示結果集返回給調用者(CALLER)。

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

圖1：存儲過程遞歸調用

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

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

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

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

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

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

CONTINUE 在處理器操作完成之后，會繼續執行產生這個異常語句之后的下一條語句。

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

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

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

NOT FOUND 標識導致SQLCODE值為＋100或者SQLSATE值為02000的異常。這個異常通常在SELECT沒有返回行的時候出現。

SQLEXCEPTIOIN 標識導致SQLCODE值為負的異常。

SQLWARNING 標識導致警告異常或者導致＋100以外的SQLCODE正值的異常。

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

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

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

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

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

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

通常，我們會為存儲過程定義一個執行狀態的輸出參數（例如：poGenStatus）。

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

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

　-- 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;

上面的異常處理器會在出現SQLEXCEPTION, SQLWARNING, NOT FOUND異常的時候觸發。異常處理器會取出當前的SQLCODE, SQLSTATE，然后根據它們的值來設置輸出參數（poGenStatus）的值。

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

清單8：供存儲過程自己調用的內部函數

------------------------------------------------------- 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　　…………

上面定制的異常處理器負責對參數hSqlcode，hSqlstate和poGenStatus初始化。當我們在程序中需要對它們初始化時，我們只需要調用SIGNAL sqlreset就可以了。

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

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

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

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

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

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

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

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

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

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

此語句創建了一個有兩個字段的臨時表。

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

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

下面示例代碼是返回臨時表的存儲過程（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;