Oracle 結果集分頁兩大經典寫法：WITH 子查詢與巢狀 ROWNUM 詳細比較

測試環境

表 test_table(f01, f02, f03)，需分頁查詢，每頁 100 筆，取第 1 頁。

寫法一：WITH 子查詢 (Subquery Factoring / CTE)

WITH part_data AS (
  SELECT ROWNUM rowno, t.*
  FROM test_table t
  WHERE some_condition = 'VALUE'  -- 可加過濾條件
  ORDER BY sort_col               -- 必須在 CTE 內排序
)
SELECT f01, f02, f03
FROM part_data
WHERE rowno BETWEEN 1 AND 100;

特點：結構清晰、可重複引用 part_data、易於維護、支援多重 CTE 串聯。

寫法二：巢狀 ROWNUM 子查詢 (傳統相容寫法)

SELECT f01, f02, f03
FROM (
  SELECT ROWNUM rowno, f01, f02, f03
  FROM test_table
  WHERE some_condition = 'VALUE'
    AND ROWNUM <= 100  -- 中層截取上限
)
WHERE rowno >= 1;      -- 外層篩選下限

特點：無 CTE 支援的舊版本 (8i/9i) 可用、單一 Statement、略難閱讀。

進階：通用分頁函數 (PL/SQL 封裝)

CREATE OR REPLACE FUNCTION get_page(
  p_sql      IN VARCHAR2,  -- 基礎查詢 (不含 ORDER BY)
  p_order_by IN VARCHAR2,  -- 排序欄位
  p_page_no  IN NUMBER,    -- 頁碼 (1-based)
  p_page_sz  IN NUMBER    -- 每頁筆數
) RETURN SYS_REFCURSOR IS
  v_sql   CLOB;
  v_cur   SYS_REFCURSOR;
BEGIN
  v_sql := 'SELECT * FROM (' ||
           '  SELECT a.*, ROWNUM rn FROM (' ||
           '    ' || p_sql || ' ORDER BY ' || p_order_by ||
           '  ) a WHERE ROWNUM <= ' || (p_page_no * p_page_sz) ||
           ') WHERE rn > ' || ((p_page_no - 1) * p_page_sz);

  OPEN v_cur FOR v_sql;
  RETURN v_cur;
END;
/

呼叫範例：

VAR rc REFCURSOR;
EXEC :rc := get_page('SELECT f01,f02,f03 FROM test_table WHERE f01>100', 'f01', 2, 50);
PRINT rc;

效能與適用性比較

大資料量分頁效能關鍵

1. 排序索引：ORDER BY 欄位必須有索引，避免全表排序。
2. FIRST_ROWS(n) Hint：引導最佳化器選擇 Nested Loop + Index Range Scan。
3. 避免 OFFSET 深分頁：第 10000 頁仍需掃描前 9999 頁資料，考慮 Keyset Pagination (游標分頁)：WHERE sort_col > :last_val ORDER BY sort_col FETCH FIRST 100 ROWS ONLY。

結語

現代 Oracle (11g+) 開發**首選 WITH 子查詢** 寫法，結構清晰且最佳化器支援完善。舊版本或需單一 Statement 時使用巢狀 ROWNUM。封裝為 PL/SQL 函數回傳 SYS_REFCURSOR 可統一應用層分頁介面，搭配索引與 Hint 達成高效能分頁。