PL/SQL批处理语句（BULK COLLECT子句和FORALL语句）

olnm

Oracle为PL/SQL中的SQL相关功能提供了FORALL语句和BULK COLLECT子句，显著的增强了SQL相关功能。这两个语句一起被称作PL/SQL的批处理语句。Oracle为什么要提供这两个语句呢？我们首先了解一下PL/SQL的引擎。该引擎可以安装在数据库，或者应用开发工具上，例如Oracle Froms。当PL/SQL运行引擎执行一个代码块时，引擎本身只会处理过程语句，而SQL语句是发送给SQL引擎执行。SQL语句的执行时是由数据库的SQL引擎负责，再将执行结果返回给PL/SQL引擎。

以下是PL/SQL引擎运行原理：

这种PL/SQL引擎和SQL引擎之间的控制转移叫做上下文切换。每次发生切换时，都会有额外的开销。通过FORALL语句和BULK COLLECT子句，可以把两个引擎的通行进行微调，让PL/SQL更有效地把多个上下文切换压缩成一个切换，从而提升程序的性能。

1.通过BULK COLLECT加速查询

不管是显示游标还是隐式游标，都可以通过BULK COLLECT在数据库的单次交互中获取多行数据。BULKCOLLECT减少了PL/SQL引擎和SQL引擎之间的切换次数，因此也减少了提取数据时的额外开销。

创建一张测试数据表：create table my_objects as select * from user_objects;

现在需要从my_objects表提取所有数据,我们通常的做法如下：

--FOR游标提取据

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object   nt_object := nt_object(); --初始化
  v_count      number := 0;
  c_big_number number := power(2, 31);
  l_start_time pls_integer;
begin
  dbms_output.put_line('========FOR游标提取==========');
  l_start_time := dbms_utility.get_time;

  for vrt_object in (select * from my_objects) loop
    vnt_object.extend;
    vnt_object(vnt_object.last) := vrt_object;
  end loop;
  
  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));
end;

--显示游标提取

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object   nt_object := nt_object(); --初始化
  c_big_number number := power(2, 31);
  l_start_time pls_integer;

  cursor cur_object is
    select * from my_objects;
  vrt_object cur_object%rowtype;

begin

  dbms_output.put_line('========显示游标提取==========');
  l_start_time := dbms_utility.get_time;

  open cur_object;
  loop
    fetch cur_object
      into vrt_object;
    exit when cur_object%notfound;
    vnt_object.extend;
    vnt_object(vnt_object.last) := vrt_object;
  end loop;

  close cur_object;
  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));
end;

结果：FOR游标明显要优于显示游标
注意：要使用集合嵌套表，必须初始化。

这个代码毫无疑问可以完成任务，不过可能会花费很长的时间。假设my_objects表中有1000个记录，PL/SQL引擎就要向SGA中的游标发送10000个fetch操作。

为了帮组这种场景，可以在查询语句中的INTO元素中使用BULK COLLECT子句。对于游标使用这个子句是告诉SQL引擎把查询出来的多行数据库批量绑定到指定的集合上。然后再把控制返回给PL/SQL引擎。这个子句的语法是：

... BULK COLLECT INTO collection_name[,collection_name] ...

其中collection_name代表一个集合。

使用BULK COLLECT时，要记住以下这些规则和限制：

• 在Oracle 9i数据之前，只能在静态SQL中使用BULK COLLECT。现在不论是动态还是静态SQL都可以使用BULK COLLECT。
• 可以在下面这些语句中使用BULK COLLECT：SELECT INTO，FETCH INTO和RETURNING INTO。
• 对于在BULK COLLECT子句中使用的集合，SQL引擎会自动进行初始化及扩展。它会从索引1开始填充集合，连续的插入元素（紧凑的），把之前已经使用的元素的值覆盖。
• 不能在FORALL语句中使用SELECT...BULK COLLECT语句。
• 如果SELECT...BULK COLLECT没有找到任何行，不会抛出NO_DATA_FOUND异常。相反，我们必须对集合的内容进行检查看看其中到底有没有数据。
• 如果查询没有返回任何行，集合的COUNT方法将返回0。
  

1.1使用隐式游标

使用隐式游标(SELECT INTO)重写，并使用dbms_utility.get_time获取时间。

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object   nt_object; --未初始化
  c_big_number number := power(2, 31);
  l_start_time pls_integer;

begin
  dbms_output.put_line('========BULK COLLECT批量提取==========');
  l_start_time := dbms_utility.get_time;

  select * bulk collect into vnt_object from my_objects;

  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));
end;

1.2使用显示游标

使用显示游标重写：

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object   nt_object := nt_object(); --初始化
  c_big_number number := power(2, 31);
  l_start_time pls_integer;

  cursor cur_object is
    select * from my_objects;
begin

  dbms_output.put_line('========显示游标BULK COLLECT提取==========');
  l_start_time := dbms_utility.get_time;

  open cur_object;
  fetch cur_object bulk collect
    into vnt_object;
  close cur_object;

  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));
end;

1.3限制BULK COLLECT提取数据

Oracle为BULK COLLECT提供了一个LIMIT子句，让我们可以对从数据库提取的行的数量做限制，语法是：
   FETCH cursor BULK COLLECT INTO ... [LIMIT rows]

其中rows可是直接量、变量或者求值的结果是整数的表达式。

对于BULK COLLECT来说，LIMIT是非常有用的，因为这个语句可以帮助我们控制程序用多大内存来处理数据。比如，假设你需要查询并处理10000行的数据。你可以用BULK COLLECT一次取出所有的行，然后填充到一个非常大的集合中。可是，这种方法会消耗掉该会话的大量PGA内存。如果这个代码被多个Oracle模式运行，你的应用程序性能就可能会因为PGA换页而下降。

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object_bulk nt_object;
  vnt_object      nt_object := nt_object(); --初始化

  c_big_number number := power(2, 31);
  l_start_time pls_integer;

  cursor cur_object is
    select * from my_objects;
begin

  dbms_output.put_line('========显示游标BULK COLLECT LIMIT提取==========');
  l_start_time := dbms_utility.get_time;

  open cur_object;
  loop
    fetch cur_object bulk collect
      into vnt_object_bulk limit 100;
    for i in vnt_object_bulk.first .. vnt_object_bulk.last loop
      vnt_object.extend;
      vnt_object(vnt_object.last) := vnt_object_bulk(i);
    end loop;
    exit when cur_object%notfound;
  end loop;
  close cur_object;

  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));

end;

注意：这里是在循环的最后通过检查cur_object%notfound的值来结束循环。当每次只查询一条数据时，总是把这个代码紧跟在FETCH语句的后面。不过使用BULK COLLECT时就不能这么做了，因为当FETCH操作提取最后一部分数据集之后，游标虽然空了（%NOTFOUND会返回TRUE）但是在集合中还有一些元素需要处理。因此，或者在循环的最后检查%NOTFOUND属性，或者在FETCH操作之后立即查看集合的内容：

open cur_object;
  loop
    fetch cur_object bulk collect
      into vnt_object_bulk limit 100;
    exit when vnt_object_bulk.count = 0;

和在循环体的最后检查%NOTFOUND属性值比较起来，第二中方法的不好之处就在于我们需要额外再执行一个返回空行的FETCH操作。

2.通过FORALL加速DML

BULK COLLECT用于对查询加速。而FORALL会对插入、更新、删除以及合并做同样的事情（只有Oracle 11g才支持FORALL的合并）。FORALL告诉PL/SQL引擎要先把一个或者多个集合的所有成员都绑定到SQL语句中，然后在把语句发送给SQL引擎。

2.1FORALL语句的语法

尽管FORALL语句带有一个迭代模式，但它并不是一个FOR循环。因此，既不需要LOOP也不需要END LOOP语句。它的语法如下：

FORALL index IN
   [lower.bound .. upper.bound |
    INDICES OF indexing_collection |
    VALUES OF indexing_collection
   ]
   [SAVE EXCEPTIONS]
   sql_statement;

其中：

index

   是一个整数，由Oracle隐式声明的，并被定义做集合的索引值。

lower_bound

   操作开始的索引值。

upper_bound

   操作结束的索引值。

sql_statement

   将对每一个集合元素执行的SQL语句。

indexing_collection

   这是一个PL/SQL集合，是一个指向sql_statement所使用的绑定数组的索引的集合。INDICES OF和VALUES OF是从Oracle 10g才有的。

SAVE EXCEPTIONS

   这是一个可选的子句，告诉FORALL处理全部行，不过把发生的任何异常保存下来。

使用FORALL时，必须遵守这些规则：

• FORALL语句的主体必须是一个单独的DML语句——可以是一个插入、更新、删除或者合并操作（Oracle 11g及以后版本）。
• 上边界和下边界对于SQL语句所使用的集合来说，必须是一个有效的连续索引值范围。
• DML语句中使用的集合下标不能是表达式。
  

2.2FORALL批量插入

从user_objects数据字典中中批量将所有数据插入到my_objects表中。

declare
  type nt_object is table of my_objects%rowtype;
  vnt_object   nt_object;
  c_big_number number := power(2, 31);
  l_start_time pls_integer;
begin

  dbms_output.put_line('========批量插入==========');
  l_start_time := dbms_utility.get_time;

  select * bulk collect into vnt_object from user_objects;
  forall i in vnt_object.first .. vnt_object.last
    insert into my_objects values vnt_object (i);

  dbms_output.put_line('count=' || vnt_object.count);
  dbms_output.put_line('Elapsed: ' ||
                       to_char(mod(dbms_utility.get_time - l_start_time +
                                   c_big_number,
                                   c_big_number)));

end;

提示：在Oracle 10g及以后的版本中，PL/SQL编辑会自动对FOR游标循环进行优化，从而性能可以和BULK COLLECT相媲美。

参考：

Oracle PL/SQL程序设计（第五版） Steven Feuersterin & Bill Pribyl著 张晓明译

zhangpengfei00

我不说，你不懂，这便是距离。