DB2 通用分頁預存過程實作：動態 SQL 構建與游標返回完整範例

分頁查詢是應用系統高頻需求。DB2 無內建 LIMIT/OFFSET (舊版本)，需透過 FETCH FIRST n ROWS ONLY 配合子查詢實現。本文提供一個生產可用的通用分頁預存過程 USP_A_FY，支援動態表名、欄位、排序、條件與頁碼/頁大小參數，採用動態 SQL + 游標返回模式。

預存過程介面定義

CREATE PROCEDURE KYJT.USP_A_FY (
  IN PTBLNAME    VARCHAR(1000),  -- 表名
  IN PSHOWNAME   VARCHAR(1000),  -- 顯示欄位清單 (含主鍵)
  IN PFLDNAME    VARCHAR(1000),  -- 主鍵欄位 (唯一、非空)
  IN PPAGESIZE   INTEGER,        -- 頁大小 (0=返回所有，預設 1000萬)
  IN PPAGEINDEX  INTEGER,        -- 頁碼 (1-based，0 視同 1)
  IN PORDERTYPE  INTEGER,        -- 排序類型 (0=升序，非 0=降序)
  IN PSTRWHERE   VARCHAR(1000)   -- WHERE 條件 (不含 WHERE 關鍵字)
)
MODIFIES SQL DATA
DETERMINISTIC
LANGUAGE SQL

核心邏輯解析

1. 參數防禦性處理

IF PPAGEINDEX = 0 THEN SET PPAGEINDEX = 1; END IF;
IF PPAGESIZE  = 0 THEN SET PPAGESIZE  = 10000000; END IF;

2. 排序方向動態構建

IF PORDERTYPE <> 0 THEN
  SET C_STRTMP  = '< (SELECT MIN';   -- 降序用 MIN 邏輯
  SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' DESC';
ELSE
  SET C_STRTMP  = '> (SELECT MAX';   -- 升序用 MAX 邏輯
  SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' ASC';
END IF;

3. 分頁 SQL 模板 (游標鍵值法 / Key-seek Pagination)

核心思想：先按排序取前 (PageIndex-1)*PageSize 行的主鍵極值，再以此為邊界取當前頁資料。避免 OFFSET 深分頁掃描大量無效行。

-- 無 WHERE 條件
SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM ' || PTBLNAME ||
  ' WHERE ' || PFLDNAME || ' ' || C_STRTMP || '( ' ||
  'TBLTMP.TTT ) FROM (SELECT ' || PFLDNAME || ' AS TTT FROM ' || PTBLNAME ||
  ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR((PPAGEINDEX-1)*PPAGESIZE) || ' ROWS ONLY) AS TBLTMP)' ||
  C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';

-- 有 WHERE 條件 (子查詢與外層雙重過濾)
SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM ' || PTBLNAME ||
  ' WHERE ' || PFLDNAME || ' ' || C_STRTMP || '( ' ||
  'TBLTMP.TTT ) FROM (SELECT ' || PFLDNAME || ' AS TTT FROM ' || PTBLNAME ||
  ' WHERE ' || PSTRWHERE || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR((PPAGEINDEX-1)*PPAGESIZE) || ' ROWS ONLY) AS TBLTMP)' ||
  ' AND ' || PSTRWHERE || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';

4. 首頁優化 (PageIndex=1 直接取前 N 行)

IF PPAGEINDEX = 1 THEN
  SET C_STRTMP = '';
  IF PSTRWHERE <> '' THEN SET C_STRTMP = ' WHERE ' || PSTRWHERE; END IF;
  SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM ' || PTBLNAME ||
    C_STRTMP || ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';
END IF;

5. 動態準備與游標返回

DECLARE bill_task CURSOR WITH RETURN TO CALLER FOR S1;
PREPARE S1 FROM C_STRSQL;
OPEN bill_task;

調用範例

-- 第 3 頁，每頁 20 行，按 ID 降序，條件 STATUS='A'
CALL KYJT.USP_A_FY(
  'ORDERS',           -- PTBLNAME
  'ID, CUST_ID, AMT, STATUS, CREATE_TIME', -- PSHOWNAME
  'ID',               -- PFLDNAME (PK)
  20,                 -- PPAGESIZE
  3,                  -- PPAGEINDEX
  1,                  -- PORDERTYPE (降序)
  "STATUS = 'A'"      -- PSTRWHERE
);

優化建議與避坑指南

• 注入風險：參數直接拼接 SQL，調用端務必嚴格驗證表名/欄位名白名單，條件參數建議改用參數標記 (?) + PREPARE ... USING。
• 深分頁效能：游標鍵值法依賴主鍵索引有序掃描，百萬級資料第 1 萬頁仍需掃描前 20 萬行。極深分頁建議改用 Keyset Pagination (記錄上頁最大/最小主鍵，下頁直接 WHERE id > last_id)。
• 統計資訊：確保主鍵與 WHERE 條件欄位有最新 RUNSTATS，否則優化器可能選錯存取路徑。
• DB2 11.1+ 原生支援 OFFSET ... ROWS FETCH FIRST ... ROWS ONLY，新專案優先使用標準語法。