讀者定位為具有一定開發(fā)經(jīng)驗的 DB2 開發(fā)經(jīng)驗的開發(fā)人員。
讀者可以從本文學(xué)習(xí)到如何編寫穩(wěn)定、高效的存儲過程。并可以直接使用文章中提供的 DB2 代碼，從而節(jié)省他們的開發(fā)和調(diào)試時間，<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">提高</nobr>效率。
本文以 DB2 開發(fā)人員的角度介紹了在 DB2 存儲過程開發(fā)中需要注意的事項和技巧。新手如果能夠按照本文介紹的最佳實踐來開發(fā)存儲過程，可以避免一些常見的錯誤，從而編寫出高效的程序。本文從初始化參數(shù)、游標(biāo)、異常處理、臨時表的使用以及如何<nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="key3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">尋找</nobr>并 rebind 非法存儲過程等常見問題進(jìn)行了著重討論,并且給出了示例代碼。
在存儲過程中，開發(fā)人員能夠聲明和設(shè)置 SQL 變量、實現(xiàn)流程<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">控制</nobr>、處理異常、能夠?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í)行<nobr oncontextmenu="return false;" onmousemove="kwM(5);" id="key4" onmouseover="kwE(event,5, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">效率</nobr>。下面是一些常用的參數(shù)
容許 SQL （allowed－SQL）
容許 SQL （allowed－SQL）子句的值指定了存儲過程是否會使用 SQL 語句，如果使用，其類型如何。它的可能值如下所示：
NO SQL： 表示存儲過程不能夠執(zhí)行任何 SQL 語句。
CONTAINS SQL： 表示<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程可以執(zhí)行 SQL 語句，但不會讀取 SQL 數(shù)據(jù)，也不會修改 SQL 數(shù)據(jù)。
READS SQL DATA： 表示在存儲過程中包含不會修改 SQL <nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="key3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">數(shù)據(jù)</nobr>的 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é)果集，<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">提高</nobr>了程序的完整性。
最佳實踐 2：對輸入?yún)?shù)進(jìn)行必要的的檢查和預(yù)處理
無論使用哪種編程語言，對輸入<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key4" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">參數(shù)</nobr>的判斷都是必須的。正確的參數(shù)驗證是保證程序良好運行的前提。同樣的，在 DB2 中對輸入?yún)?shù)的驗證和處理也是很重要的。正確的驗證和預(yù)處理操作包括：
如果輸入?yún)?shù)錯誤，<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key3" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: 0px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程應(yīng)返回一個明確的值告訴客戶應(yīng)用，然后客戶應(yīng)用可以根據(jù)返回的值進(jìn)行處理，或者向存儲過程提交新的參數(shù)，或者去調(diào)用其他的程序。
根據(jù)<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">業(yè)務(wù)</nobr>邏輯，對輸入?yún)?shù)作一定的預(yù)處理，如大小寫的轉(zhuǎn)換，NULL 與空字符串或 0 的轉(zhuǎn)換等。
在 DB2 儲存過程<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: 0px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">開發(fā)</nobr>中，如需要遇到對空(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插入<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">數(shù)據(jù)</nobr>的存儲過程的參數(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字段，多個空格我們也認(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ù)為字符類型，且允許為空的，可以使用COALESCE(inputParameter,'')把NULL轉(zhuǎn)換成'';
2. 輸入類型為整型，且允許為空的，可以使用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 2
READS SQL DATA
LANGUAGE SQL
BEGIN
DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR
SELECT name, age FROM person
WHERE age<piAge;
DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR
SELECT NAME, age FROM person
WHERE age>piAge;
OPEN rs1;
OPEN rs2;
END
代碼中rs1游標(biāo)的DECLAER語句中包含WITH RETURN TO CLIENT子句，表示結(jié)果集返回給<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">客戶</nobr>應(yīng)用(CLIENT)。rs2游標(biāo)的DECLARE語句中包含WITH RETURN TO CALLER子句，表示結(jié)果集返回給調(diào)用者(CALLER)。
游標(biāo)返回給調(diào)用者(CALLER)表示由<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程的調(diào)用者接收結(jié)果集，而不考慮調(diào)用者是否是另一個存儲過程，還是客戶<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key4" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">應(yīng)用</nobr>。圖（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)致程序錯誤。
最佳<nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="key3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">實踐</nobr> 4：異常（condition）處理
在存儲過程執(zhí)行的過程中，經(jīng)常因為<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">數(shù)據(jù)</nobr>或者其他問題產(chǎn)生異常（condition）。根據(jù)業(yè)務(wù)邏輯，存儲過程應(yīng)該對異常進(jìn)行相應(yīng)處理或直接返回給調(diào)用者。此處暫且將condition譯為異常以方便讀者理解。實際上有些異常（condition）并非是由于錯誤引起的，下面將詳細(xì)講述。
當(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 在處理器操作完成之后，存儲過程會終止，并將<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">控制</nobr>返回給調(diào)用者。
UNDO 在處理器操作執(zhí)行之前，DB2會回滾存儲過程中執(zhí)行的SQL操作。在處理器操作完成之后，<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程會終止，并將控制返回給調(diào)用者。
異常處理器可以處理基于特定SQLSTATE值的定制異常，或者處理<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">預(yù)定</nobr>義異常的類。預(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, SQLWARNING
SET stmt = 'ABORTED';
DECLARE UNDO HANDLER FOR NOT FOUND;
如果預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個定制異常聲明處理器。語法如下：
清單6：定制異常處理器
DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'
處理器可以由單獨的<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: 0px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程語句定義，也可以使用由BEGIN…END塊界定的復(fù)合語句定義。注意在執(zhí)行符合語句的時候，SQLSATE和SQLCODE的值會被改變，如果需要保留異常前的SQLSATE和SQLCODE，就需要在執(zhí)行復(fù)合語句的第一個語句把SQLSATE和SQLCODE賦予本地變量或<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key2" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">參數(shù)</nobr>。
通常，我們會為存儲過程定義一個執(zhí)行狀態(tài)的輸出參數(shù)（例如：poGenStatus）。
根據(jù)這個輸出狀態(tài)，可以表明存儲過程是否正確執(zhí)行完畢。我們需要定義一些異常處理器為這個輸出參數(shù)賦值。下面是一個例子：
清單7：定義為輸出參數(shù)賦值的異常處理器
-- Generic Handler
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN NOT ATOMIC
-- Capture SQLCODE & SQLSTATE
SELECT SQLCODE, SQLSTATE
INTO hSqlcode, hSqlstate
FROM SYSIBM.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è)置輸出<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: 0px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">參數(shù)</nobr>（poGenStatus）的值。
我們還可以定制一些異常處理器。例如，我們可以定義一些對參數(shù)進(jìn)行初始化的異常處理器。這里，異常處理器可以看作是一個供<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程自己調(diào)用的內(nèi)部函數(shù)。下面是這種情況的一個例子：
清單8：供存儲過程自己調(diào)用的內(nèi)部函數(shù)
-----------------------------------------------------
-- CONDITION declaration
-----------------------------------------------------
-- (80100~80199) SQLCODE & SQLSTATE
DECLARE sqlReset CONDITION for sqlstate '80100';
-----------------------------------------------------
-- EXCEPTION HANDLER declaration
-----------------------------------------------------
-- Handy Handler
DECLARE CONTINUE HANDLER FOR sqlReset
BEGIN 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：合理使用臨時表
我們在儲存過程<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">開發(fā)</nobr>中經(jīng)常使用臨時表。合理的使用臨時表可以簡化程序的編寫，提供執(zhí)行效率，然而濫用臨時表同樣也會使得程序運行效率降低。
臨時表一般在如下情況下使用：
1. 臨時表用于<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>程序運行中的臨時數(shù)據(jù)。例如，如果在一個程序中第一條查詢語句執(zhí)行的結(jié)果會被后續(xù)的查詢語句用到，那么我們可以把第一次查詢的結(jié)果存儲在一個臨時表中供后續(xù)查詢語句使用，而不是在后續(xù)查詢語句中重新查詢一次。如果第一條查詢語句非常復(fù)雜和耗時，那么上面的策略是非常有效的。
2. 臨時表可以用于存儲在一個程序中需要返回多次的結(jié)果集。例如，程序中有一個很耗資源的多表查詢，同時，該查詢在程序中需要執(zhí)行多次，那么就可以把第一次查詢的結(jié)果集存儲在臨時保中，后續(xù)的查詢只需要查臨時表就可以了。
3. 臨時表也可以用于讓SQL訪問非關(guān)系型<nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="key3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">數(shù)據(jù)庫</nobr>。例如，可以編寫程序把非關(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_temp
LIKE person
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
此語句創(chuàng)建一個名為 gbl_temp 的用戶臨時表。定義此用戶臨時表 所使用的列的名稱和說明與 person 的列的名稱和說明完全相同。
清單10：創(chuàng)建有兩個字段的臨時表
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2
(
ID INTEGER default 3,
NAME CHAR(30)
)
--WITH REPLACE
NOT LOGGED;
--IN USER_TEMP_01;
此語句創(chuàng)建了一個有兩個字段的臨時表。
理論上臨時表是不需要<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">顯示</nobr>DROP的，因為它是基于會話的，當(dāng)臨時表基于的連接關(guān)閉的時候，臨時表也就不存在了。但是在實際<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">開發(fā)</nobr>中會有一些情況需要我們對臨時表加以注意。
一種情況就是被調(diào)用的存儲過程的返回值是一個基于臨時表的結(jié)果集。當(dāng)<nobr oncontextmenu="return false;" onmousemove="kwM(5);" id="key5" onmouseover="kwE(event,5, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程執(zhí)行完畢的時候，臨時表并不會消失，因為返回的結(jié)果集相當(dāng)于一個指針，指向臨時表所在的<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key4" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">內(nèi)存</nobr>地址，此時臨時表是不會被DROP掉的。這種情況下，既不能在存儲過程中刪除這個臨時表，也不應(yīng)該由客戶<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">應(yīng)用</nobr>顯示的刪除臨時表，這就容易出現(xiàn)一些問題。下面我們通過一個例子來說明這個問題。
下面示例代碼是返回臨時表的存儲過程（get_temp_table）：
清單11：返回臨時表的存儲過程
-----------------------------------------------------
-- TEMPORARY TABLE & CURSOR declaration
-----------------------------------------------------
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
(
ID INTEGER,
NAME CHAR(30)
)
--WITH REPLACE
NOT LOGGED;
P2: BEGIN
DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM SESSION.TEMP
FOR READ ONLY;
INSERT INTO SESSION.TEMP VALUES(1,piName);
OPEN R_CRSR;
END P2;
存儲
過程中聲明了有兩個字段的臨時表TEMP,聲明了一個游標(biāo)R_CRSR返回臨時表中所有<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">記錄</nobr>，最后在臨時表中插入兩條記錄。
程序第一次執(zhí)行的結(jié)果如下：
圖2：程序第一次執(zhí)行的結(jié)果
可以從圖中看出，運行結(jié)果是我們期望的。那么如果我們再運行一次，會有什么結(jié)果呢？下圖是其運行結(jié)果：
圖3：程序再次執(zhí)行的結(jié)果
第二次執(zhí)行的時候程序卻出錯了，這是因為在同一個連接中，臨時表并沒有被DROP掉，所以在第二次調(diào)用存儲過程的時候就會出現(xiàn)臨時表已經(jīng)存在的錯誤。
另外一種情況，就是很多時候例如在websphere中通過JDBC連接<nobr oncontextmenu="return false;" onmousemove="kwM(4);" id="key4" onmouseover="kwE(event,4, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">數(shù)據(jù)</nobr>庫時使用了連接池的技術(shù)，這帶來了一些效率的提升，同時在某些情況下也容易讓人誤解。客戶<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">應(yīng)用</nobr>程序中關(guān)閉了數(shù)據(jù)庫連接，但是并不一定真正關(guān)閉了數(shù)據(jù)庫連接，如果客戶應(yīng)用程序使用了臨時表而數(shù)據(jù)庫連接并沒有關(guān)閉，那么臨時表就不會被DROP。當(dāng)連接池把這個連接分給另一個客戶程序的時候，新的客戶程序仍然可以使用舊的臨時表，這不是我們希望的。如果想避免上述問題，可以在創(chuàng)建臨時表時，加上WITH REPLACE；或者根據(jù)<nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="key3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">業(yè)務(wù)</nobr>邏輯在合適的地方顯示的DROP臨時表。
下面是使用WITH REPLACE創(chuàng)建臨時表的執(zhí)行情況。
圖4：使用WITH REPLACE創(chuàng)建臨時表
可以看出在一個連接里面，多次調(diào)用<nobr oncontextmenu="return false;" onmousemove="kwM(2);" id="key2" onmouseover="kwE(event,2, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">存儲</nobr>過程get_temp_table，也不會出現(xiàn)問題。臨時表在某些情況下也是需要避免使用的。大家知道臨時表是存放在內(nèi)存中的，如果一個臨時表有上萬或者十幾萬條<nobr oncontextmenu="return false;" onmousemove="kwM(0);" id="key0" onmouseover="kwE(event,0, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">記錄</nobr>，同時程序的并發(fā)數(shù)很大，那么在內(nèi)存中建立的臨時表耗費的資源就很龐大，此時數(shù)據(jù)庫的性能會急劇下降，甚至?xí)?dǎo)致數(shù)據(jù)庫崩潰。因此，大家在使用臨時表的時候，需要考慮它對資源的耗費，避免盲目使用臨時表。
最佳<nobr oncontextmenu="return false;" onmousemove="kwM(5);" id="key3" onmouseover="kwE(event,5, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">實踐</nobr> 6：尋找并rebind 非法的存儲過程
存儲過程會因為其涉及和引用的對象發(fā)生了改變而導(dǎo)致其非法（invalid），例如：修改了表結(jié)構(gòu)，導(dǎo)致引用該表的存儲過程非法，或者重新編譯一個存儲過程，會使調(diào)用這個存儲過程的父存儲過程非法。此時我們需要對非法的存儲過程重新編譯(rebind)。但是，對非法的存儲過程進(jìn)行rebind的時候，需要確定其引用的對象是合法的，否則非法的存儲過程也不能rebind成功。
這里我們介紹一下發(fā)現(xiàn)和rebind非法存儲過程的方法。我們是通過判斷SYSCAT.routines中VALID字段的值來查找非法存儲過程的。下面是查找非法存儲過程的一段代碼：
清單12：查找非法存儲過程
SELECT
RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname ,
' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )'
FROM
SYSCAT.routines r
WHERE
r.routinetype = 'P'
AND ((r.origin = 'Q' AND r.valid != 'Y')
OR EXISTS (
SELECT 1 FROM syscat.packages
WHERE pkgschema = r.routineschema
AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)
AND valid !='Y'
)
)
ORDER BY
spname;
獲得的結(jié)果如下：
清單13：查找非法存儲過程的結(jié)果
SPNAME
----------------------------------
TEST.DEMO_INFO_8 (TEST. P3550884)
可以使用下面的命令rebind它們
清單14：Rebind 非法存儲過程語法
rebind package packagename resolve any@
Packagename就是查詢結(jié)果中括號里的值。例如，如果rebind上面查出來的存儲過程。我們只需要執(zhí)行下面語句
清單15：Rebind 非法存儲過程
rebind package TEST.P3550884 resolve any@
當(dāng)然，如果此存儲過程程序本身有問題，需要先修改存儲過程代碼后再進(jìn)行編譯。
類似的，通過下面的代碼可以獲得非法的視圖。
清單16：獲得非法的視圖
SELECT
RTRIM(viewschema) || '.' || RTRIM(viewname) AS viewname
FROM
SYSCAT.views
WHERE
valid = 'X'
ORDER BY
viewname;
結(jié)束語
本文介紹了我們在 DB2 存儲過程<nobr oncontextmenu="return false;" onmousemove="kwM(1);" id="key1" onmouseover="kwE(event,1, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC();" onmouseout="kwL(event, this);" target="_blank">開發(fā)</nobr>中經(jīng)常用到的一些技巧。同時這些技巧也是編寫優(yōu)秀存儲過程的基本要求。本文介紹的一些技巧只是揭開了高效使用 DB2 的冰山一角。DB2 為我們提供了豐富和強大的功能。在使用 DB2 的時候，我們應(yīng)當(dāng)深入理解其原理，找出更多的最佳實踐與大家分享。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
