BULK COLLECT INTO

从Oracle8i开始，就引入了一个非常有用的特性，大大提高了查询的效率，它就是：BULK COLLECT子句。

使用BULK COLLECT，你可以通过一个显式或隐式游标，只需循环一次就能从数据库中取出多行记录。BULK COLLECT减少了PL/SQL和SQL引擎之间的上下文交换，因此减少了检索数据的开销。

特别是对于某些时候，我们需要使用游标一条记录、一条记录地处理，就可能使用了多次循环。在这种情况，我们就可以使用批量集合，一次性地把记录取出，放入一个集合变量中，然后再对该变量进行处理，减少系统的开销。

下面我们来看两个范例，一个是使用BULK COLLECT的，一个没有：

范例1、使用多次循环来检索并显示数据。

 

范例2、一次性获取数据，然后再循环显示。

下面是一些有关BULK COLLECT的建议：

1、从Oracle9i开始，你都可以在静态SQL和动态SQL中使用BULK COLLECT。

2、你可以在任何地方的SELECT INTO，FETCH INTO，和RETURNING INTO子句中使用BULK COLLECT关键字。

3、在Oracle9i以前，你引用的集合变量只能够存储标量值（字符串、数字和日期）。换句话说，你不能提取一条记录存放到一个记录型的数据结构中（这个数据结构是一个集合变量中的一行记录）。

4、SQL引擎会自动地初始化和扩展你在BULK COLLECT子句中引用的集合变量。它会从索引1开始，连续地把记录填充进集合中，并且会重写任何以前定义了的数据。

5、你不能在一个FORALL语句中使用SELECT...BULK COLLECT语句。

6、如果找不到任何记录，SELECT...BULK COLLECT也不会产生NO_DATA_FOUND异常。相应地，你必须检查集合的内容，以察看里面是否有数据。

7、在执行查询以前，BULK COLLECT操作会清空在INTO子句中引用的集合。如果查询没有返回任何记录，这个集合的COUNT方法就会返回0。

 

 

如果对于Oracle9i或以上的版本，我们把上面的例子写得更简单一些：

create or replace procedure Bulk_Demo2(i_deptno in number) is

  type t_tbl1 is table of emp%ROWTYPE index by binary_integer;

  emp_info1 t_tbl1;

 

  type emp_rec is record(

    empno emp.empno%type,

    ename emp.ename%type);

  type t_tbl2 is table of emp_rec index by binary_integer;

  emp_info2 t_tbl2;

 

begin

  Select t.* BULK COLLECT

    INTO emp_info1

    From emp t

   Where t.deptno = i_deptno;

  Dbms_Output.put_line('------批量存放整行记录类型-------');

  For i in 1 .. emp_info1.count Loop

    Dbms_Output.put_line(emp_info1(i).empno || ' ' || emp_info1(i)

                         .ename || ' ' || emp_info1(i)

                         .job || ' ' || emp_info1(i)

                         .sal || ' ' || emp_info1(i).hiredate);

  End Loop;

 Dbms_Output.put_line('+++++++++++++++++++++++++++++++++++++++');

  Dbms_Output.put_line('------批量存放自定义记录类型-------');

  select t.empno, t.ename BULK COLLECT

    INTO emp_info2

    From emp t

   Where t.deptno = i_deptno;

  For i in 1 .. emp_info2.count Loop

    Dbms_Output.put_line(emp_info2(i).empno ||' '|| emp_info2(i).ename);

  End Loop;

  Dbms_Output.put_line('++++++++++++++++++++++++++++++++++++++');

end BULK_demo2;

运行结果：

1.1.1.1 限制BULK COLLECT提取的记录数目

Oracle对于BULK COLLECT提供了一个LIMIT关键字来让你限制从数据库检索出来的记录数目。语法如下：

FETCH cursor BULK COLLECT INTO ... [LIMIT rows];

这里的rows参数可以是一个字面值，变量或者是表达式——都指向一个整数（不然的话，Oracle将会给出一个VALUE_ERROR的异常信息）。

LIMIT对于BULK COLLECT来说是非常有用的。它帮助你清楚地了解并管理程序所用到过程数据。比如说，你要精确地查询并处理1000条记录。你可以使用BULK COLLECT来取出这些记录，并且残生了一个相对较大的集合。但是这种方法会消耗许多PGA（全局进程区）内存。如果这个代码是被很多独立的会话使用，那么你的应用就会因为PGA频繁的页面交换而效率低下。

下面的这段代码（在Oracle9i的HR模式下），在FETCH语句中使用了LIMIT语句。请注意，这里使用了集合的COUNT方法来确认是否有数据被取出。而%FOUND和%NOTFOUND属性在这里就不能用来检测是否有数据被取出。

create or replace procedure LimitDemo is

  CURSOR allrows_cur IS

    SELECT employee_id FROM employees;

  TYPE employee_aat IS TABLE OF

      employees.employee_id%Type INDEX BY BINARY_INTEGER;

  l_employees employee_aat; 

begin

  OPEN allrows_cur;

  Loop

    EXIT WHEN allrows_cur%NOTFOUND;

    FETCH allrows_cur BULK COLLECT

      INTO l_employees LIMIT 100;

    Dbms_Output.put_line(l_employees.count);

  end loop;

  close allrows_cur;

end LimitDemo;

获得结果是：

p1.JPG

p2.JPG

p3.JPG

p5.JPG

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-740608/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27042095/viewspace-740608/