Oracle数据库之FORALL与BULK COLLECT语句

我们再来看一下PL/SQL块的执行过程：当PL/SQL运行时引擎处理一块代码时，它使用PL/本文来源[email protected]搞@^&代*@码)网5SQL引擎来执行过程化的代码，而将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_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 BY BINARY_INTEGER; 5 tb_table tb_table_type;i IN 1..100 LOOP 8tb_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 7tb_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 LOOPtb_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 6demo_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_tabletmp_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 9demo_table(i).id:=i;i;11 END LOOP; index_ponitertmp_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

示例：

喜欢 (0)赏

[搞代码]

分享 (0)