DB2 SQL Procedure 動態游標完整範例：PREPARE/OPEN/FETCH 範式

動態游標適用場景

• 查詢條件/排序/表名在執行期才確定
• 避免為每種組合寫靜態游標
• 需在 Procedure 內動態組裝 SQL 字串並回傳結果集

核心語法四步驟

1. 宣告 Statement Handle：DECLARE stmt1 STATEMENT;
2. 宣告游標綁定 Statement：DECLARE c1 CURSOR FOR stmt1;
3. PREPARE 組裝 SQL 字串：PREPARE stmt1 FROM :sql_str;
4. OPEN/FETCH/CLOSE：標準游標流程

完整範例

CREATE PROCEDURE DATA_WTPTEST(
  IN IN_TASKID_TIMESTAMP VARCHAR(30),
  OUT O_ERR_NO INT,
  OUT O_ERR_MSG VARCHAR(1024)
)
LANGUAGE SQL
P1: BEGIN ATOMIC
  -- 變數宣告
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE SQL_STMT VARCHAR(1024) DEFAULT '';
  DECLARE R_CODE INT DEFAULT 0;
  DECLARE STATE VARCHAR(1024) DEFAULT 'AAA';
  DECLARE AT_END INT DEFAULT 0;
  DECLARE T_DESTNETID INT DEFAULT 0;
  DECLARE T_RECVID VARCHAR(30) DEFAULT '';
  DECLARE SP_NAME VARCHAR(50) DEFAULT 'DATA_WTPTEST';

  -- 動態游標宣告
  DECLARE STMT1 STATEMENT;
  DECLARE C1 CURSOR FOR STMT1;

  -- 異常處理
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS EXCEPTION 1
      O_ERR_NO = DB2_RETURNED_SQLCODE,
      O_ERR_MSG = MESSAGE_TEXT;
    ROLLBACK;
  END;

  -- 動態構建查詢
  SET SQL_STMT = 'SELECT DESTNETID, RECVID FROM ROUTE_TABLE '
               || 'WHERE TASK_ID = ? ORDER BY PRIORITY';
  PREPARE STMT1 FROM SQL_STMT;
  OPEN C1 USING IN_TASKID_TIMESTAMP;

  FETCH_LOOP: LOOP
    FETCH C1 INTO T_DESTNETID, T_RECVID;
    IF AT_END = 1 THEN LEAVE FETCH_LOOP; END IF;
    -- 業務邏輯處理...
    SET STATE = 'PROCESSING ' || T_RECVID;
  END LOOP FETCH_LOOP;

  CLOSE C1;
  SET O_ERR_NO = 0;
  SET O_ERR_MSG = 'SUCCESS';
END P1

關鍵細節

• ATOMIC：整個 Procedure 為單一事務，錯誤自動回滾
• GET DIAGNOSTICS：標準化捕捉 SQLCODE/SQLSTATE/訊息
• USING 子句綁定參數，防止 SQL 注入
• 游標預設 WITHOUT HOLD，Commit 後自動關閉；需跨 Commit 保持加 WITH HOLD

效能提示

• 動態 SQL 每次執行皆 PREPARE，高頻呼叫考慮 CONCENTRATE STATEMENTS ON 或改靜態 SQL
• 確保動態 SQL 的 WHERE 欄位有索引，避免全表掃描
• 大結果集啟用 BLOCKING（CLI/ODBC/JDBC 端設定）減少網路往返