Oracle DBMS_SQL 套件實戰：動態查詢與 DML 完整範例

DBMS_SQL 核心流程

1. OPEN_CURSOR → 取得游標 ID (NUMBER)。
2. PARSE → 解析動態 SQL。
3. BIND_VARIABLE → 綁定輸入參數 (針對 DML/查詢 WHERE)。
4. DEFINE_COLUMN → 定義查詢輸出欄位對應變數 (僅查詢)。
5. EXECUTE → 執行，返回處理筆數 (DML) 或 0 (查詢)。
6. FETCH_ROWS → 迴圈抓取下一行 (查詢)，返回 1/0。
7. COLUMN_VALUE → 將緩衝區欄位值讀入變數 (查詢)。
8. CLOSE_CURSOR → 釋放游標。

範例 1：動態查詢 (SELECT)

CREATE OR REPLACE PROCEDURE pro_dict_proteam IS
    v_cursor     NUMBER;
    v_sql        VARCHAR2(500);
    v_proteam_id NUMBER;
    v_proteam_name VARCHAR2(50);
    v_cnt        NUMBER;
    v_workflag   NUMBER := 1;
BEGIN
    v_sql := 'SELECT proteamid, proteamname FROM dict_proteam WHERE workflag = :wf';
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':wf', v_workflag);
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_proteam_id);
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_proteam_name, 50);
    v_cnt := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_OUTPUT.PUT_LINE('EXECUTE 返回:' || v_cnt);
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) <= 0;
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_proteam_id);
        DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_proteam_name);
        DBMS_OUTPUT.PUT_LINE(v_proteam_id || ' ' || v_proteam_name);
    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;
/

-- 測試
BEGIN pro_dict_proteam; END;
/
-- 輸出
-- EXECUTE 返回:0
-- 1 電器上車組
-- 2 電機上車組
-- ...

範例 2：動態 DML (INSERT)

CREATE TABLE tb_test2 (id NUMBER, name VARCHAR2(100), sex CHAR(5));

CREATE OR REPLACE PROCEDURE pro_tb_test2 IS
    v_cursor NUMBER;
    v_sql    VARCHAR2(200);
    v_cnt    NUMBER;
BEGIN
    v_sql := 'INSERT INTO tb_test2 (id, name, sex) VALUES (:id, :name, :sex)';
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', 1);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':name', 'tom');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':sex', '男');
    v_cnt := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    DBMS_OUTPUT.PUT_LINE('資料插入成功! ' || v_cnt);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
        DBMS_OUTPUT.PUT_LINE('異常: ' || SQLERRM);
END;
/

-- 測試
TRUNCATE TABLE tb_test2;
BEGIN pro_tb_test2; END;
/
SELECT * FROM tb_test2;
-- 1 tom 男

DML 流程差異

關鍵提示

• 數值/日期欄位在 BIND_VARIABLE 時傳入原生類型 (NUMBER/DATE)，不要轉字串；DEFINE_COLUMN 字串欄位需指定最大長度。
• PARSE 第三參數 DBMS_SQL.NATIVE (10g+) 使用原生 PL/SQL 引擎，效能最佳。
• 異常區塊務必檢查 DBMS_SQL.IS_OPEN 並關閉游標，避免游標洩漏導致 ORA-01000。
• 大量資料操作優先考慮 EXECUTE IMMEDIATE + BULK COLLECT/FORALL 或 DBMS_SQL.BIND_ARRAY 批次綁定。
• DBMS_SQL 支援 DESCRIBE_COLUMNS 在執行期發現查詢欄位中繼資料，適合通用報表工具開發。