Oracle PL/SQL 动态 SQL 教程：立即执行和 DBMS

⚡ 智能摘要

动态 SQL Oracle PL/SQL 在运行时构建和运行语句，通过两种方法使查询适应不断变化的需求：使用 EXECUTE IMMEDIATE 和 OPEN-FOR 的原生动态 SQL，以及用于复杂情况的灵活的 DBMS_SQL 包。

⚙️ 运行时 SQL： 当事先不知道表名或列名时，动态 SQL 会生成并执行语句。
⚡ 原生动态 SQL： EXECUTE IMMEDIATE 以最少的代码快速创建和运行 SQL。
🔁 开放申请： 处理 EXECUTE IMMEDIATE 无法单独获取的多行动态查询。
🧩 DBMS_SQL： 适用于在运行时列数或类型未知的语句。
🔐 绑定变量： USING 子句按位置传递值，并阻止 SQL 注入。
🤖 人工智能协助： AI工具能够生成动态SQL语句，并在审查过程中标记注入风险。

什么是动态 SQL？

动态 SQL SQL是一种在运行时生成和执行语句的编程方法。它主要用于编写通用且灵活的程序，其中SQL语句根据需求在运行时创建和执行，例如，当表名、列列表或WHERE条件在程序运行时才确定时。

编写动态 SQL 的方法

PL/SQL 提供了两种编写动态 SQL 的方法：

NDS – 本机动态 SQL （EXECUTE IMMEDIATE 和 OPEN-FOR 语句）
DBMS_SQL （提供的包裹）

一般规则很简单：如果在编译时已知输入输出变量的数量和数据类型，则使用原生动态 SQL，因为它速度更快，代码量更少。如果这些信息只能在运行时知道，则使用 DBMS_SQL 包。

NDS（本机动态 SQL）–立即执行

原生动态 SQL 是编写动态 SQL 的更简便方法。它使用 EXECUTE IMMEDIATE 命令在运行时创建并执行 SQL。要使用这种方法，必须预先知道运行时使用的变量的数据类型和数量。与 DBMS_SQL 相比，它还具有更高的性能和更低的复杂度。

句法

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

dynamic_sql_string： 包含单个 SQL 语句或 PL/SQL 块的字符串表达式（VARCHAR2 或 CHAR，而不是 NVARCHAR2/NCHAR）。
INTO 子句： 可选。仅当动态 SQL 为单行 SELECT 语句时使用；它会将返回值捕获到变量或记录中。每个选定的列都需要一个类型兼容的变量。
USING 子句： 可选。用于绑定变量。默认模式为 IN；OUT 和 IN OUT 用于接收返回值。
返回到子句： 与带有 RETURNING 子句的 DML 语句一起使用，将受影响的行值捕获到绑定参数中。

例如1： 在这个例子中，我们使用带有绑定变量的 NDS 语句从 emp 表中获取 emp_no 为 '1001' 的数据。

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

输出

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code 说明：

第 2-6 行： 声明变量。
线8： 在运行时构建 SQL 语句。该 SQL 语句的 WHERE 条件中包含绑定变量“:empno”。
第 9-11 行： 使用 EXECUTE IMMEDIATE 执行框架 SQL。INTO 子句中的变量保存提取的值，USING 子句为绑定变量 :empno 提供值。
第 12-15 行： 显示获取到的值。

使用动态 SQL 进行 DDL

静态 PL/SQL 无法直接执行 CREATE、ALTER 或 DROP 等 DDL 语句。EXECUTE IMMEDIATE 通过将语句构建为字符串来解决这个问题，这在运行时提供对象名称时也非常方便：

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

对象名称（表名、列名、模式）不能作为绑定变量传递，因此必须将它们连接成字符串。务必验证此类输入，例如使用 DBMS_ASSERT.SIMPLE_SQL_NAME，以避免 SQL 注入。

DBMS_SQL 用于动态 SQL

PL/SQL 提供了 DBMS_SQL 包，用于处理动态 SQL，即语句的结构在运行时才能确定的情况。创建和执行动态 SQL 的过程包括以下步骤：

打开光标： 动态 SQL 的执行方式如下：光标要执行 SQL 语句，我们必须先打开游标。
解析 SQL： 解析动态 SQL。此操作会检查语法并确保查询随时可以执行。
绑定变量值： 如果有绑定变量，请为其赋值。
定义列： 在 SELECT 语句中，使用列的相对位置来定义每一列。
执行： 执行已解析的查询。
获取值： 获取已执行的值。
关闭光标： 获取结果后，关闭光标。

例如1： 在这个例子中，我们使用 DBMS_SQL 语句从 emp 表中获取 emp_no 为 '1001' 的数据。即使发生错误，EXCEPTION 块也会关闭游标。

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

输出

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code 说明：

第 1-8 行： 变量声明。
线10： 构建 SQL 语句。
线11： 使用 DBMS_SQL.OPEN_CURSOR 打开游标，该函数返回已打开游标的 ID。
线12： 打开游标后，解析 SQL。
线13： 绑定值“1001”被赋值给“:empno”。
第 14-17 行： 按相对位置定义列：（1）emp_name，（2）emp_no，（3）salary，（4）manager。
线18： 使用 DBMS_SQL.EXECUTE 执行查询，返回已处理的记录数。
第 19-32 行： 循环获取记录。当没有剩余行时，FETCH_ROWS 返回 0，从而退出循环。
异常处理块： 确保光标已关闭，因此如果发生错误，打开的光标不会泄漏。

NDS 与 DBMS_SQL：何时使用哪一个？

两种方法都在运行时执行 SQL，但它们适用于不同的场景：

使用原生动态 SQL（EXECUTE IMMEDIATE / OPEN-FOR） 当输入输出的数量和数据类型在编译时已知时，这种方法速度更快、更易读，而且需要的代码更少。
使用 DBMS_SQL 例如，当结构在运行时才确定时，查询中选定的列或绑定变量的数量会发生变化（称为方法 4 动态 SQL），或者语句太大而无法放入单个 32K VARCHAR2 变量中。

常见问题

绑定变量将用户输入作为数据传递，而不是作为可执行代码传递。USING 子句按位置提供值，因此恶意文本无法更改语句结构。始终绑定不受信任的输入，而不是将其拼接起来。

序号 Oracle 仅绑定数据值，不绑定对象名称。将标识符连接到 SQL 字符串中，并使用 DBMS_ASSERT.SIMPLE_SQL_NAME 进行验证，以防止注入攻击。

EXECUTE IMMEDIATE 只获取一行数据。对于多行数据，请使用 OPEN-FOR 语句打开一个 REF CURSOR，然后循环执行 FETCH 直到 %NOTFOUND，最后关闭游标。

在 INSERT、UPDATE 或 DELETE 语句中添加 RETURNING 子句，然后使用 EXECUTE IMMEDIATE 的 RETURNING INTO 子句将受影响的行值捕获到绑定参数中。

动态 SQL 会增加解析开销，因为语句是在运行时编译的。重用绑定变量可以 Oracle 共享游标并减少硬解析，保持ping 性能接近静态 SQL。

字符串必须为 VARCHAR2 或 CHAR 类型。不允许使用 NVARCHAR2 和 NCHAR 等国家字符类型。对于长度超过 32K 的文本，DBMS_SQL 接受 VARCHAR2 字符串集合。

是的。像 GitHub Copilot 这样的 AI 助手可以根据简单的提示生成 EXECUTE IMMEDIATE 和 DBMS_SQL 代码块，建议绑定变量占位符，并解释每个子句，但开发人员仍然应该审核输出结果。

人工智能驱动的代码扫描器会标记拼接的用户输入，并建议使用绑定变量或进行 DBMS_ASSERT 检查。它们会在代码审查期间突出显示风险模式，并提供帮助。ping 团队在部署前发现注入漏洞。

Oracle PL/SQL 动态 SQL 教程：立即执行和 DBMS_SQL

什么是动态 SQL？

编写动态 SQL 的方法

NDS（本机动态 SQL）–立即执行

使用动态 SQL 进行 DDL

DBMS_SQL 用于动态 SQL

NDS 与 DBMS_SQL：何时使用哪一个？

常见问题

总结一下这篇文章：

注册简报

什么是动态 SQL？

编写动态 SQL 的方法

NDS（本机动态 SQL）–立即执行

相关文章

使用动态 SQL 进行 DDL

DBMS_SQL 用于动态 SQL

NDS 与 DBMS_SQL：何时使用哪一个？

常见问题

总结一下这篇文章：

注册简报