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）。每发生一次交换，就会带来一定的额外开销。
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
示例：