Oracle数据库之FORALL与BULK COLLECT语句

我们再来看一下PL/SQL块的执行过程：当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给

我们再来看一下PL/SQL块的执行过程：当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换（context switch）。每发生一次交换，就会带来一定的额外开销。

FORALL，用于增强PL/SQL引擎到SQL引擎的交换。

BULK COLLECT，用于增强SQL引擎到PL/SQL引擎的交换。（前面我们已经介绍过了）

1. FORALL介绍

使用FORALL，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销。下面是 FORALL 的一个示意图：

语法：

1 FORALL index_name IN 2 { lower_bound .. upper_bound index_collection 5 } dml_statement;

说明：

index_name：一个无需声明的标识符，作为集合下标使用。

lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。

INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值。

VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。

2. FORALL的使用

示例所使用表结构：

tmp_tab( 2 id NUMBER(5), 3 name VARCHAR2(50) 4 );

示例1，使用FORALL批量插入、修改、删除数据：

TYPE tb_table_type tmp_tab%rowtype INDEX BY BINARY_INTEGER; 5 tb_table tb_table_type; i IN 1..100 LOOP 8 tb_table(i).id:=i; i; 10 END LOOP; ..tb_table. tmp_tab VALUES tb_table(i); 14 END;

TYPE tb_table_type tmp_tab%rowtype INDEX BY BINARY_INTEGER; 4 tb_table tb_table_type; i IN 1..100 LOOP 7 tb_table(i).id:=i; i; 9 END LOOP; tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id; 12 END;

TYPE tb_table_type tmp_tab%rowtype INDEX BY BINARY_INTEGER; tb_table tb_table_type; ..10 LOOP tb_table(i).id:=i; tb_table(i).name:i; END LOOP; FORALL i tmp_tab WHERE id =tb_table(i).id; END;

示例2，使用INDICES OF子句：

TYPE demo_table_type tmp_tab%rowtype INDEX BY BINARY_INTEGER; 3 demo_table demo_table_type; i IN 1..10 LOOP 6 demo_table(i).id:=i; i; 8 END LOOP; demo_table.delete(3); 11 demo_table.delete(6); 12 demo_table.delete(9); 13 FORALL i IN INDICES OF demo_table tmp_tab VALUES demo_table(i); 15 END ;

示例3，使用VALUES OF子句：

TYPE index_poniter_type pls_integer; 3 index_poniter index_poniter_type; tmp_tab%rowtype INDEX BY BINARY_INTEGER; 5 demo_table demo_table_type; index_poniter := index_poniter_type(1,3,5,7); ..10 LOOP 9 demo_table(i).id:=i; i; 11 END LOOP; index_poniter tmp_tab VALUES demo_table(i); 14 END;

3. FORALL注意事项

使用FORALL时，应该遵循如下规则：

4. BULK COLLECT介绍

BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。

通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法。

5. BULK COLLECT的使用

5.1 在SELECT INTO中使用BULK COLLECT

示例：