您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 純 SQL 預存程序入門：建立臨時表與傳回游標完整範例

編輯：DB2 教學

前言：為什麼需要純 SQL 預存程序？

在 DB2 9.1 環境下開發 BIRT 報表應用時，常遇到需要經過多層巢狀迴圈查詢多張表，最後回傳單一結果集的情況。若在應用層處理邏輯過於複雜，不如直接在資料庫層透過純 SQL 預存程序完成資料整理，再以開啟的游標方式回傳給前端（如 BIRT）直接呈現。網路上雖有許多片段資訊，但缺乏可直接執行的完整範本。本文提供一個經實測可用的模板，供開發者參考與延伸。

核心邏輯與流程說明

範例採用「建立全域暫存表 → 寫入測試資料 → 宣告游標查詢暫存表 → 開啟游標回傳」的四步驟架構。全域暫存表（DGTT）屬於 Session 級別，程序結束自動清除，不影響永久物件，極適合作為中繼運算容器。

完整程式碼範本

CREATE PROCEDURE testPrc()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    -- 1. 宣告全域暫存表（若存在則取代，不記錄日誌以提升效能）
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_HY (
        DM VARCHAR(10),
        MC VARCHAR(10)
    ) WITH REPLACE NOT LOGGED;

    -- 2. 插入測試資料（實務上可替換為複雜的多表 JOIN、遞迴查詢等）
    INSERT INTO SESSION.TMP_HY VALUES ('1', 'A');
    INSERT INTO SESSION.TMP_HY VALUES ('2', 'B');
    INSERT INTO SESSION.TMP_HY VALUES ('3', 'C');

    -- 3. 宣告可回傳給呼叫端的游標
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
        SELECT * FROM SESSION.TMP_HY;

    -- 4. 開啟游標，結果集將傳遞給呼叫者（如 BIRT、JDBC Client）
    OPEN CURSOR1;
END

關鍵語法解析

DYNAMIC RESULT SETS 1

宣告此程序會回傳 1 個結果集。若需回傳多組查詢結果，可調整數字並對應多個 DECLARE CURSOR ... WITH RETURN。

DECLARE GLOBAL TEMPORARY TABLE ... WITH REPLACE NOT LOGGED

WITH REPLACE：若同名暫存表已存在則覆蓋，避免重複執行報錯。
NOT LOGGED：不寫入交易日誌，大幅降低插入大量中繼資料時的 I/O 開銷。
暫存表預設置於 SESSION schema，僅目前連線可見，連線中斷自動消失。

DECLARE CURSOR ... WITH RETURN

WITH RETURN 關鍵字指定該游標為「可回傳給呼叫端」。若省略，游標僅能在程序內部使用。

呼叫與驗證方式

在 DB2 Command Line Processor (CLP) 或圖形化工具（如 Data Studio、DBeaver）中執行：

CALL testPrc()

執行成功會顯示結果集格資料。若使用 JDBC 呼叫，需使用 CallableStatement 並啟用 getResultSet() 依序取得各結果集。

擴充建議：加入輸入/輸出參數

範例為求簡潔未加參數。實務上可依需求加入 IN、OUT、INOUT 參數，例如：

CREATE PROCEDURE testPrc(IN p_dept VARCHAR(10), OUT p_cnt INTEGER)
...
SET p_cnt = (SELECT COUNT(*) FROM SESSION.TMP_HY);

參數用法與 Oracle PL/SQL、高階語言函式簽章概念相似，學習曲線平緩。

DB2 與 Oracle 預存程序/函數的差異補充

DB2 預存程序可透過 DYNAMIC RESULT SETS 回傳多組結果集；Oracle 預存程序傳統上不直接回傳結果集（需用 SYS_REFCURSOR OUT 參數）。
DB2 函數支援標量回傳與資料表函數；Oracle 函數回傳彈性較大，但語法差異較大。

開發工具心得

作者提到早期缺乏類似 Oracle PL/SQL Developer 的強力工具。現今建議使用 IBM Data Studio（官方免費）、DBeaver（開源跨平台）或 DataGrip（JetBrains 付費），皆具備語法提示、偵錯、執行計畫分析等功能，大幅改善開發體驗。