Oracle PL/SQL BULK COLLECT：FORALL 示例

什么是 BULK COLLECT？

批量收集减少了上下文切换 SQL 以及 PL/SQL 引擎，并允许 SQL 引擎一次性获取记录。

Oracle PL / SQL 提供批量获取记录的功能，而不是逐条获取。此 BULK COLLECT 可用于 SELECT 语句中，以批量填充记录或获取单个记录。 光标 批量操作。由于 BULK COLLECT 会批量获取记录，因此 INTO 子句必须始终包含集合类型的变量。使用 BULK COLLECT 的主要优势在于，它通过减少数据库和 PL/SQL 引擎之间的交互来提高性能。

语法：

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

在上述语法中，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) 参数，该参数返回 FORALL 语句中第 n 个 DML 语句所影响的行数，即它统计 FORALL 语句中集合变量中每个值所影响的记录数。参数“n”表示需要统计行数的集合中值的序号。

例如1： 在这个例子中，我们将使用 BULK COLLECT 从 emp 表中导出所有员工的姓名，并且我们还将使用 FORALL 将所有员工的工资增加 5000。

下面的屏幕截图显示了 BULK COLLECT 和 FORALL 示例及其输出。 Oracle.

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 salary=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

Code 说明：

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

常见问题