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

由： 菲奥娜·布朗 菲奥娜·布朗
Hours 更新

什么是动态 SQL？

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

编写动态 SQL 的方法

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

  1. NDS – 本机动态 SQL
  2. DBMS_SQL

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

本机动态 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-立即执行

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。此过程仅检查语法并使查询保持可执行状态。
  • 绑定变量值：下一步是分配绑定变量的值（如果有）。
  • 定义列：下一步是使用选择语句中的相对位置来定义列。
  • 执行：下一步是执行解析后的查询。
  • 获取值：下一步是获取执行的值。
  • 关闭光标：一旦获取结果，就应该关闭游标。

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

DBMS_SQL 用于动态 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 用于动态 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 方式处理同一场景以在运行时执行的示例。

你可能会喜欢：