PL/SQL 動態 SQL 完整實戰：EXECUTE IMMEDIATE 與 DBMS_SQL 套件詳解

動態 SQL 定義與適用場景

• 靜態 SQL：編譯期確定物件與語句，如 DML、查詢、交易控制。
• 動態 SQL：執行期才組裝語句，適用於 DDL、系統控制指令、依參數決定的表名/欄位名、動態 WHERE 條件等。

Oracle 提供兩種實作方式：原生動態 SQL (EXECUTE IMMEDIATE) 與 DBMS_SQL 套件。

一、原生動態 SQL (EXECUTE IMMEDIATE)

1. 執行 DDL：動態建表

CREATE OR REPLACE PROCEDURE proc_test (
    p_table_name  VARCHAR2,
    p_field1      VARCHAR2, p_datatype1 VARCHAR2,
    p_field2      VARCHAR2, p_datatype2 VARCHAR2
) AS
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'CREATE TABLE ' || p_table_name || ' (' ||
             p_field1 || ' ' || p_datatype1 || ', ' ||
             p_field2 || ' ' || p_datatype2 || ')';
    EXECUTE IMMEDIATE v_sql;  -- 直接執行 DDL
EXCEPTION WHEN OTHERS THEN NULL;
END;

測試：EXEC proc_test('dinya_test','id','NUMBER(8) NOT NULL','name','VARCHAR2(100)'); → 成功建表。

2. 執行 DML：綁定變數 (USING)

CREATE OR REPLACE PROCEDURE proc_insert (p_id NUMBER, p_name VARCHAR2) AS
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'INSERT INTO dinya_test VALUES (:1, :2)';
    EXECUTE IMMEDIATE v_sql USING p_id, p_name;
END;

查詢回傳單值用 INTO + USING：

v_sql := 'SELECT COUNT(*) FROM table_name WHERE id = :id';
EXECUTE IMMEDIATE v_sql INTO v_count USING p_id;

需輸出參數時用 RETURNING INTO 子句。

二、DBMS_SQL 套件（四步驟流程）

1. v_cur := DBMS_SQL.OPEN_CURSOR 開啟游標
2. DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE) 解析
3. DBMS_SQL.BIND_VARIABLE(v_cur, ':name', value) 綁定
4. v_rows := DBMS_SQL.EXECUTE(v_cur) 執行
5. DBMS_SQL.CLOSE_CURSOR(v_cur) 關閉（例外處理區也要關閉）

1. 執行 DDL：動態建表

CREATE OR REPLACE PROCEDURE proc_dbms_sql (
    p_table_name VARCHAR2, p_f1 VARCHAR2, p_dt1 VARCHAR2,
    p_f2 VARCHAR2, p_dt2 VARCHAR2
) AS
    v_cur NUMBER; v_sql VARCHAR2(200); v_rows NUMBER;
BEGIN
    v_cur := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'CREATE TABLE '||p_table_name||' ('||p_f1||' '||p_dt1||', '||p_f2||' '||p_dt2||')';
    DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);  -- PARSE 即執行 DDL
    v_rows := DBMS_SQL.EXECUTE(v_cur);               -- DDL 可省略 EXECUTE
    DBMS_SQL.CLOSE_CURSOR(v_cur);
EXCEPTION WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_cur); RAISE;
END;

關鍵差異：DDL 不可使用綁定變數，直接拼接字串；PARSE 階段即執行 DDL，EXECUTE 可省略。

2. 執行 DML：參數化更新

CREATE OR REPLACE PROCEDURE proc_dbms_sql_update (p_id NUMBER, p_name VARCHAR2) AS
    v_cur NUMBER; v_sql VARCHAR2(200); v_rows NUMBER;
BEGIN
    v_cur := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'UPDATE dinya_test2 SET name = :p_name WHERE id = :p_id';
    DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cur, ':p_name', p_name);
    DBMS_SQL.BIND_VARIABLE(v_cur, ':p_id', p_id);
    v_rows := DBMS_SQL.EXECUTE(v_cur);
    DBMS_SQL.CLOSE_CURSOR(v_cur);
EXCEPTION WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_cur); RAISE;
END;

測試：EXEC proc_dbms_sql_update(2, 'csdn_dinya'); → 第二筆 name 更新成功。

查詢型動態 SQL 處理

需額外：
DBMS_SQL.DEFINE_COLUMN 定義輸出欄位 → EXECUTE → FETCH_ROWS 迴圈 → COLUMN_VALUE 取值 → VARIABLE_VALUE 取輸出參數。

總結比較

建議：簡單動態 DML/DDL 優先用 EXECUTE IMMEDIATE；需解析未知結構查詢或大量重複解析優化時用 DBMS_SQL。