Oracle 动态 SQL（Native Dynamic SQL）

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

---

1. 概述

动态 SQL 在运行时构建和执行 SQL[1]：

适用场景：

• 表名/列名动态
• 复杂查询条件
• DDL 操作
• 未知结构

---

2. EXECUTE IMMEDIATE

2.1 基本语法

EXECUTE IMMEDIATE dynamic_sql 
  [INTO variable_list]
  [USING [IN|OUT|IN OUT] bind_variable_list];

2.2 DDL

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test (id NUMBER, name VARCHAR2(100))';
  EXECUTE IMMEDIATE 'DROP TABLE test';
END;

2.3 DML

DECLARE
  v_count NUMBER;
BEGIN
  -- INSERT
  EXECUTE IMMEDIATE 'INSERT INTO employees (id, name) VALUES (:1, :2)'
    USING 100, 'Alice';
  
  -- UPDATE
  EXECUTE IMMEDIATE 'UPDATE employees SET salary = :1 WHERE id = :2'
    USING 5000, 100;
  
  -- DELETE
  EXECUTE IMMEDIATE 'DELETE FROM employees WHERE id = :1'
    USING 100;
  
  -- SELECT
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE dept_id = :1'
    INTO v_count USING 10;
END;

2.4 动态表名

DECLARE
  v_table_name VARCHAR2(100) := 'employees';
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name
    INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;

---

3. USING 子句

3.1 IN 参数

DECLARE
  v_dept_id NUMBER := 10;
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE dept_id = :d'
    INTO v_count USING IN v_dept_id;
END;

3.2 OUT 参数

DECLARE
  v_sql VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  v_sql := 'BEGIN :result := COUNT_EMP(:dept); END;';
  EXECUTE IMMEDIATE v_sql 
    USING OUT v_count, IN 10;
END;

3.3 IN OUT 参数

DECLARE
  v_value NUMBER := 100;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :val := :val * 2; END;'
    USING IN OUT v_value;
  DBMS_OUTPUT.PUT_LINE(v_value);  -- 200
END;

---

4. RETURNING INTO

DECLARE
  v_id NUMBER := 100;
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 
    'UPDATE employees SET salary = salary * 1.1 
     WHERE employee_id = :1 
     RETURNING last_name INTO :2'
    USING v_id
    RETURNING INTO v_name;
  
  DBMS_OUTPUT.PUT_LINE('Updated: ' || v_name);
END;

---

5. REF CURSOR 动态查询

5.1 基本用法

DECLARE
  TYPE emp_cursor IS REF CURSOR;
  c_emp emp_cursor;
  v_emp employees%ROWTYPE;
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT * FROM employees WHERE dept_id = :d ORDER BY salary DESC';
  OPEN c_emp FOR v_sql USING 10;
  
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
  
  CLOSE c_emp;
END;

5.2 动态条件

DECLARE
  c_cur SYS_REFCURSOR;
  v_sql VARCHAR2(1000);
  v_where VARCHAR2(1000);
  v_emp employees%ROWTYPE;
BEGIN
  v_sql := 'SELECT * FROM employees';
  
  -- 动态构建 WHERE
  IF v_dept_id IS NOT NULL THEN
    v_where := ' WHERE dept_id = :d';
  END IF;
  
  v_sql := v_sql || v_where;
  
  IF v_dept_id IS NOT NULL THEN
    OPEN c_cur FOR v_sql USING v_dept_id;
  ELSE
    OPEN c_cur FOR v_sql;
  END IF;
  
  ...
END;

---

6. DBMS_SQL

6.1 适用场景

• 复杂动态 SQL
• 列数未知
• 多行结果

6.2 基本流程

DECLARE
  v_cursor INTEGER;
  v_sql VARCHAR2(1000);
  v_count NUMBER;
  v_id NUMBER;
  v_name VARCHAR2(100);
BEGIN
  v_sql := 'SELECT employee_id, last_name FROM employees WHERE dept_id = :d';
  
  -- 1. 打开游标
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  
  -- 2. 解析 SQL
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  
  -- 3. 绑定变量
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':d', 10);
  
  -- 4. 定义列
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_id);
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_name, 100);
  
  -- 5. 执行
  v_count := DBMS_SQL.EXECUTE(v_cursor);
  
  -- 6. 获取行
  LOOP
    IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
      EXIT;
    END IF;
    
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_id);
    DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_name);
    
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
  END LOOP;
  
  -- 7. 关闭游标
  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;
END;

---

7. EXECUTE IMMEDIATE vs DBMS_SQL

维度	EXECUTE IMMEDIATE	DBMS_SQL
简单性	高	低
性能	好	中
灵活性	中	高
动态列	不支持	支持
推荐	简单场景	复杂场景

---

8. SQL 注入防护

8.1 使用绑定变量

-- 安全
EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE name = :n'
  USING v_name;

-- 不安全（SQL 注入）
EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE name = ''' || v_name || '''';

8.2 验证输入

-- 验证表名
IF NOT REGEXP_LIKE(v_table_name, '^[A-Za-z_][A-Za-z0-9_]*$') THEN
  RAISE_APPLICATION_ERROR(-20001, 'Invalid table name');
END IF;

---

9. 应用场景

9.1 动态表名

CREATE OR REPLACE PROCEDURE count_rows(
  p_table_name VARCHAR2
) AS
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name)
    INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;

9.2 通用查询

CREATE OR REPLACE PROCEDURE dynamic_query(
  p_table VARCHAR2,
  p_where VARCHAR2 DEFAULT NULL,
  p_order VARCHAR2 DEFAULT NULL
) AS
  c_cur SYS_REFCURSOR;
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT * FROM ' || p_table;
  IF p_where IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where;
  END IF;
  IF p_order IS NOT NULL THEN
    v_sql := v_sql || ' ORDER BY ' || p_order;
  END IF;
  
  OPEN c_cur FOR v_sql;
  ...
END;

9.3 数据泵

-- 动态导出多个表
BEGIN
  FOR t IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'EMP%') LOOP
    EXECUTE IMMEDIATE 'CREATE TABLE ' || t.table_name || '_bak AS SELECT * FROM ' || t.table_name;
  END LOOP;
END;

---

10. 常见坑与排错

10.1 ORA-00900: 无效 SQL 语句

-- 检查 SQL 语法
-- DDL 不能带绑定变量
-- 错误：
EXECUTE IMMEDIATE 'CREATE TABLE :t (id NUMBER)' USING 'test';
-- 正确：
EXECUTE IMMDIAE 'CREATE TABLE test (id NUMBER)';

10.2 ORA-01006: 绑定变量不存在

-- 检查绑定变量名
-- SQL 中的 :name 与 USING 中的变量对应

10.3 ORA-01756: 引号字符串

-- 使用绑定变量避免引号问题
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE name = :n' USING v_name;

10.4 SQL 注入

修复：

-- 使用绑定变量
-- 验证输入
-- 使用 DBMS_ASSERT

---

11. 最佳实践

1. 优先使用绑定变量：性能+安全
2. EXECUTE IMMEDIATE 优先：简洁
3. DBMS_SQL 处理复杂：动态列
4. 验证输入：防注入
5. 使用 DBMS_ASSERT：对象名校验
6. 异常处理：关闭游标
7. 避免 SQL 拼接：性能差
8. 测试边界：空值等
9. 审计动态 SQL：安全
10. 限制权限：最小化

---

12. 参考资料

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