.---------------.
(1) V |
>>-COALESCE-------(--expression----,--expression-+--)---------->< COALESCE函數(shù)會(huì)依次檢查輸入的參數(shù)，返回第一個(gè)不是NULL的參數(shù)，只有當(dāng)傳入COALESCE函數(shù)的所有的參數(shù)都是NULL的時(shí)候，函數(shù)才會(huì)返回NULL。例如， COALESCE(piName,'')，假如變量piName為NULL，那么函數(shù)會(huì)返回''，否則就會(huì)返回piName本身的值。 下面的例子展示了如何對(duì)參數(shù)進(jìn)行檢查何初始化。 Person表用來(lái)存儲(chǔ)個(gè)人的基本信息，其定義如下： 表1： Person 
下面是用于向表Person插入數(shù)據(jù)的存儲(chǔ)過(guò)程的參數(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; 上一頁(yè)12345678下一頁(yè) 表Person中num、name和age都是非空字段。對(duì)于name字段，多個(gè)空格我們也認(rèn)為是空值，所以在進(jìn)行判定前我們調(diào)用RTRIM和COALESCE對(duì)其進(jìn)行處理，然后使用 piName = '',對(duì)其進(jìn)行非空判定；對(duì)于Rank字段，我們希望假如用戶輸入的NULL，我們把它設(shè)置成"0"，對(duì)其我們也使用COALESCE進(jìn)行初始化；對(duì)于"Age"和"Num" 我們直接使用 IS NULL進(jìn)行非空判定就可以了。 假如輸入?yún)?shù)沒(méi)有通過(guò)非空判定，我們就對(duì)輸出參數(shù)poGenStatus設(shè)置一個(gè)確定的值（例子中為 34100）告知調(diào)用者：輸入?yún)?shù)錯(cuò)誤。 下面是對(duì)參數(shù)初始化規(guī)則的一個(gè)總結(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)行非空判定。 最佳實(shí)踐 3：正確設(shè)定游標(biāo)的返回類型 前面我們已經(jīng)討論了如何聲明存儲(chǔ)過(guò)程的返回結(jié)果集。這里我們討論一下結(jié)果集返回類型的問(wèn)題。結(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 上一頁(yè)123456789下一頁(yè) 代碼中rs1游標(biāo)的DECLAER語(yǔ)句中包含WITH RETURN TO CLIENT子句，表示結(jié)果集返回給客戶應(yīng)用(CLIENT)。rs2游標(biāo)的DECLARE語(yǔ)句中包含WITH RETURN TO CALLER子句，表示結(jié)果集返回給調(diào)用者(CALLER)。 游標(biāo)返回給調(diào)用者(CALLER)表示由存儲(chǔ)過(guò)程的調(diào)用者接收結(jié)果集，而不考慮調(diào)用者是否是另一個(gè)存儲(chǔ)過(guò)程，還是客戶應(yīng)用。圖（1）中存儲(chǔ)過(guò)程PROZ假如聲明為WITH RETURN TO CALLER，那么結(jié)果集會(huì)返回給存儲(chǔ)過(guò)程PROY，Client application是不會(huì)得到PROZ返回的結(jié)果集的。 圖1：存儲(chǔ)過(guò)程遞歸調(diào)用 
游標(biāo)返回給客戶應(yīng)用（CLIENT）表示由發(fā)出最初 CALL 語(yǔ)句的客戶應(yīng)用接收結(jié)果集，即使結(jié)果集由嵌套層次中的 15 層深的嵌套存儲(chǔ)過(guò)程發(fā)出也是如此。圖1中存儲(chǔ)過(guò)程 PROZ 假如聲明為 WITH RETURN TO CLIENT，那么結(jié)果集會(huì)返回給 Client Application。返回給客戶應(yīng)用（CLIENT）的游標(biāo)聲明是我們經(jīng)常使用的，也是默認(rèn)的結(jié)果集類型。 在聲明返回類型時(shí)，我們要認(rèn)真考慮一下，我們需要把結(jié)果集返回給誰(shuí)，以免丟失返回集，導(dǎo)致程序錯(cuò)誤。 最佳實(shí)踐 4：異常（condition）處理 在存儲(chǔ)過(guò)程執(zhí)行的過(guò)程中，經(jīng)常因?yàn)閿?shù)據(jù)或者其他問(wèn)題產(chǎn)生異常（condition）。根據(jù)業(yè)務(wù)邏輯，存儲(chǔ)過(guò)程應(yīng)該對(duì)異常進(jìn)行相應(yīng)處理或直接返回給調(diào)用者。此處暫且將condition譯為異常以方便讀者理解。實(shí)際上有些異常（condition）并非是由于錯(cuò)誤引起的，下面將具體講述。 當(dāng)存儲(chǔ)過(guò)程中的語(yǔ)句返回的SQLSTATE值超過(guò)00000的時(shí)候，就表明在存儲(chǔ)過(guò)程中產(chǎn)生了一個(gè)異常（condition），它表示出現(xiàn)了錯(cuò)誤、數(shù)據(jù)沒(méi)有找到或者出現(xiàn)了警告。為了響應(yīng)和處理存儲(chǔ)過(guò)程中出現(xiàn)的異常，我們必須在存儲(chǔ)過(guò)程體中聲明異常處理器（condition handler），它可以決定存儲(chǔ)過(guò)程怎樣響應(yīng)一個(gè)或者多個(gè)已定義的異常或者預(yù)定義異常組。聲明條件處理器的語(yǔ)法如下，它會(huì)位于變量聲明和游標(biāo)聲明之后： 上一頁(yè)12345678910下一頁(yè) 清單4：聲明異常處理器DECLARE handler-type HANDLER FOR condition handler-action 異常處理器類型(handler-type)有以下幾種： CONTINUE 在處理器操作完成之后，會(huì)繼續(xù)執(zhí)行產(chǎn)生這個(gè)異常語(yǔ)句之后的下一條語(yǔ)句。 EXIT 在處理器操作完成之后，存儲(chǔ)過(guò)程會(huì)終止，并將控制返回給調(diào)用者。 UNDO 在處理器操作執(zhí)行之前，DB2會(huì)回滾存儲(chǔ)過(guò)程中執(zhí)行的SQL操作。在處理器操作完成之后，存儲(chǔ)過(guò)程會(huì)終止，并將控制返回給調(diào)用者。 異常處理器可以處理基于特定SQLSTATE值的定制異常，或者處理預(yù)定義異常的類。預(yù)定義的3種異常如下所示： NOT FOUND 標(biāo)識(shí)導(dǎo)致SQLCODE值為＋100或者SQLSATE值為02000的異常。這個(gè)異常通常在SELECT沒(méi)有返回行的時(shí)候出現(xiàn)。 SQLEXCEPTIOIN 標(biāo)識(shí)導(dǎo)致SQLCODE值為負(fù)的異常。 SQLWARNING 標(biāo)識(shí)導(dǎo)致警告異常或者導(dǎo)致＋100以外的SQLCODE正值的異常。 假如產(chǎn)生了NOT FOUND 或者SQLWARNING異常，并且沒(méi)有為這個(gè)異常定義異常處理器，那么就會(huì)忽略這個(gè)異常，并且將控制流轉(zhuǎn)向下一個(gè)語(yǔ)句。假如產(chǎn)生了SQLEXCEPTION異常，并且沒(méi)有為這個(gè)異常定義異常處理器，那么存儲(chǔ)過(guò)程就會(huì)失敗，并且會(huì)將控制流返回調(diào)用者。 以下示例聲明了兩個(gè)異常處理器。 EXIT處理器會(huì)在出現(xiàn)SQLEXCEPTION 或者SQLWARNING異常的時(shí)候被調(diào)用。EXIT處理器會(huì)在終止SQL程序之前，將名為stmt的變量設(shè)為"ABORTED",并且將控制流返回給調(diào)用者。UNDO處理器會(huì)將控制流返回給調(diào)用者之前，回滾存儲(chǔ)過(guò)程體中已經(jīng)完成的SQL操作。 清單5：異常處理器示例DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
SET stmt = 'ABORTED';
DECLARE UNDO HANDLER FOR NOT FOUND; 上一頁(yè)234567891011下一頁(yè) 假如預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個(gè)定制異常聲明處理器。語(yǔ)法如下： 清單6：定制異常處理器DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate' 處理器可以由單獨(dú)的存儲(chǔ)過(guò)程語(yǔ)句定義，也可以使用由BEGIN…END塊界定的復(fù)合語(yǔ)句定義。注重在執(zhí)行符合語(yǔ)句的時(shí)候，SQLSATE和SQLCODE的值會(huì)被改變，假如需要保留異常前的SQLSATE和SQLCODE，就需要在執(zhí)行復(fù)合語(yǔ)句的第一個(gè)語(yǔ)句把SQLSATE和SQLCODE賦予本地變量或參數(shù)。 通常，我們會(huì)為存儲(chǔ)過(guò)程定義一個(gè)執(zhí)行狀態(tài)的輸出參數(shù)（例如：poGenStatus）。 根據(jù)這個(gè)輸出狀態(tài)，可以表明存儲(chǔ)過(guò)程是否正確執(zhí)行完畢。我們需要定義一些異常處理器為這個(gè)輸出參數(shù)賦值。下面是一個(gè)例子： 清單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; 上一頁(yè)3456789101112下一頁(yè) 上面的異常處理器會(huì)在出現(xiàn)SQLEXCEPTION, SQLWARNING, NOT FOUND異常的時(shí)候觸發(fā)。異常處理器會(huì)取出當(dāng)前的SQLCODE, SQLSTATE，然后根據(jù)它們的值來(lái)設(shè)置輸出參數(shù)（poGenStatus）的值。 我們還可以定制一些異常處理器。例如，我們可以定義一些對(duì)參數(shù)進(jìn)行初始化的異常處理器。這里，異常處理器可以看作是一個(gè)供存儲(chǔ)過(guò)程自己調(diào)用的內(nèi)部函數(shù)。下面是這種情況的一個(gè)例子： 清單8：供存儲(chǔ)過(guò)程自己調(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
………… 上一頁(yè)456789101112下一頁(yè) 上面定制的異常處理器負(fù)責(zé)對(duì)參數(shù)hSqlcode，hSqlstate和poGenStatus初始化。當(dāng)我們?cè)诔绦蛑行枰獙?duì)它們初始化時(shí)，我們只需要調(diào)用SIGNAL sqlreset就可以了。 最佳實(shí)踐 5：合理使用臨時(shí)表 我們?cè)趦?chǔ)存過(guò)程開(kāi)發(fā)中經(jīng)常使用臨時(shí)表。合理的使用臨時(shí)表可以簡(jiǎn)化程序的編寫(xiě)，提供執(zhí)行效率，然而濫用臨時(shí)表同樣也會(huì)使得程序運(yùn)行效率降低。 臨時(shí)表一般在如下情況下使用： 1. 臨時(shí)表用于存儲(chǔ)程序運(yùn)行中的臨時(shí)數(shù)據(jù)。例如，假如在一個(gè)程序中第一條查詢語(yǔ)句執(zhí)行的結(jié)果會(huì)被后續(xù)的查詢語(yǔ)句用到，那么我們可以把第一次查詢的結(jié)果存儲(chǔ)在一個(gè)臨時(shí)表中供后續(xù)查詢語(yǔ)句使用，而不是在后續(xù)查詢語(yǔ)句中重新查詢一次。假如第一條查詢語(yǔ)句非常復(fù)雜和耗時(shí)，那么上面的策略是非常有效的。 2. 臨時(shí)表可以用于存儲(chǔ)在一個(gè)程序中需要返回多次的結(jié)果集。例如，程序中有一個(gè)很耗資源的多表查詢，同時(shí)，該查詢?cè)诔绦蛑行枰獔?zhí)行多次，那么就可以把第一次查詢的結(jié)果集存儲(chǔ)在臨時(shí)保中，后續(xù)的查詢只需要查臨時(shí)表就可以了。 3. 臨時(shí)表也可以用于讓SQL訪問(wèn)非關(guān)系型數(shù)據(jù)庫(kù)。例如，可以編寫(xiě)程序把非關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)插入到一個(gè)全局臨時(shí)表中，那么我們就可以對(duì)其數(shù)據(jù)進(jìn)行查詢。 我們可使用 DECLARE GLOBAL TEMPORARY TABLE 語(yǔ)句來(lái)定義臨時(shí)表。DB2的臨時(shí)表是基于會(huì)話的，且在會(huì)話之間是隔離的。當(dāng)會(huì)話結(jié)束時(shí)，臨時(shí)表的數(shù)據(jù)被刪除，臨時(shí)表被隱式卸下。對(duì)臨時(shí)表的定義不會(huì)在SYSCAT.TABLES中出現(xiàn) 下面是定義臨時(shí)表的一個(gè)示例： 清單9：定義臨時(shí)表DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE person
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp 此語(yǔ)句創(chuàng)建一個(gè)名為 gbl_temp 的用戶臨時(shí)表。定義此用戶臨時(shí)表 所使用的列的名稱和說(shuō)明與 person 的列的名稱和說(shuō)明完全相同。 上一頁(yè)56789101112下一頁(yè) 清單10：創(chuàng)建有兩個(gè)字段的臨時(shí)表DECLARE GLOBAL TEMPORARY TABLE session.TEMP2
(
ID INTEGER default 3,
NAME CHAR(30)
)
--WITH REPLACE
NOT LOGGED;
--IN USER_TEMP_01; 此語(yǔ)句創(chuàng)建了一個(gè)有兩個(gè)字段的臨時(shí)表。 理論上臨時(shí)表是不需要顯示DROP的，因?yàn)樗腔跁?huì)話的，當(dāng)臨時(shí)表基于的連接關(guān)閉的時(shí)候，臨時(shí)表也就不存在了。但是在實(shí)際開(kāi)發(fā)中會(huì)有一些情況需要我們對(duì)臨時(shí)表加以注重。 一種情況就是被調(diào)用的存儲(chǔ)過(guò)程的返回值是一個(gè)基于臨時(shí)表的結(jié)果集。當(dāng)存儲(chǔ)過(guò)程執(zhí)行完畢的時(shí)候，臨時(shí)表并不會(huì)消失，因?yàn)榉祷氐慕Y(jié)果集相當(dāng)于一個(gè)指針，指向臨時(shí)表所在的內(nèi)存地址，此時(shí)臨時(shí)表是不會(huì)被DROP掉的。這種情況下，既不能在存儲(chǔ)過(guò)程中刪除這個(gè)臨時(shí)表，也不應(yīng)該由客戶應(yīng)用顯示的刪除臨時(shí)表，這就輕易出現(xiàn)一些問(wèn)題。下面我們通過(guò)一個(gè)例子來(lái)說(shuō)明這個(gè)問(wèn)題。 下面示例代碼是返回臨時(shí)表的存儲(chǔ)過(guò)程（get_temp_table）： 清單11：返回臨時(shí)表的存儲(chǔ)過(guò)程 -----------------------------------------------------
-- 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; 上一頁(yè)6789101112下一頁(yè) 新聞熱點(diǎn)
疑難解答
圖片精選
