Oracle——Forall 与 Bluk Collect语句
PL/SQL块的执行过程：
当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换（context switch）。每发生一次交换，就会带来一定的额外开销。
- FORALL，用于增强PL/SQL引擎到SQL引擎的交换，
使用FORALL，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销。
- BULK COLLECT，用于增强SQL引擎到PL/SQL引擎的交换，
BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。
通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。
FORALL语句介绍
1.语法
FORALL index_name IN { lower_bound .. upper_bound | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ] | VALUES OF index_collection insert/update/delete .....; } [SAVE EXCEPTIONS] dml_statement;
说明1：
- 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：
做循环的记数器时，可以使用pls_integer. pls_interger,binary_integer,number中，pls_integer速度最快。
Binary_Integer 与 Pls_Integer 都是整型类型.
Binary_Integer类型变量值计算是由Oracle来执行，不会出现溢出，但是执行速度较慢，因为它是由Oracle模拟执行。
Pls_Integer的执行是由硬件即直接由CPU来运算，因而会出现溢出，但其执行速度较前者快许多。
实例：
1.新建一张示例表
CREATE TABLE tmp_tab( id NUMBER(5), name VARCHAR2(50) );
2.使用FORALL批量插入、修改、删除数据
declare type tb_table_type is table of tmp_tab%rowtype index by binary_integer; --number类型下标自增长 tb_table tb_table_type; begin for i in 1..100 loop tb_table(i).id:=i; tb_table(i).name:='NAME'||i; end loop; forall i in 1..tb_table.count --insert into tmp_tab values tb_table(i); --update tmp_tab a set a.name = tb_table(i).name||'哈哈' where a.id = tb_table(i).id; delete tmp_tab a where a.id = tb_table(i).id; end;
commit;
3.使用INDICES OF子句
declare type demo_table_type is table of tmp_tab%rowtype index by binary_integer; demo_table demo_table_type; begin for i in 1..10 loop demo_table(i).id:=i; demo_table(i).name:='NAME'||i; end loop; --使用集合的delete方法移除id为3，6，9的成员 demo_table.delete(3); demo_table.delete(6); demo_table.delete(9); forall i in indices of demo_table insert into tmp_tab values demo_table(i); end;
commit;
4.使用value of 子句
declare type index_poniter_type is table of pls_integer; index_poniter index_poniter_type; type demo_table_type is table of tmp_tab%rowtype index by binary_integer; demo_table demo_table_type; begin index_poniter := index_poniter_type(1,3,5,7); for i in 1..10 loop demo_table(i).id:=i; demo_table(i).name:='NAME'||i; end loop; forall i in values of index_poniter insert into tmp_tab values demo_table(i); end;
FORALL注意事项
使用FORALL时，应该遵循如下规则：
- FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。
- 不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。
- 这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。
- lower_bound和upper_bound之间是按照步进 1 来递增的。
- 在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。
- 在sql_statement中使用的集合，下标不能使用表达式。
BULK COLLECT 语句介绍
1.在SELECT INTO中使用BULK COLLECT
DECLARE TYPE tmp_rec_type IS RECORD ( id tmp_tab.id%TYPE, name tmp_tab.name%TYPE ); TYPE nested_tmp_type IS TABLE OF tmp_rec_type; tmp_tab1 nested_tmp_type; BEGIN --使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中 SELECT id,name BULK COLLECT INTO tmp_tab1 FROM tmp_tab; FOR i IN tmp_tab1.FIRST .. tmp_tab1.LAST LOOP DBMS_OUTPUT.PUT_LINE('当前记录： ' ||tmp_tab1(i).id||chr(9) ||tmp_tab1(i).name); END LOOP; END;
说明：使用BULK COLLECT一次即可提取所有行并绑定到记录变量，这就是所谓的批量绑定。
在FETCH INTO中使用BULK COLLECT
语法：
FETCH ... BULK COLLECT INTO ...[LIMIT row_number];
在使用BULK COLLECT子句时，对于集合类型会自动对其进行初始化以及扩展。
因此如果使用BULK COLLECT子句操作集合，则无需对集合进行初始化以及扩展。
由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此可以使用LIMIT子句来限制一次提取的数据量。
LIMIT子句只允许出现在FETCH操作语句的批量中。
declare cursor tmp_cur is select id,name from tmp_tab; type tmp_rec_type is record ( id tmp_tab.id%type, name tmp_tab.name%type ); type nested_tmp_type is table of tmp_rec_type; tmp_tab1 nested_tmp_type; v_limit pls_integer := 2; v_counter pls_integer := 0; begin open tmp_cur; loop fetch tmp_cur bulk collect into tmp_tab1 limit v_limit; -- 使用limit子句限制每一次提取的数据量 exit when tmp_tab1.count = 0; -- 注意此时游标退出使用了emp_tab.count，而不是emp_cur%notfound --exit when tmp_cur%notfound; v_counter := v_counter + 1; for i in tmp_tab1.first .. tmp_tab1.last loop dbms_output.put_line( '当前记录： ' ||tmp_tab1(i).id||chr(9) ||tmp_tab1(i).name); end loop; end loop; close tmp_cur; dbms_output.put_line( '总共获取次数为：' || v_counter ); end;
在RETURNING INTO中使用BULK COLLECT
BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，需要使用RETURNING子句来实现批量绑定。
declare type tmp_rec_type is record ( id tmp_tab.id%type, name tmp_tab.name%type ); type nested_tmp_type is table of tmp_rec_type; tmp_tab1 nested_tmp_type; begin delete from tmp_tab where id = 1 returning id,name -- 使用returning 返回这几个列 bulk collect into tmp_tab1; -- 将返回的列的数据批量插入到集合变量 dbms_output.put_line( '删除 ' || sql%rowcount || ' 行记录' ); commit; if tmp_tab1.count > 0 then -- 当集合变量不为空时，输出所有被删除的元素 for i in tmp_tab1.first .. tmp_tab1.last loop dbms_output.put_line('当前记录：' || tmp_tab1( i ).id || chr( 9 ) || tmp_tab1( i ).name || ' 已被删除' ); end loop; end if; end;
BULK COLLECT的注意事项
- BULK COLLECT INTO 的目标对象必须是集合类型。
- 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
- 不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
- 复合目标(如对象类型)不能在RETURNING INTO子句中使用。
- 如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。
- 如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中。
FORALL与BULK COLLECT综合运用
FORALL与BULK COLLECT是实现批量SQL的两个重要方式，我们可以将其结合使用以提高性能。
declare type mycur is ref cursor; lv_cur mycur; type tab_type is table of tablea%rowtype; lv_tt tab_type; begin open lv_cur for select * from tablea; while (true) loop fetch lv_cur bulk collect into lv_tt limit 1000; forall i in 1 .. lv_tt.count --lv_tt.first ..lv_tt.last insert /*+ parallel(8)*/ into tableb values (select * from tablea); commit; exit when lv_cur%notfound; end loop; close lv_cur; end;
