DB2 程式設計實用技巧大全（一）：存儲過程、游標、臨時表與常用函數

DB2 程式開發精華筆記

彙整實戰經驗，涵蓋存儲過程建立、臨時表使用、游標操作、常用函數替代方案、類型轉換、錯誤處理、效能調優、系統管理等核心主題。

核心技巧目錄

1. 存儲過程建立

• CREATE 後禁用 TAB 鍵：只能用空格，否則編譯失敗。

2. 臨時表使用

• 僅能建立於 USER TEMPORARY TABLESPACE，無此表空間則無法建立。
• DB2 臨時表在單一 Session 內有效，多線程程式難以控制，謹慎使用。
• 建議加 WITH REPLACE 選項，避免重複建立報錯。

3. 取得指定前幾筆記錄

-- 正確語法
select * from tb_market_code fetch first 1 rows only;

-- 錯誤：SELECT INTO 不支援 FETCH FIRST
-- 替代方案：使用游標
declare v_market_code char(1);
declare cursor1 cursor for select market_code from tb_market_code fetch first 1 rows only for update;
open cursor1;
fetch cursor1 into v_market_code;
close cursor1;

4. 游標操作

• COMMIT/ROLLBACK 影響：未加 WITH HOLD 選項時，提交或回滾會關閉游標。
• 兩種定義方式：
  • 傳統方式：DECLARE CONTINUE HANDLER FOR NOT FOUND 搭配 WHILE 迴圈，支援 WITH HOLD，適合迴圈中需 COMMIT/ROLLBACK 場景。
  • FOR 迴圈方式：FOR loopcs1 AS cursor1 CURSOR FOR ... DO ... END FOR; 簡潔不需 OPEN/FETCH/CLOSE，但不支援 WITH HOLD，迴圈內不可 COMMIT/ROLLBACK。
• 修改游標當前列：UPDATE tb_market_code SET market_code='0' WHERE CURRENT OF cursor1; 需宣告 FOR UPDATE 游標。不可與 GROUP BY、DISTINCT、ORDER BY、FOR READ ONLY、UNION/EXCEPT/INTERSECT (UNION ALL 除外) 共用。

5. 常用函數替代方案

• DECODE 轉碼：使用 CASE WHEN ... THEN ... ELSE ... END。
• CHARINDEX 字元位置：使用 LOCATE('y', 'dfdasfay')。
• DATEDIFF 日期差天數：DAYS(DATE('2001-06-05')) - DAYS(DATE('2001-04-01'))，DAYS 回傳自 0001-01-01 起算天數。

6. UDF 撰寫範例

C 語言 UDF 參考 sqllib\samples\cli\udfsrv.c。

7. IDENTITY 自動遞增欄位

CREATE TABLE test (
  t1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 500, INCREMENT BY 1),
  t2 CHAR(1)
);

單表僅允許一個 IDENTITY 欄位。

8. 空值防護

使用 COALESCE 回傳第一個非空運算式，支援多參數，優於 Oracle ISNULL（僅兩參數）。

9. 取得影響列數

GET DIAGNOSTICS v_count = ROW_COUNT;

僅對 UPDATE/INSERT/DELETE 有效，對 SELECT INTO 無效。

10. 存儲過程回傳結果集

宣告 RESULT SETS n，游標加 WITH RETURN TO CALLER，OPEN 後由呼叫端 ASSOCIATE RESULT SET LOCATOR、ALLOCATE CURSOR 取得。

更多進階主題（類型轉換、存儲過程互呼、C 存儲過程參數、FENCED/UNFENCED、錯誤處理、IMPORT/VALUES 用法、隔離等級指定、ATOMIC/NOT ATOMIC、大量資料導入、複雜 SQL 優化、SQL SP vs C SP、HASH JOIN/RR_TO_RS、避免 COUNT(*)/EXISTS、系統管理指令）請見後續章節。