使用腳本加速 DB2 存儲過程開發：模板、生成與部署自動化

存儲過程開發痛點與腳本化價值

DB2 存儲過程 (SQL PL / PL/SQL) 開發常面臨：樣板代碼重複 (錯誤處理、日誌、事務控制)、參數映射繁瑣、部署腳本手工維護、版本升級遷移困難、缺乏單元測試框架。通過腳本化 (Shell/Python/Groovy) + 模板引擎 (Jinja2/Velocity/Mustache) + CI/CD 流水線，可實現「配置驅動生成、一鍵部署、自動測試、版本化管理」。

核心腳本化組件

1. 元數據模型 (JSON/YAML)：定義存儲過程接口、業務邏輯片段、依賴表、權限、測試用例。

   name: "process_order"
   schema: "APP"
   params:
     - {name: "p_order_id", type: "BIGINT", mode: "IN"}
     - {name: "p_status", type: "VARCHAR(20)", mode: "OUT"}
     - {name: "p_msg", type: "VARCHAR(200)", mode: "OUT"}
   logic: |
     -- 業務邏輯片段 (SQL PL)
     DECLARE v_count INT;
     SELECT COUNT(*) INTO v_count FROM orders WHERE id = p_order_id;
     IF v_count = 0 THEN
       SET p_status = 'NOT_FOUND'; SET p_msg = 'Order not found'; RETURN;
     END IF;
     UPDATE orders SET status='PROCESSING' WHERE id = p_order_id;
     -- ... 更多邏輯
     SET p_status = 'SUCCESS'; SET p_msg = 'OK';
   grants: ["APP_ROLE"]
   tests:
     - {input: {p_order_id: 999}, expect: {p_status: "NOT_FOUND"}}
     - {input: {p_order_id: 1001}, expect: {p_status: "SUCCESS"}}

2. 模板引擎 (Jinja2 示例)：標準化結構、錯誤處理、日誌、編譯選項。

   {# template/sp_template.sql.j2 #}
   CREATE OR REPLACE PROCEDURE {{schema}}.{{name}} (
   {% for p in params %}  {{p.mode}} {{p.name}} {{p.type}}{% if not loop.last %},{% endif %}
   {% endfor %}
   )
   LANGUAGE SQL
   SPECIFIC {{schema}}.{{name}}
   NOT DETERMINISTIC
   MODIFIES SQL DATA
   CALLED ON NULL INPUT
   INHERIT SPECIAL REGISTERS
   BEGIN
     DECLARE SQLSTATE CHAR(5);
     DECLARE v_err_msg VARCHAR(1000);
     -- 標準錯誤處理塊
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
     BEGIN
       GET DIAGNOSTICS CONDITION 1 v_err_msg = MESSAGE_TEXT;
       SET {{out_param('p_msg')}} = 'ERROR: ' || v_err_msg;
       SET {{out_param('p_status')}} = 'FAILED';
       RESIGNAL; -- 可選：拋給調用者
     END;
   
     {{logic | indent(2)}}
   END@

3. 生成腳本 (Python)：讀取元數據、渲染模板、輸出 `.sql` 文件、生成部署/回滾/測試腳本。
4. 部署自動化：`db2 -td@ -vf deploy.sql` (部署腳本包含所有生成的 SP、GRANT、COMMENT)。集成到 Jenkins/GitLab CI/CD：代碼推送 -> 渲染 -> 語法檢查 (`db2 -syntax` 單元測試框架 (db2unit / 自研) 運行元數據中定義的測試用例，驗證輸出參數、結果集、副作用 (表變更)。

進階模式：動態 SQL 安全構建

模板中封裝 `EXECUTE IMMEDIATE` 安全構建函數，防止注入：

{% macro safe_exec(sql_template, params) %}
  DECLARE v_stmt VARCHAR(32000);
  SET v_stmt = {{sql_template | tojson}}; -- 模板字符串
  {% for p in params %}
  SET v_stmt = REPLACE(v_stmt, ':{p}', CAST({p} AS VARCHAR(1000)));
  {% endfor %}
  EXECUTE IMMEDIATE v_stmt;
{% endmacro %}

版本管理與遷移策略

• 每個存儲過程對應一個元數據文件 `sp_.yaml`，Git 管理歷史。
• 部署腳本自動生成 `DROP PROCEDURE IF EXISTS` + `CREATE` (或 `ALTER` 保留權限)。
• 數據庫遷移工具 (Flyway/Liquibase) 管理元數據版本表，確保冪等部署。
• 藍綠部署：新版部署到影子 Schema、驗證通過後切換同義詞/權限、舊版保留回滾。

效益量化

總結

將 DB2 存儲過程開發從「手工編寫」轉向「元數據驅動、模板生成、自動化部署測試」，不僅大幅提升效率與質量，更建立了可追溯、可審計、可擴展的工程化體系。團隊可逐步引入：先統一模板、再元數據化、最後接入 CI/CD，平滑演進。

1. 上一篇文章： 理解 DB2 中的列組統計信息：原理、收集與優化效用
2. 下一篇文章： 簡化 DB2 for i5/OS 索引建議：自動化分析與優化實踐

DB2 教學

DB2 臨時表使用全攻略：聲明語法、表空間前置、SESSION 模式、提交/回滾行為與日志選項

臨時表核心特性速覽 特性說明 創建語法DECLARE GL

提升 DB2 查詢效能的開發者實戰技巧：索引、改寫與工具善用

核心工具介紹 Visual Explain / db2exp

Windows 7 64位安裝 MongoDB (ZIP 免安裝版) 圖文詳細教程

Windows 7 64位安裝 MongoDB (ZIP 免

DB2 UDB v8.1 數據庫調優最佳實踐（二）：關鍵配置參數詳解

核心內存與排序配置 SORTHEAP（排序堆大小） SORT