Oracle PL/SQL 动态 SQL 教程：Execute Immediate 和 DBMS_SQL

作者： Fiona Brown Fiona Brown
小时 - 权威教程 更新于

什么是动态 SQL？

动态 SQL 是一种在运行时生成和执行语句的编程方法。它主要用于编写通用且灵活的程序，在这些程序中，SQL 语句将根据需求在运行时创建和执行。

编写动态 SQL 的方法

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

  1. NDS – 原生动态 SQL
  2. DBMS_SQL

NDS（原生动态 SQL）– Execute Immediate

原生动态 SQL 是编写动态 SQL 的一种更简单的方法。它使用“EXECUTE IMMEDIATE”命令在运行时创建和执行 SQL。但是，要使用这种方法，运行时使用的变量的数据类型和数量需要提前知道。与 DBMS_SQL 相比，它还提供了更好的性能和更低的复杂性。

语法

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • 上面的语法显示了 EXECUTE IMMEDIATE 命令。
  • INTO 子句是可选的，仅当动态 SQL 包含一个获取值的 SELECT 语句时使用。变量类型应与 SELECT 语句的变量类型匹配。
  • USING 子句是可选的，仅当动态 SQL 包含任何绑定变量时使用。

示例 1：在此示例中，我们将使用 NDS 语句从 emp 表中为 emp_no '1001' 获取数据。

NDS - Execute Immediate

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

输出

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

代码解释

  • 代码第 2-6 行：声明变量。
  • 代码第 8 行：在运行时构建 SQL。SQL 在 where 条件“:empno”中包含绑定变量。
  • 代码第 9 行：使用 NDS 命令“EXECUTE IMMEDIATE”执行已构建的 SQL 文本（在第 8 行完成）。
  • “INTO”子句中的变量 (lv_emp_name, ln_emp_no, ln_salary, ln_manager) 用于保存从 SQL 查询 (emp_name, emp_no, salary, manager) 中获取的值。
  • “USING”子句为 SQL 查询中的绑定变量（:emp_no）提供值。
  • 代码第 10-13 行：显示获取的值。

DBMS_SQL 用于动态 SQL

PL/SQL 提供了 DBMS_SQL 包，允许您使用动态 SQL。创建和执行动态 SQL 的过程包含以下步骤：

  • 打开游标：动态 SQL 将以与游标相同的方式执行。因此，为了执行 SQL 语句，我们必须打开游标。
  • 解析 SQL：下一步是解析动态 SQL。此过程仅检查语法并将查询准备好执行。
  • 绑定变量值：下一步是为绑定变量（如果有）分配值。
  • 定义列：下一步是使用 SELECT 语句中相对位置的列来定义它们。
  • 执行：下一步是执行已解析的查询。
  • 获取值：下一步是获取已执行的值。
  • 关闭游标：获取结果后，应关闭游标。

示例 1：在此示例中，我们将使用 DBMS_SQL 语句从 emp 表中为 emp_no '1001' 获取数据。

DBMS_SQL for Dynamic SQL

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

DBMS_SQL for Dynamic SQL

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

输出

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

代码解释

  • 代码第 1-9 行：变量声明。
  • 代码第 10 行：构建 SQL 语句。
  • 代码第 11 行：使用 DBMS_SQL.OPEN_CURSOR 打开游标。它将返回已打开的游标 ID。
  • 代码第 12 行：打开游标后，解析 SQL。
  • 代码第 13 行：将绑定变量“1001”分配给游标 ID，而不是“:empno”。
  • 代码第 14-17 行：根据 SQL 语句中的相对位置定义列名。在我们的例子中，相对位置是 (1) emp_name，(2) emp_no，(3) salary，(4) manager。因此，基于此位置，我们定义了目标变量。
  • 代码第 18 行：使用 DBMS_SQL.EXECUTE 执行查询。它返回处理的记录数。
  • 代码第 19-33 行：使用循环获取记录并显示。
  • 代码第 20 行：DBMS_SQL.FETCH_ROWS 将从已处理的行中获取一条记录。它可以被反复调用以获取所有行。如果它无法获取行，它将返回 0，从而退出循环。

摘要

在本节中，我们讨论了动态 SQL 和执行动态 SQL 的方法。我们还看到了在两种方法中执行动态 SQL 的不同步骤。我们还看到了在两种 NDS 和 DBMS_SQL 方法中处理相同场景以执行运行时操作的示例。