批量SQL之 BULK COLLECT 子句

3121321321

    BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
    有关FORALL语句的用法请参考：批量SQL之 FORALL 语句



一、BULK COLLECT批量绑定的示例


    --下面的示例中使用了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;  
    --上面的例子可以通过FOR 循环和普通的SELECT INTO来实现，那两者之间的差异呢？  
    --差异是FOR循环的SELECT INTO逐行提取并绑定到记录变量，而BULK COLLECT则一次即可提取所有行并绑定到记录变量。即谓批量绑定。  

二、使用LIMIT限制FETCH数据量
    在使用BULK COLLECT 子句时，对于集合类型，如嵌套表，联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK
COLLECT子句操作集合，则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避
免过大的数据集造成性能下降，因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。

    用法：
        FETCH ... BULK COLLECT INTO ... [LIMIT rows]


    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子句来实现批量绑定。


    --下面示例中从表emp中删除所有deptno=20的记录  
    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;  

四、FORALL与BULK COLLECT 综合运用
    FORALL与BULK COLLECT是实现批量SQL的两个重要方式，我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。


    DROP TABLE tb_emp;  
      
    CREATE TABLE tb_emp AS                        -->创建表tb_emp  
       SELECT empno, ename, hiredate  
       FROM   emp  
       WHERE  1 = 2;  
      
    DECLARE                                 
       CURSOR emp_cur IS                          -->声明游标   
          SELECT empno, ename, hiredate FROM emp;  
      
       TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;  -->基于游标的嵌套表类型  
      
       emp_tab   nested_emp_type;                         -->声明嵌套变量  
    BEGIN  
       SELECT empno, ename, hiredate  
       BULK   COLLECT INTO emp_tab                        -->BULK  COLLECT批量提取数据  
       FROM   emp  
       WHERE  sal > 1000;  
      
       FORALL i IN 1 .. emp_tab.COUNT                     -->使用FORALL语句将变量中的数据插入到表tb_emp  
          INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
          VALUES emp_tab( i );  
      
       COMMIT;  
       DBMS_OUTPUT.put_line( 'The total ' || emp_tab.COUNT || ' rows has been inserted to tb_emp' );  
    END;  

五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。