Oracle PL/SQL BULK COLLECT：FORALL 示例

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

BULK COLLECT 是什么？

BULK COLLECT 减少了 SQL 和 PL/SQL 引擎之间的上下文切换，并允许 SQL 引擎一次性获取记录。

Oracle PL/SQL 提供了批量获取记录的功能，而不是逐条获取。此 BULK COLLECT 可用于“SELECT”语句以批量填充记录，或用于批量获取游标。由于 BULK COLLECT 批量获取记录，因此 INTO 子句应始终包含集合类型变量。使用 BULK COLLECT 的主要优点是通过减少数据库和 PL/SQL 引擎之间的交互来提高性能。

语法

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

在上面的语法中，BULK COLLECT 用于从“SELECT”和“FETCH”语句收集数据。

FORALL 子句

FORALL 允许批量执行 DML 操作。它类似于 FOR 循环语句，但 FOR 循环是记录级别的，而 FORALL 没有 LOOP 概念。相反，给定范围内的所有数据同时被处理。

语法

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

在上面的语法中，给定的 DML 操作将针对存在于低范围和高范围之间的所有数据执行。

LIMIT 子句

BULK COLLECT 概念将所有数据批量加载到目标集合变量中，即所有数据一次性填充到集合变量中。但当要加载的总记录非常大时，这并不推荐，因为当 PL/SQL 尝试加载所有数据时，它会消耗更多的会话内存。因此，最好限制此 BULK COLLECT 操作的大小。

但是，通过在“SELECT”语句中引入 ROWNUM 条件，可以轻松实现此大小限制，而在游标的情况下则无法实现。

为了解决这个问题，Oracle 提供了“LIMIT”子句，用于定义批量中需要包含的记录数。

语法

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

在上面的语法中，游标的 FETCH 语句使用了 BULK COLLECT 语句和 LIMIT 子句。

BULK COLLECT 属性

与游标属性类似，BULK COLLECT 具有 %BULK_ROWCOUNT(n)，它返回 FORALL 语句的第 n 个 DML 语句中受影响的行数，即它将给出 FORALL 语句中为集合变量中的每个单独值所影响的记录数。“n”表示集合中需要行数的序列。

示例 1：在此示例中，我们将使用 BULK COLLECT 从 emp 表中投影所有员工姓名，并使用 FORALL 将所有员工的工资提高 5000。

BULK COLLECT Attributes

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;	
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

相关文章

输出

Employee Fetched:BBB
Employee Fetched:XXX 
Employee Fetched:YYY
Salary Updated

代码解释

  • 代码行 2：声明用于语句“SELECT emp_name FROM emp”的游标 guru99_det。
  • 代码行 3：声明 lv_emp_name_tbl 为 VARCHAR2(50) 的表类型
  • 代码行 4：声明 lv_emp_name 为 lv_emp_name_tbl 类型。
  • 代码行 6：打开游标。
  • 代码行 7：使用 BULK COLLECT 和 LIMIT 大小为 5000 将游标获取到 lv_emp_name 变量中。
  • 代码行 8-11：设置 FOR 循环以打印集合 lv_emp_name 中的所有记录。
  • 代码行 12：使用 FORALL 将所有员工的工资更新为 5000。
  • 代码行 14：提交事务。