调整方法三：使用存储过程的批量更新(1)

       调整方法三：使用存储过程的批量更新

背景知识简介

l         Nested Table简介

语法：

TYPE type_name IS TABLE OF type [NOT NULL];

简单的来说上述语句定义的类型就称为Nested Table。

Nested Table可以保存任意数目的元素，可以作为数据库表中的一个列。

l         FORALL和BULK COLLECT简介

众所周知，PL/SQL与底层的SQL引擎紧密集成在一起，PL/SQL引擎执行一段PL/SQL代码的时候会把SQL语句传送到SQL引擎执行，需要的话SQL引擎会把执行结果返回到PL/SQL引擎。在PL/SQL引擎和SQL引擎之间的切换称为上下文切换。如下图所示：

每一次的上下文切换均会引起额外的数据库负载而造成性能的损失。8i后Oracle提供了两种技术把多次上下文切换组合为一次上下文切换从而提高性能。这两种技术就是FORALL和BULK COLLECT。

FORALL把多个DML语句与数据捆绑到一个集合中一次性提交到SQL引擎，基本语法：

FORALL <索引> IN 低边界 ... 高边界

DML语句;

BULK COLLECT把游标的数据一次性提取到集合中，基本语法：

FETCH BULK COLLECT INTO 集合1[,集合2]……;

下面是使用FORALL和BULK COLLECT的一个例子：

SQL> drop table t5

  2  /

表已丢弃。

已用时间:  00: 00: 00.00

SQL> create table t5

  2  (n1 number)

  3  /

表已创建。

已用时间:  00: 00: 00.00

SQL>

SQL> declare

  2    cursor cur_1 is select object_id from dba_objects;

  3  begin

  4    for c1 in cur_1 loop

  5      insert into t5 values(c1.object_id);

  6    end loop;

  7    commit;

  8  end;

  9  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.06

SQL>

SQL>

SQL> declare

  2    type n_type is table of number;

  3    v_n_type n_type;

  4    cursor cur_1 is select object_id from dba_objects;

  5    v_rowcount number;

  6  begin

  7    open cur_1;

  8    fetch cur_1 bulk collect into v_n_type;

  9    v_rowcount := cur_1%rowcount;

 10    close cur_1;

 11    forall i in 1..v_rowcount

 12      insert into t5 values(v_n_type(i));

 13    commit;

 14  end;

 15  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.04

SQL>

我们看到使用了FORALL/BULK COLLECT后PL/SQL块的执行效率远远优于之前的效率。