Oracle 動態 SQL 實戰：構建通用分頁查詢存儲過程的最佳實踐與性能優化

引言

動態 SQL（EXECUTE IMMEDIATE / DBMS_SQL）賦予 PL/SQL 運行時構建、執行任意 SQL 的能力，適用於表名/列名/條件/排序動態化場景。但動態 SQL 伴隨**難以靜態檢查、解析開銷大、綁定變量窺探導致計劃不穩、可讀性差**等缺點。本文以**通用分頁查詢**為例，展示動態 SQL 規範寫法、綁定變量最佳實踐、性能優化要點與替代方案對比。

一、通用分頁存儲過程規範實現

CREATE OR REPLACE PROCEDURE sp_exec_dynamic_page (
    i_tablename     IN  VARCHAR2,          -- 表名，支持多表聯結別名，如 'employees e, departments d'
    i_tablecolumn   IN  VARCHAR2,          -- 查詢列，如 'e.employee_id, d.department_name'，NULL 表示 *
    i_where         IN  VARCHAR2,          -- WHERE 條件，不含 WHERE 關鍵字，如 "d.department_name LIKE 'S%'"
    i_ordercolumn   IN  VARCHAR2,          -- ORDER BY 列，如 'd.department_name DESC'
    i_pagesize      IN  NUMBER,            -- 每頁行數
    i_curpage       IN  NUMBER,            -- 當前頁碼 (1-based)
    o_rowcount      OUT NUMBER,            -- 總行數
    o_pagecount     OUT NUMBER,            -- 總頁數
    o_cursor        OUT SYS_REFCURSOR      -- 分頁結果集游標
) IS
    v_start_rec   NUMBER;
    v_end_rec     NUMBER;
    v_pagesize    NUMBER;
    v_curpage     NUMBER;
    v_cols        VARCHAR2(2000);
    v_where       VARCHAR2(2000);
    v_order       VARCHAR2(200);
    v_cnt_sql     VARCHAR2(32767);
    v_sel_sql     VARCHAR2(32767);
BEGIN
    -- 1. 參數預處理與默認值
    v_cols  := NVL(i_tablecolumn, '*');
    v_where := CASE WHEN i_where IS NOT NULL THEN ' WHERE 1=1 AND ' || i_where ELSE ' WHERE 1=1' END;
    v_order := CASE WHEN i_ordercolumn IS NOT NULL THEN ' ORDER BY ' || i_ordercolumn ELSE '' END;
    v_pagesize := NVL(i_pagesize, 10);
    v_curpage  := CASE WHEN i_curpage IS NULL OR i_curpage < 1 THEN 1 ELSE i_curpage END;

    -- 2. 統計總行數（使用綁定變量防注入、利用游標共享）
    v_cnt_sql := 'SELECT COUNT(*) FROM ' || i_tablename || v_where;
    EXECUTE IMMEDIATE v_cnt_sql INTO o_rowcount;

    -- 3. 計算總頁數
    IF MOD(o_rowcount, v_pagesize) = 0 THEN
        o_pagecount := o_rowcount / v_pagesize;
    ELSE
        o_pagecount := FLOOR(o_rowcount / v_pagesize) + 1;
    END IF;

    -- 4. 修正頁碼越界
    IF v_curpage > o_pagecount THEN
        v_curpage := o_pagecount;
    END IF;

    -- 5. 計算分頁邊界
    v_start_rec := (v_curpage - 1) * v_pagesize + 1;
    v_end_rec   := v_curpage * v_pagesize;

    -- 6. 構建核心查詢（內層：排序+截取 ROWNUM；外層：過濾 RN）
    v_sel_sql := 
        'SELECT * FROM (' ||
        '  SELECT e.*, ROWNUM rn FROM (' ||
        '    SELECT ' || v_cols || ' FROM ' || i_tablename || v_where || v_order ||
        '  ) e' ||
        '  WHERE ROWNUM <= :end_rec' ||
        ') WHERE rn >= :start_rec';

    -- 7. 綁定變量執行，開啟游標返回
    OPEN o_cursor FOR v_sel_sql USING v_end_rec, v_start_rec;

    -- 8. 調試輸出（生產環境建議移除或改為 DEBUG 級別日誌）
    DBMS_OUTPUT.PUT_LINE('Count SQL => ' || v_cnt_sql);
    DBMS_OUTPUT.PUT_LINE('Page SQL  => ' || v_sel_sql);
END;
/

二、關鍵設計決策解析

三、動態 SQL 性能優化要點

1. 盡量使用綁定變量：所有**字面值**參數（分頁邊界、業務過濾值）必須綁定，減少硬解析、共享游標、防注入
2. 避免綁定變量窺探副作用：數據傾斜列綁定時，優化器可能根據首次執行值生成不優計劃。對策：
   　- OPT_PARAM('_optim_peek_user_binds', 'FALSE') 會話級關閉窺探
   　- 使用 DBMS_SQL 手動綁定並設置 BIND_ARRAY
   　- 關鍵查詢固定寫死 SQL，不動態化
3. 動態 SQL 緩存：相同模板重複調用會命中 Library Cache，**保持模板字符串完全一致**（大小寫、空格、換行）
4. 批量綁定：DBMS_SQL.BIND_ARRAY / FORALL 批量 DML，減少上下文切換
5. 限制動態 SQL 範圍：僅將**真正動態**部分（表名、列名、可選條件）動態化，固定骨架靜態化

四、Oracle 12c+ 標準分頁語法（推薦替代動態 SQL）

-- 靜態 SQL + 標準分頁，無需動態構建
SELECT *
FROM (
    SELECT e.*, ROW_NUMBER() OVER (ORDER BY d.department_name DESC) rn
    FROM employees e, departments d
    WHERE d.department_name LIKE 'S%'
)
WHERE rn BETWEEN :start_rec AND :end_rec;
-- 或 12c 簡潔語法
SELECT * 
FROM employees e, departments d
WHERE d.department_name LIKE 'S%'
ORDER BY d.department_name DESC
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

**優勢**：靜態解析、計劃穩定、優化器完整優化、可讀性高、無注入風險。**除非表名/列名確需動態，優先使用靜態 SQL + 綁定變量**。

五、安全加固：防止動態 SQL 注入

1. 識別符白名單驗證：表名、列名、排序列必須從預定義集合中選擇，拒絕任意字符串
2. 條件片段白名單/正則校驗：僅允許簡單比較、IN、LIKE、BETWEEN，禁止子查詢、函數調用、註釋符
3. 最小權限執行：動態 SQL 執行上下文為定義者權限，確保調用者僅有目標表 SELECT 權限
4. 審計日誌：記錄動態 SQL 模板、綁定值、執行用戶、耗時

六、總結

動態 SQL 是雙刃劍。**通用分頁場景下，Oracle 12c+ 標準 OFFSET/FETCH 或 ROW_NUMBER() 靜態 SQL 為首選**；確需動態表名/列名時，遵循「模板固定、值綁定、識別符白名單、最小權限、審計日誌」五原則。理解綁定變量窺探、硬解析開銷、游標共享機制，能寫出高性能、安全、可維護的動態 SQL 代碼。