DB2 通用分頁存儲過程實作與原理解析

存儲過程介面設計

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）
  IN PORDERTYPE  INTEGER,        -- 排序：非 0=降序，0=升序
  IN PSTRWHERE   VARCHAR(1000)   -- WHERE 條件（不含 WHERE 關鍵字）
)
MODIFIES SQL DATA
DETERMINISTIC
LANGUAGE SQL

核心演算法：動態 SQL 雙層巢狀查詢

採用鍵值範圍定位 + FETCH FIRST 技巧，避免傳統 OFFSET 掃描大量前置資料列的效能問題。

邏輯流程

1. 參數預處理：頁碼 < 1 視為 1；頁大小 = 0 視為 10,000,000。
2. 依 PORDERTYPE 決定排序方向與比較運算子：
   • 升序：WHERE pk > (SELECT MAX(pk) FROM (SELECT pk FROM tbl ORDER BY pk ASC FETCH FIRST (page-1)*size ROWS ONLY) AS tmp)
   • 降序：WHERE pk < (SELECT MIN(pk) FROM (SELECT pk FROM tbl ORDER BY pk DESC FETCH FIRST (page-1)*size ROWS ONLY) AS tmp)
3. 第 1 頁特殊處理：直接 ORDER BY ... FETCH FIRST size ROWS ONLY，無需巢狀子查詢。
4. 動態組裝 SQL 字串 → PREPARE → OPEN CURSOR WITH RETURN TO CALLER 回傳結果集。

關鍵程式碼片段解析

-- 排序與比較邏輯建構
IF (PORDERTYPE <> 0) THEN
  SET C_STRTMP = '<(SELECT MIN';
  SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' DESC';
ELSE
  SET C_STRTMP = '>(SELECT MAX';
  SET C_STRORDER = ' ORDER BY ' || PFLDNAME || ' ASC';
END IF;

-- 一般頁面動態 SQL
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';

-- 第 1 頁優化路徑
IF (PPAGEINDEX = 1) THEN
  SET C_STRSQL = 'SELECT ' || PSHOWNAME || ' FROM ' || PTBLNAME || 
    (CASE WHEN PSTRWHERE <> '' THEN ' WHERE ' || PSTRWHERE ELSE '' END) || 
    ' ' || C_STRORDER || ' FETCH FIRST ' || CHAR(PPAGESIZE) || ' ROWS ONLY';
END IF;

效能特性分析

改良建議（生產環境就緒版）

• 防注入：使用 DBMS_ASSERT 驗證識別字元，或改寫為參數化 PREPARE（DB2 9.7+ 支援）。
• 型別安全：PPAGESIZE/PPAGEINDEX 建議加上 CHECK (val > 0) 邏輯。
• 效能監控：加入執行時間記錄、執行計畫快照輸出參數。
• 相容性：DB2 11.1+ 可直接使用 OFFSET ... LIMIT / FETCH FIRST ... ROWS ONLY 標準語法，簡化邏輯。

適用場景

• 舊版 DB2 (V8/V9) 應用快速增加分頁功能。
• 報表工具、通用查詢介面需動態支援多表分頁。
• 主鍵為單欄數值/字串且有索引的表。

此存儲過程展示了 DB2 SQL PL 動態 SQL 與游標回傳的經典組合，理解其原理有助於在現代版本中設計更優雅的分頁方案。