当前位置:首页 > 开发 > 数据库 > 正文

批量执行 bulk collect与forall用法

发表于: 2013-04-08   作者:daizj   来源:转载   浏览:
摘要: BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、 FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。     有关FORALL语句的用法请参考:批量SQL之 F
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
    有关FORALL语句的用法请参考:批量SQL之 FORALL 语句

一、BULK COLLECT批量绑定的示例
[sql] view plaincopyprint?
--下面的示例中使用了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] view plaincopyprint?
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] view plaincopyprint?
--下面示例中从表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的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
[sql] view plaincopyprint?
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 子句中。

批量执行 bulk collect与forall用法

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理 这种在PL/SQL引
在oracle 8i或更高版本的forall语句中,oracle提供了非常有意义的dml增强特性。Forall告诉PL/SQL运
在oracle 8i或更高版本的forall语句中,oracle提供了非常有意义的dml增强特性。Forall告诉PL/SQL运
在oracle 8i或更高版本的forall语句中,oracle提供了非常有意义的dml增强特性。Forall告诉PL/SQL运
一、使用HQL执行批量操作 注意:插入数据时:只支持INSERT INTO ... SELECT ...形式,不支持INSERT I
一、环境准备:   1、Jdk1.6或以上:http://www.oracle.com/technetwork/java/javase/downloads/i
1、在使用WebService时我们经常会考虑以下问题:怎么防止别人访问我的WebService?   在System.Ne
ORACLE 10G OFFICIAL DOCUMNET ----------------------------------------------------------------
以往记得Mysql的命令端可以批量插入或者批量更新数据库。 今天记下PL/SQL Deverloper 实现批量数据
在工作中,有的项目使用Oracle数据库,有的使用SQL SERVER数据库,当一部分功能开发完毕后,提交测
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号