oracle的forall,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_boundindex_collection5 }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 BYBINARY_INTEGER;5 tb_table tb_table_type;i IN 1..100LOOP8

tb_table(i).id:=i;i;10 ENDLOOP;..tb_table.tmp_tab VALUES tb_table(i);14 END;

TYPE tb_table_type tmp_tab%rowtype INDEX BYBINARY_INTEGER;4 tb_table tb_table_type;i IN 1..100LOOP7

tb_table(i).id:=i;i;9 ENDLOOP;tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id;12 END;

TYPE tb_table_typetmp_tab%rowtype INDEX BYBINARY_INTEGER;

tb_table tb_table_type;..10LOOP

tb_table(i).id:=i;

tb_table(i).name:i;ENDLOOP;

FORALL itmp_tab WHERE id =tb_table(i).id;END;

示例2，使用INDICES OF子句：

TYPE demo_table_type tmp_tab%rowtype INDEX BYBINARY_INTEGER;3 demo_table demo_table_type;i IN 1..10LOOP6

demo_table(i).id:=i;i;8 ENDLOOP;demo_table.delete(3);11 demo_table.delete(6);12 demo_table.delete(9);13 FORALL i IN INDICES OFdemo_tabletmp_tab VALUESdemo_table(i);15 END ;

示例3，使用VALUES OF子句：

TYPE index_poniter_type pls_integer;3 index_poniter index_poniter_type;tmp_tab%rowtype INDEX BYBINARY_INTEGER;5 demo_table demo_table_type;index_poniter := index_poniter_type(1,3,5,7);..10LOOP9

demo_table(i).id:=i;i;11 ENDLOOP;index_ponitertmp_tab VALUESdemo_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

示例：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！