.---------------.
(1) V |
>>-COALESCE-------(--expression----,--expression-+--)---------->< COALESCE函數會依次檢查輸入的參數，返回第一個不是NULL的參數，只有當傳入COALESCE函數的所有的參數都是NULL的時候，函數才會返回NULL。例如， COALESCE(piName,'')，假如變量piName為NULL，那么函數會返回''，否則就會返回piName本身的值?！ ∠旅娴睦诱故玖巳绾螌颠M行檢查何初始化?！ erson表用來存儲個人的基本信息，其定義如下： 表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; 上一頁12345678下一頁 表Person中num、name和age都是非空字段。對于name字段，多個空格我們也認為是空值，所以在進行判定前我們調用RTRIM和COALESCE對其進行處理，然后使用 piName = '',對其進行非空判定；對于Rank字段，我們希望假如用戶輸入的NULL，我們把它設置成"0"，對其我們也使用COALESCE進行初始化；對于"Age"和"Num" 我們直接使用 IS NULL進行非空判定就可以了?！ 〖偃巛斎雲禌]有通過非空判定，我們就對輸出參數poGenStatus設置一個確定的值（例子中為 34100）告知調用者：輸入參數錯誤?！ ∠旅媸菍党跏蓟巹t的一個總結，供大家參考： 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 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 上一頁123456789下一頁 代碼中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）的游標聲明是我們經常使用的，也是默認的結果集類型?！ ≡诼暶鞣祷仡愋蜁r，我們要認真考慮一下，我們需要把結果集返回給誰，以免丟失返回集，導致程序錯誤。 最佳實踐 4：異常（condition）處理 在存儲過程執行的過程中，經常因為數據或者其他問題產生異常（condition）。根據業務邏輯，存儲過程應該對異常進行相應處理或直接返回給調用者。此處暫且將condition譯為異常以方便讀者理解。實際上有些異常（condition）并非是由于錯誤引起的，下面將具體講述?！ ‘敶鎯^程中的語句返回的SQLSTATE值超過00000的時候，就表明在存儲過程中產生了一個異常（condition），它表示出現了錯誤、數據沒有找到或者出現了警告。為了響應和處理存儲過程中出現的異常，我們必須在存儲過程體中聲明異常處理器（condition handler），它可以決定存儲過程怎樣響應一個或者多個已定義的異常或者預定義異常組。聲明條件處理器的語法如下，它會位于變量聲明和游標聲明之后： 上一頁12345678910下一頁 清單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, SQLWARNING
SET stmt = 'ABORTED';
DECLARE UNDO HANDLER FOR NOT FOUND; 上一頁234567891011下一頁 假如預定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個定制異常聲明處理器。語法如下： 清單6：定制異常處理器DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate' 處理器可以由單獨的存儲過程語句定義，也可以使用由BEGIN…END塊界定的復合語句定義。注重在執行符合語句的時候，SQLSATE和SQLCODE的值會被改變，假如需要保留異常前的SQLSATE和SQLCODE，就需要在執行復合語句的第一個語句把SQLSATE和SQLCODE賦予本地變量或參數?！ ⊥ǔ＃覀儠榇鎯^程定義一個執行狀態的輸出參數（例如：poGenStatus）?！ 「鶕@個輸出狀態，可以表明存儲過程是否正確執行完畢。我們需要定義一些異常處理器為這個輸出參數賦值。下面是一個例子： 清單7：定義為輸出參數賦值的異常處理器 -- 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; 上一頁3456789101112下一頁 上面的異常處理器會在出現SQLEXCEPTION, SQLWARNING, NOT FOUND異常的時候觸發。異常處理器會取出當前的SQLCODE, SQLSTATE，然后根據它們的值來設置輸出參數（poGenStatus）的值。 我們還可以定制一些異常處理器。例如，我們可以定義一些對參數進行初始化的異常處理器。這里，異常處理器可以看作是一個供存儲過程自己調用的內部函數。下面是這種情況的一個例子： 清單8：供存儲過程自己調用的內部函數 -----------------------------------------------------
-- 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
………… 上一頁456789101112下一頁 上面定制的異常處理器負責對參數hSqlcode，hSqlstate和poGenStatus初始化。當我們在程序中需要對它們初始化時，我們只需要調用SIGNAL sqlreset就可以了?！ ∽罴褜嵺` 5：合理使用臨時表 我們在儲存過程開發中經常使用臨時表。合理的使用臨時表可以簡化程序的編寫，提供執行效率，然而濫用臨時表同樣也會使得程序運行效率降低?！ ∨R時表一般在如下情況下使用： 1. 臨時表用于存儲程序運行中的臨時數據。例如，假如在一個程序中第一條查詢語句執行的結果會被后續的查詢語句用到，那么我們可以把第一次查詢的結果存儲在一個臨時表中供后續查詢語句使用，而不是在后續查詢語句中重新查詢一次。假如第一條查詢語句非常復雜和耗時，那么上面的策略是非常有效的?！ ?. 臨時表可以用于存儲在一個程序中需要返回多次的結果集。例如，程序中有一個很耗資源的多表查詢，同時，該查詢在程序中需要執行多次，那么就可以把第一次查詢的結果集存儲在臨時保中，后續的查詢只需要查臨時表就可以了?！ ?. 臨時表也可以用于讓SQL訪問非關系型數據庫。例如，可以編寫程序把非關系型數據庫中的數據插入到一個全局臨時表中，那么我們就可以對其數據進行查詢。 我們可使用 DECLARE GLOBAL TEMPORARY TABLE 語句來定義臨時表。DB2的臨時表是基于會話的，且在會話之間是隔離的。當會話結束時，臨時表的數據被刪除，臨時表被隱式卸下。對臨時表的定義不會在SYSCAT.TABLES中出現 下面是定義臨時表的一個示例： 清單9：定義臨時表DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE person
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp 此語句創建一個名為 gbl_temp 的用戶臨時表。定義此用戶臨時表 所使用的列的名稱和說明與 person 的列的名稱和說明完全相同。 上一頁56789101112下一頁 清單10：創建有兩個字段的臨時表DECLARE GLOBAL TEMPORARY TABLE session.TEMP2
(
ID INTEGER default 3,
NAME CHAR(30)
)
--WITH REPLACE
NOT LOGGED;
--IN USER_TEMP_01; 此語句創建了一個有兩個字段的臨時表?！ ±碚撋吓R時表是不需要顯示DROP的，因為它是基于會話的，當臨時表基于的連接關閉的時候，臨時表也就不存在了。但是在實際開發中會有一些情況需要我們對臨時表加以注重?！ ∫环N情況就是被調用的存儲過程的返回值是一個基于臨時表的結果集。當存儲過程執行完畢的時候，臨時表并不會消失，因為返回的結果集相當于一個指針，指向臨時表所在的內存地址，此時臨時表是不會被DROP掉的。這種情況下，既不能在存儲過程中刪除這個臨時表，也不應該由客戶應用顯示的刪除臨時表，這就輕易出現一些問題。下面我們通過一個例子來說明這個問題?！ ∠旅媸纠a是返回臨時表的存儲過程（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; 上一頁6789101112下一頁 新聞熱點
疑難解答
