DB2 純 SQL 預存程序完整範例解析

前言

許多開發人員在尋找 DB2 SQL 預存程序的實戰範例時，往往難以找到包含錯誤處理、動態游標、聯合體（Federation）別名操作、分支與迴圈邏輯的完整代碼。本文分享一個從 Oracle 移植到 DB2 的實際生產環境預存程序，涵蓋帳務數據抽取、任務管理、動態別名創建與清理等核心場景，供大家參考探討。

程式碼結構與核心邏輯

該預存程序 IPD.st_inter_PROF 接收轉檔任務 ID、類型、起始日期及目標表名等參數，透過 OUT 參數返回錯誤碼與訊息。主體區塊 P1 採用結構化錯誤處理：

• 變量聲明：包含狀態追蹤變量 state、動態 SQL 語句變量 stmt/v_sql_str、游標變量 c_bill_task_id 等。
• 異常處理器：EXIT HANDLER FOR SQLEXCEPTION 捕獲運行時錯誤並記錄 SQLCODE；CONTINUE HANDLER FOR NOT FOUND 處理游標結束條件。

主要處理流程

1. 獲取處理週期：從 ipd.transfer_task 讀取 deal_cycle。
2. 類型 7（匯總數據）處理：
   • 動態準備游標查詢 ADMINISTRATOR.bill_task_cycle 關聯 billing_cycle，篩選上一個帳期的任務 ID。
   • 迴圈遍歷每個 bill_task_id：
     • 動態創建別名 ADMINISTRATOR.ACCT_ITEM_BILLINGDAY_<id> 指向遠端聯合體表 bill.cal.acct_item_billingday_<id>。
     • 呼叫輔助程序 ipd.sp_exec_dsql 執行動態 DDL/DML。
     • 更新任務表累計行數 rows_cnt。
     • 錯誤即標記任務失敗並返回。
   • 迴圈結束關閉游標，提交事務。
3. 建立介面表並整合數據：
   • 呼叫 ipd.bi_settle_tablespace 檢查表空間。
   • 動態創建目標介面表 ipd.<in_TRANSFER_name>。
   • 再次打開游標，構建 INSERT ... UNION SELECT 動態 SQL 將多張分區表數據合併插入介面表。
   • 成功則更新任務旗標 deal_flag=2。
4. 清理別名：再次迴圈 DROP NICKNAME 移除臨時創建的聯合體別名。
5. 類型 8（下賬數據）處理：邏輯類似，但直接操作 ADMINISTRATOR.acct_item 表，建表採用 LIKE 子句，插入指定欄位列表。
6. 最終檢核：呼叫 ipd.bi_check 執行後續驗證。

關鍵技術點總結

• 動態 SQL 與 PREPARE/EXECUTE：靈活構建跨分區、跨聯合體的查詢與 DDL。
• 聯合體別名機制：替代 Oracle DB Link，實現透明訪問遠端分區表。
• 狀態變量追蹤：state 記錄當前操作步驟，錯誤訊息精確定位問題環節。
• 模組化錯誤處理：統一透過 sp_exec_dsql 返回 o_err_no，主程序統一判斷流控。
• 事務邊界控制：關鍵步驟後顯式 COMMIT，確保數據一致性。

適用場景與遷移啟示

此範例展示了大型計費系統從 Oracle 遷移至 DB2 時，如何利用 DB2 SQL PL 實現複雜的批次處理邏輯。對於需要跨多個分區表（如按帳期分表）進行數據抽取、匯總、轉檔的場景，該模式極具參考價值。開發者可根據實際表結構調整動態 SQL 構建邏輯，並擴展錯誤碼分類以滿足監控需求。

完整代碼已包含在原文中，建議結合 DB2 官方文檔中 /sqllib/samples 目錄下的範例程式一同研讀，以掌握更多最佳實踐。