PL/SQL DBMS_SQL 套件完整指南：動態 SQL 進階操作實戰

PL/SQL DBMS_SQL 套件完整指南：動態 SQL 進階操作實戰

DBMS_SQL 是 Oracle 提供的另一種動態 SQL 介面，適用於「查詢欄位數/型別未知」、「DDL 執行」、「大量批次綁定」等 EXECUTE IMMEDIATE 難以處理的場景。

標準操作流程

1. OPEN_CURSOR 開啟游標，取得游標 ID
2. PARSE 解析 SQL 字串
3. BIND_VARIABLE 綁定變數（可多次呼叫）
4. DEFINE_COLUMN 定義查詢結果欄位（僅 SELECT 需要）
5. EXECUTE 執行，返回處理行數
6. FETCH_ROWS 逐行擷取（SELECT）
7. COLUMN_VALUE 取得欄位值
8. CLOSE_CURSOR 關閉游標（必須在 finally 區塊執行）

實戰範例集

範例 1：動態建立資料表（DDL）

DECLARE
  v_cursor  NUMBER;
  v_sql     VARCHAR2(200);
  v_rows    NUMBER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  v_sql := 'CREATE TABLE t2 (id NUMBER, name VARCHAR2(20))';
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
  DBMS_OUTPUT.PUT_LINE('執行結果: ' || v_rows);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
    RAISE;
END;

範例 2：動態 INSERT 綁定變數

DECLARE
  v_id      NUMBER := &ID;
  v_name    VARCHAR2(20) := '&name';
  v_cursor  NUMBER;
  v_sql     VARCHAR2(200);
  v_rows    NUMBER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  v_sql := 'INSERT INTO t2 VALUES (:id, :name)';
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cursor, 'id', v_id);
  DBMS_SQL.BIND_VARIABLE(v_cursor, 'name', v_name);
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
    RAISE;
END;

範例 3：動態 UPDATE

v_sql := 'UPDATE t2 SET name = :name WHERE id = :id';
DBMS_SQL.BIND_VARIABLE(v_cursor, 'name', v_name);
DBMS_SQL.BIND_VARIABLE(v_cursor, 'id', v_id);

範例 4：動態 DELETE

v_sql := 'DELETE FROM t2 WHERE id = :id';
DBMS_SQL.BIND_VARIABLE(v_cursor, 'id', v_id);

範例 5：動態查詢（完整流程）

DECLARE
  v_deptno  emp.deptno%TYPE := &ID;
  v_cursor  NUMBER;
  v_sql     VARCHAR2(200);
  v_empno   NUMBER;
  v_ename   VARCHAR2(20);
  v_rows    NUMBER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  v_sql := 'SELECT empno, ename FROM emp WHERE deptno = :deptno';
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cursor, 'deptno', v_deptno);
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_empno);
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_ename, 20);  -- VARCHAR2 需指定長度
  
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) <= 0;
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_empno);
    DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_ename);
    DBMS_OUTPUT.PUT_LINE('no: ' || v_empno || ' ename: ' || v_ename);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
    RAISE;
END;

DBMS_SQL vs EXECUTE IMMEDIATE 選擇指南

關鍵注意事項

• 游標必須關閉：任何路徑（正常/異常）皆需 CLOSE_CURSOR，否則洩漏游標資源
• PARSE 第三參數用 DBMS_SQL.NATIVE（原生模式），舊版相容性參數已棄用
• DEFINE_COLUMN 對 VARCHAR2/RAW 必須指定最大長度
• 異常區塊使用 DBMS_SQL.IS_OPEN 判斷後再關閉，避免二次異常