Oracle PL/SQL 动态 SQL

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

动态 SQL 在运行时构造[1]：

方式：

• EXECUTE IMMEDIATE
• DBMS_SQL

详细见：Oracle PL/SQL 动态 SQL。

---

2. EXECUTE IMMEDIATE

2.1 基本 DML

DECLARE
  v_sql VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE dept_id = :d';
  EXECUTE IMMEDIATE v_sql INTO v_count USING 10;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

2.2 DDL

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test (id NUMBER)';
END;
/

2.3 INSERT

DECLARE
  v_id NUMBER := 1;
  v_name VARCHAR2(100) := 'Alice';
BEGIN
  EXECUTE IMMEDIATE 
    'INSERT INTO employees (id, name) VALUES (:id, :name)'
    USING v_id, v_name;
END;
/

2.4 UPDATE

DECLARE
  v_id NUMBER := 1;
  v_salary NUMBER := 5000;
BEGIN
  EXECUTE IMMEDIATE 
    'UPDATE employees SET salary = :sal WHERE id = :id'
    USING v_salary, v_id;
END;
/

---

3. USING 子句

3.1 IN

EXECUTE IMMEDIATE v_sql USING IN v_id;

3.2 OUT

DECLARE
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 
    'BEGIN SELECT COUNT(*) INTO :c FROM employees; END;'
    USING OUT v_count;
  DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

3.3 IN OUT

DECLARE
  v_val NUMBER := 10;
BEGIN
  EXECUTE IMMEDIATE 
    'BEGIN :x := :x * 2; END;'
    USING IN OUT v_val;
  DBMS_OUTPUT.PUT_LINE(v_val);  -- 20
END;
/

---

4. RETURNING INTO

DECLARE
  v_id NUMBER;
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 
    'INSERT INTO employees (id, name) VALUES (seq.NEXTVAL, :n) 
     RETURNING id, name INTO :id, :name'
    USING 'Alice'
    RETURNING INTO v_id, v_name;
  
  DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name);
END;
/

---

5. BULK COLLECT

5.1 基本

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array;
BEGIN
  EXECUTE IMMEDIATE 'SELECT id FROM employees WHERE dept_id = :d'
    BULK COLLECT INTO v_ids
    USING 10;
END;
/

5.2 LIMIT

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array;
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR 'SELECT id FROM employees';
  LOOP
    FETCH v_cur BULK COLLECT INTO v_ids LIMIT 1000;
    EXIT WHEN v_ids.COUNT = 0;
    -- 处理
  END LOOP;
  CLOSE v_cur;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

6. FORALL

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  TYPE sal_array IS TABLE OF NUMBER;
  v_ids id_array;
  v_sals sal_array;
BEGIN
  v_ids := id_array(1, 2, 3);
  v_sals := sal_array(5000, 6000, 7000);
  
  FORALL i IN 1..v_ids.COUNT
    EXECUTE IMMEDIATE 
      'UPDATE employees SET salary = :sal WHERE id = :id'
      USING v_sals(i), v_ids(i);
END;
/

---

7. REF CURSOR

7.1 弱类型

DECLARE
  v_cur SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN v_cur FOR 'SELECT * FROM employees WHERE dept_id = :d' USING 10;
  LOOP
    FETCH v_cur INTO v_emp;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name);
  END LOOP;
  CLOSE v_cur;
END;
/

7.2 强类型

DECLARE
  TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;
  v_cur emp_cur;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN v_cur FOR SELECT * FROM employees WHERE id = 100;
  FETCH v_cur INTO v_emp;
  CLOSE v_cur;
END;
/

---

8. DBMS_SQL

8.1 基本

DECLARE
  v_cur NUMBER;
  v_cnt NUMBER;
  v_id NUMBER;
  v_name VARCHAR2(100);
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, 'SELECT id, name FROM employees', DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_id);
  DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_name, 100);
  
  v_cnt := DBMS_SQL.EXECUTE(v_cur);
  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cur) = 0;
    DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_id);
    DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_name);
    DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name);
  END LOOP;
  
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

8.2 对比

特性	EXECUTE IMMEDIATE	DBMS_SQL
简单	是	否
灵活	中	高
动态列	否	是
性能	好	中

---

9. DBMS_SQL.TO_CURSOR_NUMBER

9.1 转换

DECLARE
  v_refcur SYS_REFCURSOR;
  v_cur NUMBER;
  v_id NUMBER;
BEGIN
  OPEN v_refcur FOR 'SELECT id FROM employees';
  v_cur := DBMS_SQL.TO_CURSOR_NUMBER(v_refcur);
  
  -- 使用 DBMS_SQL
  DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_id);
  WHILE DBMS_SQL.FETCH_ROWS(v_cur) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_id);
    DBMS_OUTPUT.PUT_LINE(v_id);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

---

10. SQL 注入防护

10.1 绑定变量

-- 推荐
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

-- 避免
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;  -- 注入风险

10.2 验证

-- 表名/列名验证
IF v_table_name NOT IN ('EMPLOYEES', 'DEPARTMENTS') THEN
  RAISE_APPLICATION_ERROR(-20001, 'Invalid table');
END IF;

EXECUTE IMMEDIATE 'SELECT * FROM ' || v_table_name;

10.3 DBMS_ASSERT

-- 包装对象名
EXECUTE IMMEDIATE 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(v_table);

---

11. 应用场景

11.1 动态表名

CREATE OR REPLACE PROCEDURE purge_table(p_table VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || DBMS_ASSERT.ENQUOTE_NAME(p_table);
END;
/

11.2 动态 WHERE

CREATE OR REPLACE FUNCTION search_emp(
  p_name VARCHAR2 DEFAULT NULL,
  p_dept NUMBER DEFAULT NULL
) RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
  v_sql VARCHAR2(1000);
  v_where VARCHAR2(1000) := 'WHERE 1=1';
BEGIN
  IF p_name IS NOT NULL THEN
    v_where := v_where || ' AND name = :name';
  END IF;
  IF p_dept IS NOT NULL THEN
    v_where := v_where || ' AND dept_id = :dept';
  END IF;
  
  v_sql := 'SELECT * FROM employees ' || v_where;
  
  -- 复杂绑定需 DBMS_SQL
  -- 或多次判断
  OPEN v_cur FOR v_sql USING p_name, p_dept;
  RETURN v_cur;
END;
/

---

12. 性能

12.1 绑定变量

- 减少 hard parse
- 共享池利用
- 必须

12.2 结果缓存

-- 11g+
CREATE OR REPLACE FUNCTION get_count(p_dept NUMBER) 
RETURN NUMBER RESULT_CACHE IS
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE dept_id = :d'
    INTO v_count USING p_dept;
  RETURN v_count;
END;
/

---

13. 常见坑与排错

13.1 ORA-01006

-- 绑定变量不存在
-- 检查 USING 参数

13.2 ORA-01403

-- 无数据
-- 异常处理

13.3 注入

- 不用字符串拼接值
- 绑定变量
- DBMS_ASSERT

---

14. 最佳实践

1. EXECUTE IMMEDIATE 优先：简单
2. DBMS_SQL 复杂：动态列
3. 绑定变量：性能 + 安全
4. DBMS_ASSERT：对象名
5. BULK COLLECT：批量
6. FORALL：DML
7. REF CURSOR：返回结果
8. 避免注入：安全
9. 测试：场景
10. 文档化：使用

---

15. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Dynamic SQL” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/dynamic-sql.html