概述

BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、 FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。

语法

FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <collection_name>
LIMIT <numeric_expression>;
or
FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <array_name>
LIMIT <numeric_expression>;

Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。

采用bulk collect可以将查询结果一次性地加载到collections中。

而不是通过cursor一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意在使用bulk collect时，所有的into变量都必须是collections

实例：BULK COLLECT将得到的结果集绑定到记录变量中

DECLARE
 TYPE emp_rec_type IS RECORD --声明记录类型
 ( 
 empno emp.empno%TYPE 
 ,ename emp.ename%TYPE
 ,hiredate emp.hiredate%TYPE 
); 
 TYPE nested_emp_type IS TABLE OF emp_rec_type; --声明记录类型变量
 emp_tab nested_emp_type;
 
BEGIN
 SELECT empno, ename, hiredate BULK COLLECT INTO emp_tab --使用BULK COLLECT 将所得的结果集一次性绑定到记录变量emp_tab中
 FROM emp;
 
 FOR i IN emp_tab.FIRST .. emp_tab.LAST 
 LOOP
 DBMS_OUTPUT.put_line('Current record is '||emp_tab(i).empno||chr(9)||emp_tab(i).ename||chr(9)||emp_tab(i).hiredate); 
 END LOOP;
END; 
/

实验：使用LIMIT限制FETCH数据量

在使用BULK COLLECT 子句时，对于集合类型，如嵌套表，联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK

COLLECT子句操作集合，则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。

DECLARE 
 CURSOR emp_cur IS SELECT empno, ename, hiredate FROM emp;
 TYPE emp_rec_type IS RECORD 
 ( 
 empno emp.empno%TYPE 
 ,ename emp.ename%TYPE
 ,hiredate emp.hiredate%TYPE
 ); 
 TYPE nested_emp_type IS TABLE OF emp_rec_type; -->定义了基于记录的嵌套表 
 
 emp_tab nested_emp_type; -->定义集合变量，此时未初始化 
 v_limit PLS_INTEGER := 5; -->定义了一个变量来作为limit的值 
 v_counter PLS_INTEGER := 0; 
 
BEGIN 
 OPEN emp_cur; 
LOOP 
 FETCH emp_cur BULK COLLECT INTO emp_tab -->fetch时使用了BULK COLLECT子句 
 LIMIT v_limit; -->使用limit子句限制提取数据量 
 EXIT WHEN emp_tab.COUNT = 0; -->注意此时游标退出使用了emp_tab.COUNT，而不是emp_cur%notfound 
 v_counter := v_counter + 1; -->记录使用LIMIT之后fetch的次数 
 FOR i IN emp_tab.FIRST .. emp_tab.LAST 
 LOOP 
 DBMS_OUTPUT.put_line( 'Current record is '||emp_tab(i).empno||CHR(9)||emp_tab(i).ename||CHR(9)||emp_tab(i).hiredate); 
 END LOOP; 
END LOOP; 
CLOSE emp_cur; 
DBMS_OUTPUT.put_line( 'The v_counter is ' || v_counter ); 
END;
/

实验：RETURNING 子句的批量绑定

BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，我们 需要使用RETURNING子句来实现批量绑定。

DECLARE 
 TYPE emp_rec_type IS RECORD 
 ( 
 empno emp.empno%TYPE 
 ,ename emp.ename%TYPE 
 ,hiredate emp.hiredate%TYPE 
 ); 
 
 TYPE nested_emp_type IS TABLE OF emp_rec_type; 
 
 emp_tab nested_emp_type; 
-- v_limit PLS_INTEGER := 3; 
-- v_counter PLS_INTEGER := 0; 
BEGIN 
 DELETE FROM emp WHERE deptno = 20 
 RETURNING empno, ename, hiredate -->使用returning 返回这几个列 
 BULK COLLECT INTO emp_tab; -->将前面返回的列的数据批量插入到集合变量 
 
 DBMS_OUTPUT.put_line( 'Deleted ' || SQL%ROWCOUNT || ' rows.' ); 
 COMMIT; 
 
 IF emp_tab.COUNT > 0 THEN -->当集合变量不为空时，输出所有被删除的元素 
 FOR i IN emp_tab.FIRST .. emp_tab.LAST 
 LOOP 
 DBMS_OUTPUT. 
 put_line( 
 'Current record ' 
 || emp_tab( i ).empno 
 || CHR( 9 ) 
 || emp_tab( i ).ename 
 || CHR( 9 ) 
 || emp_tab( i ).hiredate 
 || ' has been deleted' ); 
 END LOOP; 
 END IF; 
END; 
/

篇幅有限，关于bulk collect批量绑定方面的内容就介绍到这了，大家可以试着对存储过程中loop循环中的DML语句做适当改写，看是不是效率上有一定提升。实际上最好的应该是FORALL与BULK COLLECT结合使用，可以极大的提高执行效率。

后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！