Oracle 預存程序分頁查詢封裝：動態 SQL + REF CURSOR 實作

設計目標

封裝通用分頁查詢，支援任意表、任意 WHERE/ORDER BY 條件，回傳總筆數與當頁游標。

Package 規範

CREATE OR REPLACE PACKAGE pg IS
    TYPE t_cursor IS REF CURSOR;

    PROCEDURE get_page_data (
        page_index   IN  INTEGER,      -- 當前頁碼 (1-based)
        page_size    IN  INTEGER,      -- 每頁筆數
        table_name   IN  VARCHAR2,     -- 表名
        query_cond   IN  VARCHAR2,     -- WHERE 與 ORDER BY 子句（含關鍵字）
        total_count  OUT NUMBER,       -- 總筆數
        v_cur        OUT t_cursor      -- 當頁游標
    );
END pg;
/

Package Body 實作

CREATE OR REPLACE PACKAGE BODY pg IS
    PROCEDURE get_page_data (
        page_index  IN  INTEGER,
        page_size   IN  INTEGER,
        table_name  IN  VARCHAR2,
        query_cond  IN  VARCHAR2,
        total_count OUT NUMBER,
        v_cur       OUT t_cursor
    ) AS
        v_sql       VARCHAR2(5000);
        v_plow      NUMBER;
        v_phei      NUMBER;
        p_sql       VARCHAR2(5000);
        p_sql2      VARCHAR2(5000);
        v_tmp       VARCHAR2(5000);
        v_total_cnt VARCHAR2(50);
    BEGIN
        -- 計算分頁範圍
        v_phei := page_index * page_size;
        v_plow := v_phei - page_size + 1;

        -- 基礎查詢（要求外層包含 ROWNUM）
        p_sql  := 'SELECT t.* FROM ' || table_name || ' t WHERE 1=1 ' || query_cond;
        p_sql2 := 'SELECT ROWNUM rn, a.* FROM (' || p_sql || ') a';
        v_sql  := 'SELECT * FROM (' || p_sql2 || ') WHERE rn BETWEEN ' || v_plow || ' AND ' || v_phei;

        -- 總筆數查詢
        v_tmp := 'SELECT COUNT(*) FROM (' || p_sql || ')';
        EXECUTE IMMEDIATE v_tmp INTO v_total_cnt;
        total_count := v_total_cnt;

        -- 開啟游標回傳
        OPEN v_cur FOR v_sql;
    END get_page_data;
END pg;
/

呼叫範例

DECLARE
    v_cur       pg.t_cursor;
    v_total     NUMBER;
    v_id        NUMBER;
    v_name      VARCHAR2(100);
BEGIN
    pg.get_page_data(
        page_index  => 2,
        page_size   => 10,
        table_name  => 'EMP',
        query_cond  => ' AND deptno = 20 ORDER BY hiredate',
        total_count => v_total,
        v_cur       => v_cur
    );

    DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
    LOOP
        FETCH v_cur INTO v_id, v_name;  -- 依實際欄位調整
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name);
    END LOOP;
    CLOSE v_cur;
END;
/

注意事項與優化建議

• SQL Injection 風險：table_name、query_cond 直接拼接，生產環境必須驗證白名單或改用 DBMS_ASSERT。
• 效能：Oracle 12c+ 建議改用 OFFSET ... FETCH NEXT ... ROWS ONLY 標準語法，優化器可直接優化分頁。
  SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
• 大資料量深分頁：ROWNUM BETWEEN 需掃描前 N 筆，深分頁效能遞減。可改用 Keyset Pagination（記住上一頁最大值 WHERE id > :last_id ORDER BY id FETCH FIRST 10 ROWS ONLY）。
• 游標洩漏：呼叫端務必 CLOSE v_cur，否則導致 ORA-01000。
• query_cond 必須包含 ORDER BY 確保分頁穩定，否則同頁資料順序可能每次不同。

Oracle 12c+ 現代分頁寫法對照

-- 標準語法（最佳效能）
SELECT * FROM emp WHERE deptno = 20 ORDER BY hiredate
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 含總筆數（12.1+）
SELECT *, COUNT(*) OVER() AS total_cnt
FROM emp WHERE deptno = 20 ORDER BY hiredate
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;