当前位置:操作系统 > Unix/Linux >>

批量执行bulk collect与forall用法

批量执行bulk collect与forall用法
 
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、 
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。 
 
一、BULK COLLECT批量绑定的示例 
[sql]  
--下面的示例中使用了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] 
[sql]  
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子句来实现批量绑定。 
[sql]  
--下面示例中从表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' );  
   
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,