本文以 DB2 開發人員的角度介紹了在 DB2 存儲過程開發中需要注意的事項和技巧。新手如果能夠按照本文介紹的最佳實踐來開發存儲過程，可以避免一些常見的錯誤，從而編寫出高效的程序。本文從初始化參數、游標、異常處理、臨時表的使用以及如何尋找并 rebind 非法存儲過程等常見問題進行了著重討論,并且給出了示例代碼。
DB2 提供的強大功能可以讓開發人員創建出非常高效穩定的存儲過程。但對于初學者來說，開發出這樣的程序并不容易。本文主要討論開發高效穩定的 DB2 存儲過程的一些常用技巧和方法。
讀者定位為具有一定開發經驗的 DB2 開發經驗的開發人員。
讀者可以從本文學習到如何編寫穩定、高效的存儲過程。并可以直接使用文章中提供的 DB2 代碼，從而節省他們的開發和調試時間，提高效率。
本文以 DB2 開發人員的角度介紹了在 DB2 存儲過程開發中需要注意的事項和技巧。新手如果能夠按照本文介紹的最佳實踐來開發存儲過程，可以避免一些常見的錯誤，從而編寫出高效的程序。本文從初始化參數、游標、異常處理、臨時表的使用以及如何尋找并 rebind 非法存儲過程等常見問題進行了著重討論,并且給出了示例代碼。
在存儲過程中，開發人員能夠聲明和設置 SQL 變量、實現流程控制、處理異常、能夠對數據進行插入、更新或者刪除。同時，客戶應用(這里指調用存儲過程的應用程序，它可以是 JDBC 的調用，也可以是 ODBC 和 CLI 等)和存儲過程之間可以傳遞參數，并且從存儲過程中返回結果集。其中，使用 SQL 編寫的 DB2 存儲過程是在開發中常見的一種存儲過程。本文主要討論此類存儲過程。
創建存儲過程語句(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 子句，在存儲過程體中聲明游標。
為結果集打開游標。當存儲過程返回的時候，保持游標打開。
在創建存儲過程時指定返回結果集的個數可以幫助程序員驗證存儲過程是否返回了所期待數量的結果集，提高了程序的完整性。
無論使用哪種編程語言，對輸入參數的判斷都是必須的。正確的參數驗證是保證程序良好運行的前提。同樣的，在 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 null IF ( piNum IS NULL OR piName = '' OR piAge IS NULL ) THEN SET poGenStatus = 34100; RETURN poGenStatus; END IF;
表Person中num、name和age都是非空字段。對于name字段，多個空ge我們也認為是空值，所以在進行判斷前我們調用RTRIM和COALESCE對其進行處理，然后使用 piName = '',對其進行非空判斷;對于Rank字段，我們希望如果用戶輸入的NULL，我們把它設置成"0"，對其我們也使用COALESCE進行初始化;對于"Age"和"Num" 我們直接使用 IS NULL進行非空判斷就可以了。
如果輸入參數沒有通過非空判斷，我們就對輸出參數poGenStatus設置一個確定的值(例子中為 34100)告知調用者：輸入參數錯誤。
下面是對參數初始化規則的一個總結，供大家參考：
1. 輸入參數為字符類型，且允許為空的，可以使用COALESCE(inputParameter,'')把NULL轉換成'';
2. 輸入類型為整型，且允許為空的，可以使用COALESCE(inputParameter,0)，把空轉換成0;
3. 輸入參數為字符類型，且是非空非空ge的，可以使用COALESCE(inputParameter,'')把NULL轉換成''，然后判斷函數返回值是否為'';
4. 輸入類型為整型，且是非空的，不需要使用COALESCE函數，直接使用IS NULL進行非空判斷。
前面我們已經討論了如何聲明存儲過程的返回結果集。這里我們討論一下結果集返回類型的問題。結果集的返回類型有兩種：調用者(CALLER) 和客戶應用(CLIENT)。首先我們看一下聲明這兩種游標的例子：
CREATE PROCEDURE getPeople(IN piAge INTEGER)DYNAMIC RESULT SETS 2READS SQL DATALANGUAGE SQLBEGIN DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR SELECT name, age FROM person WHERE agepiAge; 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，那么結果ji hui返回給存儲過程PROY，Client Application是不會得到PROZ返回的結果集的。
圖1：存儲過程遞歸調用
游標返回給客戶應用(CLIENT)表示由發出最初 CALL 語句的客戶應用接收結果集，即使結果集由嵌套層次中的 15 層深的嵌套存儲過程發出也是如此。圖1中存儲過程 PROZ 如果聲明為 WITH RETURN TO CLIENT，那么結果ji hui返回給 Client Application。返回給客戶應用(CLIENT)的游標聲明是我們經常使用的，也是默認的結果集類型。
在聲明返回類型時，我們要認真考慮一下，我們需要把結果集返回給誰，以免丟失返回集，導致程序錯誤。
新聞熱點
疑難解答
