Oracle 分頁存儲過程深度重構：從巢狀 ROWNUM 到現代 OFFSET/FETCH 的演進之路

原始碼痛點分析

原存儲過程 PROC6338196642095312503719 採用三層巢狀 ROWNUM 實現分頁，存在嚴重問題：

• 可讀性極差，維護成本極高。
• 動態條件拼接邏輯錯誤（WHERE 1=1 AND ... OR ... 運算子優先級導致條件失效）。
• 游標參數命名使用中文，違反命名規範，跨工具易亂碼。
• 缺乏 ORDER BY 確定性排序，分頁結果不穩定。

重構版：參數英文化、條件邏輯修正、可讀性重寫

CREATE OR REPLACE PROCEDURE proc_news_page (
    p_title       IN  VARCHAR2,
    p_content     IN  VARCHAR2,
    p_pub_date    IN  VARCHAR2,
    p_page_no     IN  NUMBER,
    p_page_size   IN  NUMBER,
    p_is_next     IN  NUMBER,
    p_news_id     IN  VARCHAR2,
    p_cur_page    OUT NUMBER,
    p_total_rows  OUT NUMBER,
    p_total_pages OUT NUMBER,
    p_result      OUT SYS_REFCURSOR
) AS
    v_offset   NUMBER;
    v_sql      CLOB;
    v_where    CLOB;
BEGIN
    -- 1. 計算總筆數
    v_where := ' WHERE 1=1 ';
    IF p_title IS NOT NULL THEN
        v_where := v_where || ' AND v_xwzt LIKE ''%'' || :title || ''%'' ';
    END IF;
    IF p_content IS NOT NULL THEN
        v_where := v_where || ' AND v_xwnr LIKE ''%'' || :content || ''%'' ';
    END IF;
    IF p_pub_date IS NOT NULL THEN
        v_where := v_where || ' AND d_fbsj = TO_DATE(:pub_date, ''YYYY-MM-DD'') ';
    END IF;
    IF p_news_id IS NOT NULL THEN
        v_where := v_where || ' AND i_id = :news_id ';
    END IF;

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM xtnews' || v_where
        INTO p_total_rows
        USING p_title, p_content, p_pub_date, p_news_id;

    p_total_pages := CEIL(p_total_rows / p_page_size);

    -- 2. 處理翻頁邏輯
    IF p_page_no IS NOT NULL THEN
        IF p_is_next = 1 THEN
            p_cur_page := LEAST(p_page_no + 1, p_total_pages);
        ELSE
            p_cur_page := GREATEST(p_page_no - 1, 1);
        END IF;
    ELSE
        p_cur_page := 1;
    END IF;

    v_offset := (p_cur_page - 1) * p_page_size;

    -- 3. 12c+ 標準分頁語法
    v_sql := q'[
        SELECT i_id, v_xwzt, v_xwnr, d_fbsj, d_yxsj, v_fbbm
        FROM xtnews
    ]' || v_where || q'[
        ORDER BY d_fbsj DESC, i_id
        OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY
    ]';

    OPEN p_result FOR v_sql
        USING p_title, p_content, p_pub_date, p_news_id, v_offset, p_page_size;
END;
/

版本演進對比

關鍵優化細節

1. 綁定變數：所有動態值用 USING 綁定，防 SQL 注入、促進計劃重用。
2. 確定性排序：ORDER BY d_fbsj DESC, i_id 避免相同日期導致頁面資料抖動。
3. 索引支援：在 (d_fbsj, i_id) 建立複合索引，配合 OFFSET/FETCH 實現「索引掃描 + 早期停止」。
4. 大偏移量效能：若 p_page_no 極大（如第 10 萬頁），OFFSET 仍需掃描前 N 行。極端場景改用 Keyset Pagination（游標分頁）：
   WHERE (d_fbsj, i_id) < (:last_date, :last_id) ORDER BY ... FETCH FIRST :size ROWS ONLY

測試驗證腳本

VAR rc REFCURSOR;
EXEC proc_news_page('Oracle', NULL, NULL, 1, 10, 0, NULL, :cur, :tot_r, :tot_p, :rc);
PRINT rc;
PRINT cur tot_r tot_p;

總結

拋棄巢狀 ROWNUM 反模式，擁抱 12c OFFSET/FETCH 標準語法，配合綁定變數、確定性排序、複合索引，即可獲得可維護、高效能、標準相容的分頁解決方案。對於超大偏移量場景，請升級至 Keyset Pagination。