Oracle 分頁查詢最佳實踐：ROWNUM 雙層嵌套、綁定變數與存儲過程封裝

分頁查詢核心挑戰

• Web 無狀態，無法長期持有游標
• Oracle 游標不支援向後捲動
• 使用者可能隨機跳頁、倒序瀏覽
• 需由資料庫層高效回傳指定區間資料列

ROWNUM 機制與常見誤區

ROWNUM 在資料列被**擷取/過濾時**動態賦值。直接寫 WHERE ROWNUM BETWEEN 30 AND 49 永遠回傳空集合，因第一列 ROWNUM=1 不滿足 ≥30 被丟棄，後續列遞補為 1 同理被丟棄。

正確雙層嵌套模式

-- 步驟 1：先取前 N 列
SELECT * FROM all_objects WHERE ROWNUM <= 49;

-- 步驟 2：外層過濾起始位置
SELECT * FROM (
  SELECT ROWNUM rn, a.* FROM all_objects a WHERE ROWNUM <= 49
) WHERE rn >= 30;

使用綁定變數避免硬解析

SELECT * FROM (
  SELECT ROWNUM rn, a.* FROM all_objects a WHERE ROWNUM <= :max_row
) WHERE rn >= :min_row;

相同 SQL 文本、不同綁定值可共享游標，消除重複解析開銷。

封裝為存儲過程回傳 REF CURSOR

CREATE OR REPLACE PROCEDURE search_page (
  p_min   IN  NUMBER,
  p_max   IN  NUMBER,
  p_rc    OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_rc FOR
    SELECT * FROM (
      SELECT ROWNUM rn, a.* FROM all_objects a WHERE ROWNUM <= p_max
    ) WHERE rn >= p_min;
END search_page;
/

應用層僅需呼叫存儲過程，直接獲取所需頁面資料集，無需在中介層過濾。

ORDER BY 對 ROWNUM 的影響

ROWNUM 在 ORDER BY **之後** 計算，因此排序會改變分頁結果順序。基於規則的優化器（RBO）會利用 ROWNUM 短路查詢，滿足條件即停止掃描。

12c/19c 更簡潔的 OFFSET/FETCH 語法（補充）

SELECT * FROM all_objects
ORDER BY object_id
OFFSET 29 ROWS FETCH NEXT 20 ROWS ONLY;

Oracle 12c 起支援 ANSI 標準分頁語法，碼更清晰、優化器支援更佳，建議新專案優先採用。

效能比較與建議

總結

Oracle 分頁核心在於「先限制上限、再過濾下限」。生產環境務必使用綁定變數並封裝為存儲過程，12c 以上版本請直接採用 OFFSET/FETCH 簡化開發。