Oracle PL/SQL 动态 SQL 详解

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

---

1. 概述

PL/SQL 动态 SQL 在运行时构造和执行 SQL[1]：

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

---

2. EXECUTE IMMEDIATE

2.1 基本

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

2.2 带变量

DECLARE
  v_name employees.name%TYPE;
  v_id NUMBER := 100;
BEGIN
  EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE id = :id'
    INTO v_name USING v_id;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

2.3 DDL

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

2.4 DML

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

2.5 多行

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE dept_id = :d'
    BULK COLLECT INTO v_emp USING 10;
END;
/

2.6 RETURNING

DECLARE
  v_id NUMBER := 100;
  v_old_salary NUMBER;
BEGIN
  EXECUTE IMMEDIATE 
    'UPDATE employees SET salary = salary * 1.1 WHERE id = :id 
     RETURNING salary INTO :sal'
    USING v_id RETURNING INTO v_old_salary;
  DBMS_OUTPUT.PUT_LINE('New: ' || v_old_salary);
END;
/

---

3. DBMS_SQL

3.1 流程

1. OPEN_CURSOR
2. PARSE
3. BIND_VARIABLE
4. EXECUTE
5. FETCH_ROWS / DEFINE_COLUMN
6. CLOSE_CURSOR

3.2 示例

DECLARE
  v_cursor INTEGER;
  v_count INTEGER;
  v_name VARCHAR2(100);
  v_id NUMBER := 100;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'SELECT name FROM employees WHERE id = :id', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', v_id);
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_name, 100);
  
  v_count := DBMS_SQL.EXECUTE(v_cursor);
  
  IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_name);
    DBMS_OUTPUT.PUT_LINE(v_name);
  END IF;
  
  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.3 数组

DECLARE
  v_cursor INTEGER;
  v_ids DBMS_SQL.NUMBER_TABLE;
  v_count INTEGER;
BEGIN
  v_ids(1) := 1;
  v_ids(2) := 2;
  v_ids(3) := 3;
  
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'DELETE FROM employees WHERE id = :id', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(v_cursor, ':id', v_ids);
  v_count := DBMS_SQL.EXECUTE(v_cursor);
  DBMS_OUTPUT.PUT_LINE('Deleted: ' || v_count);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

---

4. EXECUTE IMMEDIATE vs DBMS_SQL

项	EXECUTE IMMEDIATE	DBMS_SQL
简单	是	否
性能	高	中
灵活	中	高
动态列	否	是
数组	否	是
适合	一般	复杂

---

5. DBMS_ASSERT

5.1 SQL 注入防护

-- 危险
EXECUTE IMMEDIATE 'SELECT * FROM ' || p_table;  -- 注入！

-- 安全
EXECUTE IMMEDIATE 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table);

5.2 函数

函数	用途
ENQUOTE_LITERAL	字符串字面量
ENQUOTE_NAME	标识符
QUALIFIED_SQL_NAME	限定名
SCHEMA_NAME	Schema 名
SQL_OBJECT_NAME	对象名
SIMPLE_SQL_NAME	简单名

5.3 示例

CREATE OR REPLACE PROCEDURE safe_query(p_table VARCHAR2) IS
  v_count NUMBER;
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table);
  EXECUTE IMMEDIATE v_sql INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

---

6. USING

6.1 IN

EXECUTE IMMEDIATE 'SELECT ... WHERE id = :id' INTO v USING IN v_id;

6.2 OUT

EXECUTE IMMEDIATE 'BEGIN :x := 1; END;' USING OUT v_x;

6.3 IN OUT

EXECUTE IMMEDIATE 'BEGIN :x := :x + 1; END;' USING IN OUT v_x;

---

7. 动态 PL/SQL

DECLARE
  v_result NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :r := emp_pkg.get_count(:d); END;'
    USING OUT v_result, 10;
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

---

8. REF CURSOR

8.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;
/

8.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;
  ...
END;
/

详细见：Oracle PL/SQL 游标。

---

9. 性能

9.1 绑定变量

- 减少 hard parse
- Shared Pool 高效
- 必须

9.2 EXECUTE IMMEDIATE

- 简单高效
- 优先

9.3 DBMS_SQL

- 复杂场景
- 动态列
- 数组

详细见：Oracle PL/SQL 性能优化。

---

10. 应用场景

10.1 动态表名

CREATE OR REPLACE PROCEDURE copy_table(p_src VARCHAR2, p_dst VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 
    'INSERT INTO ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_dst) ||
    ' SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_src);
END;
/

10.2 动态 WHERE

CREATE OR REPLACE FUNCTION query_emp(p_dept NUMBER DEFAULT NULL, p_min_sal NUMBER DEFAULT NULL) 
  RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
  v_sql VARCHAR2(4000);
  v_where VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT * FROM employees WHERE 1=1';
  
  IF p_dept IS NOT NULL THEN
    v_sql := v_sql || ' AND dept_id = :dept';
  END IF;
  
  IF p_min_sal IS NOT NULL THEN
    v_sql := v_sql || ' AND salary >= :sal';
  END IF;
  
  IF p_dept IS NOT NULL AND p_min_sal IS NOT NULL THEN
    OPEN v_cur FOR v_sql USING p_dept, p_min_sal;
  ELSIF p_dept IS NOT NULL THEN
    OPEN v_cur FOR v_sql USING p_dept;
  ELSIF p_min_sal IS NOT NULL THEN
    OPEN v_cur FOR v_sql USING p_min_sal;
  ELSE
    OPEN v_cur FOR v_sql;
  END IF;
  
  RETURN v_cur;
END;
/

10.3 通用查询

CREATE OR REPLACE PROCEDURE generic_query(p_sql VARCHAR2) IS
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR p_sql;
  -- 处理结果
  CLOSE v_cur;
END;
/

---

11. 常见坑与排错

11.1 ORA-00900

- SQL 语法
- 检查

11.2 ORA-01006

- 绑定变量不存在
- 检查

11.3 SQL 注入

- 拼接字符串
- DBMS_ASSERT
- 绑定变量

---

12. 最佳实践

1. 绑定变量：必须
2. DBMS_ASSERT：安全
3. EXECUTE IMMEDIATE：简单
4. DBMS_SQL：复杂
5. REF CURSOR：结果集
6. 避免拼接：注入
7. USING：明确
8. BULK COLLECT：批量
9. 关闭游标：资源
10. 测试：完整

---

13. 参考资料

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