DB2 通用分頁預存程序完整實現 (動態 SQL + 結果集輸出)

介面設計

CREATE PROCEDURE SALES.DB2PAGINATION (
  IN  ITBNAME         VARCHAR(2000),  -- 表名 (含 Schema)
  IN  ISHOWFIELD      VARCHAR(1000),  -- 顯示欄位清單
  IN  IJOIN           VARCHAR(1000),  -- JOIN 子句 (含 JOIN 關鍵字)
  IN  IWHERE          VARCHAR(2000),  -- WHERE 條件 (不含 WHERE 關鍵字)
  IN  IORDER          VARCHAR(100),   -- ORDER BY 條件 (不含 ORDER BY)
  IN  IPAGESIZE       INTEGER,        -- 頁大小 (0=全部, 內部轉 1,000,000)
  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;

  -- 1. 組裝 WHERE / ORDER / 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;

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

  -- 3. 頁碼修正與邊界計算
  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;

  -- 4. 動態分頁查詢 (ROW_NUMBER() OVER 窗口函數)
  SET STRSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(' || IORDER || ') AS ROWNUM, '
             || ISHOWFIELD || ' FROM ' || ITBNAME || IJOIN || IWHERE
             || ') AS TEMP WHERE ROWNUM BETWEEN '
             || RTRIM(CHAR(OPAGESTARTROW)) || ' AND ' || RTRIM(CHAR(OPAGEENDROW));

  PREPARE S2 FROM STRSQL;
  OPEN RESULT;
END

調用範例

-- 第一頁，每頁 20 筆
CALL SALES.DB2PAGINATION(
  'SALES.ORDERS',           -- 表名
  'ORDER_ID, CUST_ID, AMT', -- 顯示欄位
  'INNER JOIN CUSTOMERS C ON O.CUST_ID=C.CUST_ID', -- JOIN
  'O.ORDER_DATE > ''2024-01-01''', -- WHERE
  'O.ORDER_DATE DESC',      -- ORDER BY
  20,                       -- 頁大小
  1,                        -- 第 1 頁
  ?, ?, ?, ?, ?, ?, ?, ?    -- 輸出參數
);

設計亮點與擴展建議

• 完全動態：表名、欄位、JOIN、WHERE、ORDER 全參數化，一個預存程序服務所有分頁需求。
• ROW_NUMBER() 窗口函數：DB2 高效分頁標準寫法，避免舊版 FETCH FIRST ... ROWS ONLY 配合游標的低效。
• 結果集直接返回：CURSOR WITH RETURN TO CALLER，Java/.NET 直接 getResultSet() 讀取。
• 擴展方向：加入 TOTAL_COUNT 快取 (Redis)、支援 Keyset Pagination (游標式分頁) 解決深分頁效能問題。