Oracle 分頁查詢最佳實踐：PL/SQL 封裝套件實現通用分組分頁

Oracle 無原生 LIMIT/OFFSET（12c 前），傳統分頁依賴 ROWNUM 多層嵌套。本文提供一套生產級 PL/SQL 封裝套件 P_PAGEWITHGROUP，支援動態表名、條件、分組、排序、欄位選擇，並返回總筆數與當前頁游標，適合框架整合與報表開發。

一、套件規格

CREATE OR REPLACE PACKAGE p_pagewithgroup IS
  TYPE type_cur IS REF CURSOR;  -- 返回游標類型

  PROCEDURE UP_GetRecordWithGroupByPage(
    tblName       IN  VARCHAR2,   -- 表名
    PageSize      IN  NUMBER,     -- 頁大小
    PageIndex     IN  NUMBER,     -- 當前頁 (1-based)
    IsReCount     OUT NUMBER,     -- 返回總筆數
    strWhere      IN  VARCHAR2,   -- WHERE 條件 (不含 WHERE 關鍵字)
    strColums     IN  VARCHAR2,   -- 內層查詢欄位 (含分組欄位)
    strSelectColums IN VARCHAR2,  -- 外層輸出欄位
    strGroup      IN  VARCHAR2,   -- GROUP BY 子句 (不含 GROUP BY)
    strOrder      IN  VARCHAR2,   -- ORDER BY 子句 (不含 ORDER BY)
    v_cur         OUT type_cur    -- 返回游標
  );
END p_pagewithgroup;

二、套件主體實現邏輯

CREATE OR REPLACE PACKAGE BODY p_pagewithgroup IS
  PROCEDURE UP_GetRecordWithGroupByPage(...) AS
    v_sql       VARCHAR2(8000);
    v_count     NUMBER;
    v_row_start NUMBER;
    v_row_end   NUMBER;
  BEGIN
    -- 1. 計算總筆數 (支援分組)
    v_sql := 'SELECT COUNT(1) FROM ' || tblName;
    IF strWhere IS NOT NULL AND strWhere <> '' THEN
      v_sql := v_sql || ' WHERE ' || strWhere;
    END IF;
    IF strGroup IS NOT NULL AND strGroup <> '' THEN
      v_sql := v_sql || ' GROUP BY ' || strGroup;
    END IF;
    EXECUTE IMMEDIATE v_sql INTO v_count;
    IsReCount := v_count;

    -- 2. 分頁查詢 (ROWNUM 雙層嵌套)
    v_row_start := (PageIndex - 1) * PageSize + 1;
    v_row_end   := PageIndex * PageSize;

    v_sql := 'SELECT ' || strSelectColums || 
             ' FROM (SELECT t.*, ROWNUM AS rn FROM (' ||
             '   SELECT ' || strColums || ' FROM ' || tblName;
    IF strWhere IS NOT NULL AND strWhere <> '' THEN
      v_sql := v_sql || ' WHERE ' || strWhere;
    END IF;
    IF strGroup IS NOT NULL AND strGroup <> '' THEN
      v_sql := v_sql || ' GROUP BY ' || strGroup;
    END IF;
    IF strOrder IS NOT NULL AND strOrder <> '' THEN
      v_sql := v_sql || ' ORDER BY ' || strOrder;
    END IF;
    v_sql := v_sql || ') t) WHERE rn BETWEEN ' || v_row_start || ' AND ' || v_row_end;

    OPEN v_cur FOR v_sql;
  END;
END;

三、呼叫測試範例

DECLARE
  v_count NUMBER;
  v_cur   p_pagewithgroup.type_cur;
  v_rec   T_DIVISION%ROWTYPE;
BEGIN
  p_pagewithgroup.UP_GetRecordWithGroupByPage(
    'T_DIVISION',  -- 表名
    5,             -- 每頁 5 筆
    1,             -- 第 1 頁
    v_count,       -- 總筆數輸出
    '1=1',         -- 條件
    '*',           -- 內層欄位
    '*',           -- 輸出欄位
    NULL,          -- 無分組
    NULL,          -- 無排序
    v_cur          -- 返回游標
  );
  DBMS_OUTPUT.PUT_LINE('總筆數: ' || v_count);
  
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.division_name); -- 視實際表結構調整
  END LOOP;
  CLOSE v_cur;
END;

四、關鍵設計考量

五、進階擴展建議

• 增加 strHint 參數支援 Hint 注入
• 支援 BIND VARIABLES 替代字串拼接，提升解析效能
• 整合 DBMS_SQL 實現更靈活的動態欄位描述
• 加入分頁緩存機制（如 Redis 缓存總筆數）

此套件模式在 Oracle 10g/11g 專案中廣泛使用，封裝了分頁的複雜邏輯，提供統一介面，是遷移至 12c+ 前的最佳過渡方案。