Oracle 全文檢索分頁存儲過程：CONTAINS + 游標實作

需求

支援全文檢索 (Oracle Text) 的分頁查詢，回傳總筆數與當前頁資料集 (REF CURSOR)。

核心邏輯

1. 動態構建 CONTAINS 查詢，按 SCORE(1) DESC 排序。
2. 用 EXECUTE IMMEDIATE 計算總筆數。
3. 計算分頁邊界：low = (page-1)*page_size + 1, high = page*page_size。
4. 用 ROWNUM 包裝兩層子查詢切分頁面。
5. 開啟 REF CURSOR 回傳給呼叫端 (Java/.NET/PL/SQL)。

完整 Package 實作

CREATE OR REPLACE PACKAGE search_pkg AS
  TYPE ref_cursor IS REF CURSOR;
  PROCEDURE page_search (
    p_key      IN  VARCHAR2,   -- 搜尋關鍵字
    p_page     IN  NUMBER,     -- 頁碼 (1-based)
    p_size     IN  NUMBER,     -- 頁面大小
    p_count    OUT NUMBER,     -- 總筆數
    p_records  OUT ref_cursor  -- 當前頁游標
  );
END search_pkg;
/

CREATE OR REPLACE PACKAGE BODY search_pkg AS
  PROCEDURE page_search (
    p_key      IN  VARCHAR2,
    p_page     IN  NUMBER,
    p_size     IN  NUMBER,
    p_count    OUT NUMBER,
    p_records  OUT ref_cursor
  ) AS
    v_sql      VARCHAR2(2000);
    v_cnt_sql  VARCHAR2(2000);
    v_low      NUMBER;
    v_high     NUMBER;
    v_table    CONSTANT VARCHAR2(30) := 'TEXT1';
    v_field    CONSTANT VARCHAR2(30) := 'SB01';
  BEGIN
    -- 1. 基礎全文查詢 (About 語法支援模糊/同義詞)
    v_sql := 'SELECT * FROM ' || v_table ||
             ' WHERE CONTAINS(' || v_field || ', ''ABOUT(' || p_key || ')'', 1) > 0' ||
             ' ORDER BY SCORE(1) DESC';

    -- 2. 計算總筆數
    v_cnt_sql := 'SELECT COUNT(*) FROM (' || v_sql || ')';
    EXECUTE IMMEDIATE v_cnt_sql INTO p_count;

    -- 3. 分頁邊界
    v_high := p_page * p_size;
    v_low  := v_high - p_size + 1;

    -- 4. 兩層 ROWNUM 切分頁
    v_sql := 'SELECT * FROM (' ||
             '  SELECT ROWNUM rn, t.* FROM (' || v_sql || ') t' ||
             '  WHERE ROWNUM <= ' || v_high ||
             ') WHERE rn >= ' || v_low;

    -- 5. 開啟游標
    OPEN p_records FOR v_sql;
  END page_search;
END search_pkg;
/

呼叫範例 (SQL*Plus)

VAR cnt NUMBER
VAR rc REFCURSOR
EXEC search_pkg.page_search('關鍵字', 1, 20, :cnt, :rc);
PRINT cnt
PRINT rc

效能優化建議

• 確保 CTXSYS.CONTEXT 索引已建立於 SB01，並定期 CTX_DDL.SYNC_INDEX / OPTIMIZE_INDEX。
• 大結果集時，考慮 RESULT_CACHE 快取總筆數或首頁資料。
• 若分頁深度極大 (如第 1000 頁)，改用 SCORE + ROWID 鍵集分頁 (Keyset Pagination) 避免掃描大量前置行。
• 動態 SQL 注入風險：p_key 應先以 CTX_DDL.ESCAPE_QUERY 轉義或使用繫結變數 (11g+ CONTAINS 支援繫結)。

結語

結合 Oracle Text CONTAINS、SCORE 排序與動態 ROWNUM 分頁，封裝為 REF CURSOR 存儲過程，即可為應用層提供高效能、標準化的全文檢索分頁介面。