Oracle 動態 SQL 回傳結果集：DBMS_SQL 與 SYS_REFCURSOR 兩種模式實戰

Oracle 動態 SQL 回傳結果集：DBMS_SQL 與 SYS_REFCURSOR 兩種模式實戰

當查詢語句在執行時才確定（如使用者自訂報表條件），需用動態 SQL 執行並回傳結果集。Oracle 提供兩種主流方式：DBMS_SQL 套件（底層游標控制）與SYS_REFCURSOR（弱型別游標變數）。本文完整示範從建表、PL/SQL 區塊驗證、建立預存程序到 SQL*Plus 測試的全流程。

測試表準備

CREATE TABLE AAA (AA NVARCHAR2(100), BB NVARCHAR2(100), CC NVARCHAR2(100), DD NVARCHAR2(100));
INSERT INTO AAA VALUES ('1','1','1','1');

模式一：DBMS_SQL 完整流程（適用於未知欄位數/型別的極度動態場景）

DECLARE
  V_CURSOR  NUMBER;
  V_STAT    NUMBER;
  AA, BB, CC, DD VARCHAR2(100);
  V_SQL     VARCHAR2(200) := 'SELECT * FROM AAA';
BEGIN
  V_CURSOR := DBMS_SQL.OPEN_CURSOR;                    -- 1. 開啟游標
  DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);   -- 2. 解析 SQL
  DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 1, AA, 100);       -- 3. 定義輸出欄位
  DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 2, BB, 100);
  DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 3, CC, 100);
  DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 4, DD, 100);
  V_STAT := DBMS_SQL.EXECUTE(V_CURSOR);                -- 4. 執行
  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR) <= 0;      -- 5. 逐列提取
    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 1, AA);
    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 2, BB);
    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 3, CC);
    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 4, DD);
    DBMS_OUTPUT.PUT_LINE(AA||';'||BB||';'||CC||';'||DD);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(V_CURSOR);                     -- 6. 關閉游標
END;
/
-- 輸出：1;1;1;1

模式二：SYS_REFCURSOR 簡潔模式（推薦：欄位固定、僅 SQL 文本動態）

-- 1. 建立預存程序
CREATE OR REPLACE PROCEDURE MY_PROC (
  MYSQLVAL IN VARCHAR2,
  RESCUR   OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN RESCUR FOR MYSQLVAL;
END;
/

-- 2. SQL*Plus 測試
VAR R REFCURSOR;
EXEC MY_PROC('SELECT * FROM AAA', :R);
PRINT R;
-- 輸出正常結果集

兩種模式選型指南

進階：C# ODP.NET 呼叫 SYS_REFCURSOR

using Oracle.ManagedDataAccess.Client;
using System.Data;

public DataTable ExecuteDynamicQuery(string sql)
{
    using (var conn = new OracleConnection(connStr))
    using (var cmd = new OracleCommand("MY_PROC", conn) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.Add("MYSQLVAL", OracleDbType.Varchar2, sql, ParameterDirection.Input);
        cmd.Parameters.Add("RESCUR", OracleDbType.RefCursor, ParameterDirection.Output);
        conn.Open();
        using (var adapter = new OracleDataAdapter(cmd))
        {
            var dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
    }
}

安全警示

• 嚴防 SQL 注入：動態 SQL 參數化處理，禁止直接拼接使用者輸入字串
• 建立白名單驗證：僅允許預定義的表名、欄位名、運算子
• 使用 DBMS_ASSERT 套件驗證物件名稱：DBMS_ASSERT.ENQUOTE_NAME(user_input)

優先使用 SYS_REFCURSOR 滿足 95% 動態查詢需求，僅在極度動態結構時啟用 DBMS_SQL。