DB2 程式設計技巧 (1)：建立程序禁忌、暫存表限制、FETCH FIRST 替代方案、游標兩種寫法、CASE/LOCATE/DAYS 函數對照

前言

本文整理 DB2 Stored Procedure 開發實務中常見的陷阱與替代寫法，涵蓋 CREATE PROCEDURE 縮排禁忌、暫存表表空間需求、單筆查詢變數賦值、游標兩種宣告風格、CASE 表達式替代 DECODE、LOCATE/DAYS 函數對照 Oracle/SQL Server。

一、CREATE PROCEDURE 縮排禁忌

-- 錯誤：CREATE 後使用 TAB 鍵縮排
CREATE    PROCEDURE my_proc()  -- 編譯失敗

-- 正確：僅使用空格
CREATE PROCEDURE my_proc()

DB2 解析器對關鍵字後的空白字元敏感，務必使用空格，不可使用 Tab。

二、暫存表 (DGTT) 必須建立在 USER TEMPORARY TABLESPACE

• DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_tab ... 僅能在 USER TEMPORARY TABLESPACE 建立。
• 若資料庫僅有 SYSTEM TEMPORARY TABLESPACE，則無法建立 DGTT。
• DGTT 僅在當前 Session 有效；多執行緒/連線池環境下極難控制生命週期，建議改用永久表 + 定期清理。
• 建表時加 WITH REPLACE 選項，避免重複建立報錯：
  DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_tab (c1 INT) WITH REPLACE ON COMMIT PRESERVE ROWS;

三、查詢單筆資料賦值給變數：FETCH FIRST 1 ROW ONLY 限制

不支援：

SELECT col INTO v_var FROM tab FETCH FIRST 1 ROW ONLY;  -- 語法錯誤

替代方案：游標 + FETCH FIRST

DECLARE v_var CHAR(1);
DECLARE c1 CURSOR FOR SELECT col FROM tab FETCH FIRST 1 ROW ONLY FOR UPDATE;
OPEN c1;
FETCH c1 INTO v_var;
CLOSE c1;

四、游標兩種宣告風格

4.1 明確游標 (支援 WITH HOLD、COMMIT/ROLLBACK 不關閉)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_notfound = 1;
DECLARE c1 CURSOR WITH HOLD FOR SELECT col FROM tab FOR UPDATE;
OPEN c1;
SET v_notfound = 0;
FETCH c1 INTO v_var;
WHILE v_notfound = 0 DO
    -- 處理邏輯
    SET v_notfound = 0;
    FETCH c1 INTO v_var;
END WHILE;
CLOSE c1;

• 優點：可使用 WITH HOLD 保持游標跨 COMMIT/ROLLBACK 開啟；彈性最高。
• 缺點：程式碼較冗長。

4.2 FOR 迴圈游標 (簡潔、自動 OPEN/FETCH/CLOSE)

FOR v_loop AS c1 CURSOR FOR SELECT col AS col FROM tab FOR UPDATE DO
    -- 直接使用 v_loop.col
END FOR;

• 優點：程式碼極簡潔，不需手動 OPEN/FETCH/CLOSE。
• 缺點：不支援 WITH HOLD；迴圈內若有 COMMIT/ROLLBACK 會導致游標關閉，不可用。

五、修改游標當前列 (WHERE CURRENT OF)

UPDATE tab SET col = '0' WHERE CURRENT OF c1;

前提：游標宣告需加 FOR UPDATE (或 FOR UPDATE OF col)。
FOR UPDATE 不可與 GROUP BY、DISTINCT、ORDER BY、FOR READ ONLY、UNION/EXCEPT/INTERSECT (除 UNION ALL) 同時使用。

六、CASE 表達式替代 Oracle DECODE

Oracle: DECODE(a1, '1', 'n1', '2', 'n2', 'n3')
DB2:

CASE a1
    WHEN '1' THEN 'n1'
    WHEN '2' THEN 'n2'
    ELSE 'n3'
END

七、LOCATE 函數對應 CHARINDEX

LOCATE('y', 'dfdasfay')  -- 回傳 'y' 在字串中的位置 (從 1 起算)，找不到回傳 0

對應 SQL Server CHARINDEX、Oracle INSTR。

八、DAYS 函數計算日期差 (對應 DATEDIFF)

DAYS(DATE('2001-06-05')) - DAYS(DATE('2001-01-01'))  -- 兩日期相差天數

DAYS(date) 回傳從 0001-01-01 起的整數天數，相減即得天數差。

結語

掌握上述技巧能避開 DB2 程式開發的常見坑點。關鍵口訣：CREATE 後用空格、暫存表要 USER TEMP 表空間、單筆查詢用游標、FOR 迴圈游標簡潔但不支援 HOLD、CASE 替代 DECODE、LOCATE/DAYS 對應常用字串日期函數。