DB2 預存程序建立與刪除實戰範例 (含會話臨時表)

業務背景

規則 20：商戶日交易金額與前周/月日均變動幅度超限檢測。輸入參數 IN_TXN_DT (DATE, 格式 yyyy-MM-dd)。

刪除舊版預存程序

DROP PROCEDURE MI_PROJECT_RULE20;

建立新版預存程序 (核心框架)

CREATE PROCEDURE MI_PROJECT_RULE20 (
  IN IN_TXN_DT DATE
)
LANGUAGE SQL
BEGIN
  -- 1. 清除當日明細資料 (防重跑重複)
  DELETE FROM T05_DUBIOUS_TXN_SUM
  WHERE D_TXN_STAT_ID IN (
    SELECT D_TXN_STAT_ID FROM T05_DUBIOUS_TXN_STAT
    WHERE MI_ID = 20 AND TXN_DT = IN_TXN_DT
  );
  COMMIT;

  -- 2. 清除當日統計資料
  DELETE FROM T05_DUBIOUS_TXN_STAT
  WHERE MI_ID = 20 AND TXN_DT = IN_TXN_DT;
  COMMIT;

  -- 3. 建立會話臨時表 (SESSION Schema, 連線結束自動清理)
  DECLARE GLOBAL TEMPORARY TABLE SESSION.T05_TMP_MI (
    -- 欄位定義...
  ) WITH REPLACE ON COMMIT PRESERVE ROWS;  -- 保留資料跨 COMMIT

  -- 後續邏輯：填充臨時表 → 計算指標 → 寫入統計表 → 提交
  -- ...
END

關鍵技術點解析

1. 雙重刪除 + COMMIT：先刪明細再刪統計，中間 COMMIT 釋放鎖、縮短事務，避免鎖升級與日誌暴漲。
2. DECLARE GLOBAL TEMPORARY TABLE (DGTT)：
   • 存在於 SESSION Schema，僅當前連線可見
   • WITH REPLACE：同名表存在時自動覆蓋，適合重跑
   • ON COMMIT PRESERVE ROWS：COMMIT 後保留資料 (預設 DELETE ROWS)
   • 需預先建立用戶臨時表空間 (USER TEMPORARY TABLESPACE)
3. 參數型別 DATE：直接用 DATE 型別，DB2 自動處理字串轉換 (ISO 格式)。

# 部署
db2 -td@ -f create_rule20.sql

# 驗證
db2 "CALL MI_PROJECT_RULE20('2024-01-15')"
db2 "SELECT * FROM T05_DUBIOUS_TXN_STAT WHERE MI_ID=20 AND TXN_DT='2024-01-15'"