批量绑定—BULK COLLECT

最新推荐文章于 2023-12-14 11:05:36 发布
最新推荐文章于 2023-12-14 11:05:36 发布
阅读量1.3k 收藏
点赞数
分类专栏： Oracle PLSQL 文章标签： sql returning exception table oracle integer
在 PL/SQL 编码中，经常会从表中获取结果集，然后进行一些逻辑处理，再生成新的数据。我们想到的最直接的方法就是使用游标。从 Oracle8i 开始，出现了一个新的子句：BULK COLLECT。可以降低 SQL 引擎到 PL/SQL 引擎的上下文交换（context switch）次数，从而实现数据的高速检索。
 
1. 速度比较
 
从表中获取结果集，我们常用的方法就是使用游标循环，我们看看它的执行速度：

 

SQL> set serveroutput on

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01 := tnt_ic01();

  4    n_startTime number;

  5  begin

  6    n_startTime := dbms_utility.get_time;

  7    for rec_ic01 in( select * from ic01 where rownum < 100000 ) loop

  8      nt_ic01.extend;

  9      nt_ic01( nt_ic01.last ) := rec_ic01;

 10    end loop;

 11    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 12    dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );

 13  end;

 14  /

COUNT: 99999

USED TIEM: 255

 

PL/SQL 过程已成功完成。

 

我们再看一下完成同样的功能，BULK COLLECT 的执行速度：

 

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4    n_startTime number;

  5  begin

  6    n_startTime := dbms_utility.get_time;

  7    select *

  8      bulk collect into nt_ic01

  9      from ic01

 10     where rownum < 100000;

 11    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 12    dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );

 13  end;

 14  /

COUNT: 99999

USED TIEM: 159

 

PL/SQL 过程已成功完成。

 
BULK COLLECT 要比游标循环快得多。如果你的代码中有游标循环，并且你也正为这里执行缓慢而发愁，不妨试试 BULK COLLECT。
 
2. 没有 NO_DATA_FOUND 异常
 
不过要记得，SELECT ... BULK COLLECT INTO ... 不会引起 NO_DATA_FOUND 异常，虽然它的语法看起来和 SELECT ... INTO ... 类似。 要判断 BULK COLLECT 是否获得数据，可以通过 COUNT 方法：

 

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4    rec_ic01 ic01%rowtype;

  5  begin

  6    --1. normal select, can cause no_data_found

  7    begin

  8      select *

  9        into rec_ic01

 10        from ic01

 11       where 1 = 0;

 12    exception

 13      when no_data_found then

 14        dbms_output.put_line( 'error1: no_data_found exception accured!' );

 15    end;

 16

 17    --2. bulk collect, won't cause no_data_found

 18    select *

 19      bulk collect into nt_ic01

 20      from ic01

 21     where 1 = 0;

 22    if nt_ic01.count = 0 then

 23      dbms_output.put_line( 'error2: There is no data in nt_ic01.' );

 24    end if;

 25  end;

 26  /

error1: no_data_found exception accured!

error2: There is no data in nt_ic01.

 

PL/SQL 过程已成功完成。

 

3. 其他使用
 
除了 SELECT INTO 外，还可以在 FETCH INTO 和 RETURNING INTO 子句中使用 BULK COLLECT：
 

-- 1. fetch collect into

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is table of cur_ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

-- 2. returning into

SQL> select * from test_return;

 

AAC001               AAC003
-------------------- ----------
1304012080949        转入人7
1304012080950        转入人8
1304012080951        转入人9
1304012080952        转入

 

SQL> declare
  2    type tnt_return is table of test_return%rowtype;
  3    nt_return tnt_return;
  4  begin
  5    update test_return
  6       set aac003 = 'yuechaotian'
  7     where rownum < 3
  8     returning aac001, aac003 bulk collect into nt_return;
  9
 10    for n_pointer in 1..nt_return.count loop
 11      dbms_output.put_line( 'AAC001=' || nt_return(n_pointer).aac001
 12                      || ' | AAC003=' || nt_return(n_pointer).aac003 );
 13    end loop;
 14  end;
 15  /
AAC001=1304012080949 | AAC003=yuechaotian
AAC001=1304012080950 | AAC003=yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_return;

AAC001               AAC003
-------------------- ------------
1304012080949        yuechaotian
1304012080950        yuechaotian
1304012080951        转入人9
1304012080952        转入

SQL>

 
4. 联合数组中使用 BULK COLLECT
 
前面我们举的例子，都是用的嵌套表，其实也可以使用联合数组：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is table of cur_ic01%rowtype index by pls_integer;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

BULK COLLECT 生成的集合，下表是默认从1开始的数字，步进为1，所以联合数组的索引，不能使用 varchar2 类型。如果你不小心使用了 varchar2 类型作为联合数组的索引，就会出现下面的错误：

 

ERROR 位于第 11 行:

ORA-06550: 第 11 行, 第 36 列:

PLS-00657: 实施约束: 不支持带有 VARCHAR2 关键字的关联性数组的批 SQL。

 

5. VARRAY 数组中使用 BULK COLLECT

 

而对于 VARRAY 数组，BULK COLLECT 也适用，不过定义时，其长度必须得大于或等于结果集的长度：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(9) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

如果 VARRAY 数组长度定义得过小，则会提示错误：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(8) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

declare

*

ERROR 位于第 1 行:

ORA-22165: 指定的索引 [9] 必须介于 [1] 到 [8] 之间

ORA-06512: 在 line 11

 

如果 VARRAY 数组长度定义得过大，没什么影响，而且也不会占用多余的内存空间：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(10) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14    dbms_output.put_line( 'LIMIT: ' || nt_ic01.limit );

 15  end;

 16  /

COUNT: 9

LIMIT: 10

 

PL/SQL 过程已成功完成。

 

不过推荐首选联合数组，或是嵌套表，在这里虽然可以使用 VARRAY 数组，但显然是不太合适的，就像steven 所介绍的那样。

 

6. 动态 SQL 中使用 BULK COLLECT

 

Oracle9i 之前，BULK COLLECT 只能用在静态 SQL 中，从 Oracle9i 开始，动态 SQL 中也可以使用 BULK COLLECT：

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4

  5    v_sql_cursor varchar2(1000);

  6  begin

  7    v_sql_cursor := 'select * from ic01 where rownum < 100000';

  8    execute immediate v_sql_cursor bulk collect into nt_ic01;

  9    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 10  end;

 11  /

COUNT: 99999

 

PL/SQL 过程已成功完成。

 

SQL>

jsjzzh
关注
专栏目录
PLSQL——集合（索引表、嵌套表、变长数组、bulk collect、批量绑定）
m0_72036727的博客
07-16 1014
索引表、嵌套表、变长数组、bulk collect、批量绑定
使用BULK COLLECT, MERGE 语句提高sql执行效率
01-17
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
批量SQL之 BULK COLLECT 子句
数据库天地
05-08 407
BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。 有关FORALL语句的用法请参考：批量SQL之 FORALL 语句 一、BULK COLL...
批量绑定
Phoenix_99的专栏
03-04 385
declare  type id_table_type is table of number(6) index by binary_integer;  type name_table_type is table of varchar2(10) index by binary_integer;  id_table id_table_type;  name_table name_table_type;
oracle取数据限定行数,PL/SQL Challenge 每日一题：2014-2-17 BULL COLLECT批量获取时限制行数...
weixin_39743369的博客
04-05 165
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。以往旧题索引：http://www.itpub.net/forum.php?m ... eid&typeid=1808http://www.itpub.net/thread-1499223-1-1.html原始出处：http://www.plsqlc...
oracle下巧用bulk collect实现cursor批量fetch的sql语句
12-15
在Oracle数据库中，Bulk Collect是一个非常实用的特性，它允许我们一次从游标（Cursor）中批量地获取多行数据，极大地提高了处理大量数据时的性能。本文将深入讲解如何在PL/SQL中使用Bulk Collect实现Cursor的批量...
Oracle BULK COLLECT批量取数据解决方法
09-11
Oracle BULK COLLECT是Oracle数据库提供的一种高级PL/SQL特性，用于提高...在实际开发中，应当根据具体业务需求和数据规模灵活运用BULK COLLECT，结合其他优化策略，比如绑定变量、索引优化等，以达到最佳的性能效果。
PL/SQL中BULK COLLECT FORALL与记录操作详解
记录允许我们存储和操作多列数据，类似于数据库中的行，而BULK COLLECT和FORALL则提供了批量处理数据的高效手段。" 在PL/SQL中，BULK COLLECT和FORALL是两个重要的特性，它们用于提高数据库操作的效率，特别是处理...
举个Oracle的bulk collect FETCH into insert的例子
06-08
以下是一个Oracle的bulk collect FETCH into insert的...FORALL语句是一个批量绑定语句，它可以在一个操作中一次性处理多个行。 最后，我们检查游标是否已经到达了最后一行，如果是，则退出循环。最后，我们关闭游标。
sql server Bulk Insert命令详细
09-10
主要介绍了sql server Bulk Insert命令详细 ,需要的朋友可以参考下
mysql oracle游标 动态sql语句_Oracle数据库之动态SQL
weixin_39944233的博客
02-01 231
Oracle数据库之动态SQL1. 静态SQLSQL与动态SQLOracle编译PL/SQL程序块分为两个种：一种为前期联编(early binding)，即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编(late binding)，即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，...
通过bulk collect 减少loop处理的开销
weixin_30955617的博客
06-25 69
通过bulk collect减少loop处理的开销 采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulkcollect。注意在使用bulk collect时，所有的into变量都必须是collections. 举几个简单的...
Oracle动态游标的使用
经验分享--我们都在成长
01-04 4802
Oracle动态游标的使用 游标的概念:     游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 游标有两种类型：显式游标和隐式游标。在前述程序中
Object Relational Features
为无为，事无事，味无味。
08-13 1202
Object Relational Features Collections Associative ArraysNested TablesVARRAYsPopulating CollectionsPersistently Storing CollectionsCAST and MULTISETPerformance of Collections Using ROWNUM
oracle学习之bulk collect用法
weixin_34297704的博客
09-22 134
通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into...
人大金仓数据库KingbaseES PLSQL中的批量处理—BULK COLLECT介绍
最新发布
arthemis_14的博客
12-14 1337
在介绍多行SQL的批处理之前，我们首先引入一个概念——上下文切换：当PL/SQL在运行一块代码时，PL/SQL引擎只处理过程语句（例如赋值语句、条件语句等），而SQL语句将会发送给SQL引擎来处理，等SQL语句处理完之后，再将结果返回给PL/SQL。那么这种在PL/SQL引擎和SQL引擎之间的转移控制，就称为上下文切换。显然，频繁的上下文切换将会增加额外的开销，导致处理速度变慢、效率降低。为了解决上述问题，最直接的解决方法就是减少上下文切换的次数。
Oracle的bulk collect使用
pursuer211的专栏
01-10 1349
bulk collect的作用是将检索结果批量的、一次性的赋给集合变量，并把结果集从PL/SQL引擎传给SQL引擎。与每次获取一条数据，并每次都要将结果由PL/SQL引擎传给SQL引擎相比，可以很大程度上的节省开销。bulk collect可以和select into、fetch into、returning into一起使用，使用bulk collect后，into后的变量必须是集合型的。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值