本文以 DB2 開發(fā)人員的角度介紹了在 DB2 存儲過程開發(fā)中需要注意的事項和技巧。新手如果能夠按照本文介紹的最佳實踐來開發(fā)存儲過程，可以避免一些常見的錯誤，從而編寫出高效的程序。本文從初始化參數(shù)、游標(biāo)、異常處理、臨時表的使用以及如何尋找并 rebind 非法存儲過程等常見問題進行了著重討論,并且給出了示例代碼。
DB2 提供的強大功能可以讓開發(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 非法存儲過程等常見問題進行了著重討論,并且給出了示例代碼。
在存儲過程中，開發(fā)人員能夠聲明和設(shè)置 SQL 變量、實現(xiàn)流程控制、處理異常、能夠?qū)?shù)據(jù)進行插入、更新或者刪除。同時，客戶應(yīng)用(這里指調(diào)用存儲過程的應(yīng)用程序，它可以是 JDBC 的調(diào)用，也可以是 ODBC 和 CLI 等)和存儲過程之間可以傳遞參數(shù)，并且從存儲過程中返回結(jié)果集。其中，使用 SQL 編寫的 DB2 存儲過程是在開發(fā)中常見的一種存儲過程。本文主要討論此類存儲過程。
創(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ù)進行增加、刪除和修改。
如果沒有明確聲明 allowed-SQL，其默認(rèn)值是 MODIFIES SQL DATA。不同類型的存儲過程執(zhí)行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。如果存儲過程只是讀取數(shù)據(jù)，但是因為沒有聲明 allowed-SQL 使其被當(dāng)作對數(shù)據(jù)進行修改的存儲過程來執(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é)果集，提高了程序的完整性。
無論使用哪種編程語言，對輸入?yún)?shù)的判斷都是必須的。正確的參數(shù)驗證是保證程序良好運行的前提。同樣的，在 DB2 中對輸入?yún)?shù)的驗證和處理也是很重要的。正確的驗證和預(yù)處理操作包括：
如果輸入?yún)?shù)錯誤，存儲過程應(yīng)返回一個明確的值告訴客戶應(yīng)用，然后客戶應(yīng)用可以根據(jù)返回的值進行處理，或者向存儲過程提交新的參數(shù)，或者去調(diào)用其他的程序。
根據(jù)業(yè)務(wù)邏輯，對輸入?yún)?shù)作一定的預(yù)處理，如大小寫的轉(zhuǎn)換，NULL 與空字符串或 0 的轉(zhuǎn)換等。
在 DB2 儲存過程開發(fā)中，如需要遇到對空(NULL)進行初始化，我們可以使用 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ù)進行檢查何初始化。
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 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我們也認(rèn)為是空值，所以在進行判斷前我們調(diào)用RTRIM和COALESCE對其進行處理，然后使用 piName = '',對其進行非空判斷;對于Rank字段，我們希望如果用戶輸入的NULL，我們把它設(shè)置成"0"，對其我們也使用COALESCE進行初始化;對于"Age"和"Num" 我們直接使用 IS NULL進行非空判斷就可以了。
如果輸入?yún)?shù)沒有通過非空判斷，我們就對輸出參數(shù)poGenStatus設(shè)置一個確定的值(例子中為 34100)告知調(diào)用者：輸入?yún)?shù)錯誤。
下面是對參數(shù)初始化規(guī)則的一個總結(jié)，供大家參考：
1. 輸入?yún)?shù)為字符類型，且允許為空的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成'';
2. 輸入類型為整型，且允許為空的，可以使用COALESCE(inputParameter,0)，把空轉(zhuǎn)換成0;
3. 輸入?yún)?shù)為字符類型，且是非空非空ge的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成''，然后判斷函數(shù)返回值是否為'';
4. 輸入類型為整型，且是非空的，不需要使用COALESCE函數(shù)，直接使用IS NULL進行非空判斷。
前面我們已經(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 SQLBEGIN DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR SELECT name, age FROM person WHERE agepiAge; 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é)果ji hui返回給存儲過程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é)果ji hui返回給 Client Application。返回給客戶應(yīng)用(CLIENT)的游標(biāo)聲明是我們經(jīng)常使用的，也是默認(rèn)的結(jié)果集類型。
在聲明返回類型時，我們要認(rèn)真考慮一下，我們需要把結(jié)果集返回給誰，以免丟失返回集，導(dǎo)致程序錯誤。
新聞熱點
疑難解答
