Oracle 分頁查詢最佳實踐：ROWNUM 子查詢 + 綁定變數 + 存儲過程封裝

核心難點

Web 無狀態環境下不可長期持有游標；ROWNUM 僅在行被取出時分配，直接 WHERE ROWNUM BETWEEN 30 AND 49 永遠無資料。

標準分頁寫法（兩層子查詢）

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

綁定變數優化

使用 :min_row, :max_row 綁定變數，使相同 SQL 文本共享游標，避免每次硬解析。

存儲過程封裝（回傳 REF CURSOR）

CREATE OR REPLACE PROCEDURE search_page (
  p_min  IN INTEGER,
  p_max  IN INTEGER,
  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;
/

進階提示

• ROWNUM 在 ORDER BY 之後計算，若需排序分頁請在內層查詢加 ORDER BY。
• 基於規則優化器 (RBO) 會在滿足 ROWNUM 條件時短路停止，適合首頁顯示；CBO 需配合 FIRST_ROWS(n) Hint。
• 12c+ 可直接使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 標準語法。