DB2 通用分頁存儲過程：動態 SQL 實現靈活分頁查詢

設計目標

提供單一存儲過程支援任意表/視圖/聯結查詢的分頁，輸出分頁元資料（總行數、總頁數、上下頁標誌），供前端直接綁定。

參數定義

核心邏輯

1. 動態構建 SELECT COUNT(*) ... 獲取總行數
2. 計算總頁數、修正當前頁碼、推算起止行號
3. 使用 ROW_NUMBER() OVER (ORDER BY ...) 產生行號
4. 外層過濾 ROWNUM BETWEEN start AND end 取得當前頁資料
5. 以 RESULT SETS 1 返回游標供調用者獲取

完整存儲過程代碼

CREATE PROCEDURE SALES.DB2PAGINATION (
    IN ITBNAME VARCHAR(2000),
    IN ISHOWFIELD VARCHAR(1000),
    IN IJOIN VARCHAR(1000),
    IN IWHERE VARCHAR(2000),
    IN IORDER VARCHAR(100),
    IN IPAGESIZE INTEGER,
    INOUT IOCURRENTPAGEIX INTEGER,
    OUT OPAGESTARTROW INTEGER,
    OUT OPAGEENDROW INTEGER,
    OUT OTOTALROWS INTEGER,
    OUT OHASPREVIOUSPAGE INTEGER,
    OUT OHASNEXTPAGE INTEGER,
    OUT OTOTALPAGES INTEGER,
    OUT OERROR VARCHAR(100)
)
RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
BEGIN
    DECLARE STRSQL VARCHAR(6000);
    DECLARE RESULT CURSOR WITH RETURN TO CALLER FOR S2;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET OERROR = 'error!';
    END;

    -- 組裝 WHERE / ORDER BY / JOIN
    IF (IWHERE <> '') THEN SET IWHERE = ' WHERE ' || IWHERE; END IF;
    IF (IORDER <> '') THEN SET IORDER = ' ORDER BY ' || IORDER; END IF;
    IF (IJOIN <> '') THEN SET IJOIN = ' ' || IJOIN; END IF;

    -- 1. 總行數
    SET STRSQL = 'SELECT COUNT(*) FROM ' || ITBNAME || IJOIN || IWHERE;
    PREPARE S2 FROM STRSQL;
    OPEN RESULT;
    FETCH RESULT INTO OTOTALROWS;
    CLOSE RESULT;

    -- 2. 頁面計算
    IF (IPAGESIZE = 0) THEN SET IPAGESIZE = 1000000; END IF;
    SET OTOTALPAGES = (OTOTALROWS - 1) / IPAGESIZE + 1;
    IF (IOCURRENTPAGEIX < 1) THEN SET IOCURRENTPAGEIX = 1;
    ELSEIF (IOCURRENTPAGEIX > OTOTALPAGES) THEN SET IOCURRENTPAGEIX = OTOTALPAGES; END IF;
    SET OPAGESTARTROW = IPAGESIZE * (IOCURRENTPAGEIX - 1) + 1;
    IF (IOCURRENTPAGEIX = OTOTALPAGES) THEN SET OPAGEENDROW = OTOTALROWS;
    ELSE SET OPAGEENDROW = IPAGESIZE * IOCURRENTPAGEIX; END IF;
    SET OHASPREVIOUSPAGE = CASE WHEN IOCURRENTPAGEIX > 1 THEN 1 ELSE 0 END;
    SET OHASNEXTPAGE = CASE WHEN IOCURRENTPAGEIX < OTOTALPAGES THEN 1 ELSE 0 END;

    -- 3. 分頁查詢
    SET STRSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(' || IORDER || ') AS ROWNUM, '
               || ISHOWFIELD || ' FROM ' || ITBNAME || IJOIN || IWHERE
               || ') AS T WHERE ROWNUM BETWEEN ' || RTRIM(CHAR(OPAGESTARTROW))
               || ' AND ' || RTRIM(CHAR(OPAGEENDROW));
    PREPARE S2 FROM STRSQL;
    OPEN RESULT;
END

調用範例

CALL SALES.DB2PAGINATION(
    'ORDERS O JOIN CUSTOMERS C ON O.CUST_ID = C.ID',
    'O.ORDER_ID, O.ORDER_DATE, C.NAME, O.TOTAL_AMT',
    '', -- 無額外 JOIN
    'O.ORDER_DATE BETWEEN ''2023-01-01'' AND ''2023-12-31''',
    'O.ORDER_DATE DESC',
    20,  -- 頁大小
    1,   -- 第 1 頁
    ?, ?, ?, ?, ?, ?, ?  -- 輸出參數
);

注意事項

• 動態 SQL 需防範注入，調用端務必驗證/清洗輸入參數
• ROW_NUMBER() 需 DB2 9.7+ / LUW 9.5+ 支援
• 大表分頁建議在 ORDER BY 欄位建立索引，避免全表排序
• 極大結果集（百萬行以上）考慮 Keyset 分頁（游標分頁）替代 Offset 分頁