萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 實現搜尋分頁查詢的標準模式

核心挑戰

Web 無狀態,資料庫游標無法跨請求保持。客戶端游標資源有限,最佳實踐:資料庫直接回傳限定筆數結果集。

ROWNUM 陷阱


-- 錯誤:不會返回任何記錄
SELECT * FROM all_objects WHERE ROWNUM BETWEEN 30 AND 49;

原因:ROWNUM 於行取出/過濾時賦值。第一行 ROWNUM=1 不符合 30-49 被捨棄,第二行成為新的 1,同樣被捨棄,以此類推永無符合行。

正確模式:子查詢先取再過濾


SELECT * FROM (
  SELECT ROWNUM r, all_objects.* FROM all_objects WHERE ROWNUM <= 49
) t WHERE t.r >= 30;

步驟:

  1. 內層 WHERE ROWNUM <= 49 取前 49 筆(含 ROWNUM 賦值)
  2. 外層 WHERE r >= 30 過濾掉前 29 筆

使用繫結變數避免重解析


SELECT * FROM (
  SELECT ROWNUM r, all_objects.* FROM all_objects WHERE ROWNUM <= :max
) t WHERE t.r >= :min;

相同 SQL 文本複用游標,消除重解析開銷。

封裝為預存程序回傳游標


CREATE OR REPLACE PROCEDURE search(p_min INTEGER, p_max INTEGER, p_rowset OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_rowset FOR
  SELECT * FROM (
    SELECT ROWNUM r, all_objects.* FROM all_objects WHERE ROWNUM <= p_max
  ) t WHERE t.r >= p_min;
END;
/

應用層僅接收需求筆數,邏輯全在資料庫端。

排序與 ROWNUM

ROWNUM 於排序後計算,ORDER BY 會改變行順序。規則基礎優化器 (RBO) 會用 ROWNUM 「短路」查詢,滿足條件即返回。

copyright © 萬盛學電腦網 all rights reserved