Oracle PL/SQL BULK COLLECT：FORALL 示例

什么是 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 操作。

限制条款

批量收集概念将整个数据批量加载到目标集合变量中，即整个数据将一次性填充到集合变量中。但是，当需要加载的总记录非常大时，这是不可取的，因为当 PL / SQL 尝试加载整个数据会消耗更多会话内存。因此，限制此批量收集操作的大小总是好的。

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

为了克服这个问题 Oracle 提供了“LIMIT”子句来定义需要包含在批量中的记录数。

语法：

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

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

BULK COLLECT 属性

像 光标 属性 BULK COLLECT 具有 %BULK_ROWCOUNT(n)，它返回 n 中受影响的行数^th FORALL 语句的 DML 语句，即它将给出集合变量中每个单个值在 FORALL 语句中受影响的记录数。术语“n”表示集合中需要行计数的值的顺序。

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

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 intl lv_emp_name 变量。
• 代码行8-11： 设置 FOR 循环来打印集合 lv_emp_name 中的所有记录。
• 代码第 12 行： 使用 FORALL 将所有员工的工资更新 5000。
• 代码第 14 行： 提交交易。