ITPub博客

首页 > Linux操作系统 > Linux操作系统 > pl/sql中bulk collect的用法

pl/sql中bulk collect的用法

原创 Linux操作系统 作者:eric0435 时间:2012-02-19 22:40:46 0 删除 编辑
bulk collect可以将查询结果一次性地加载到collections中,而不用一条一条地处理。在select into,fetch into,returning into语句使用使用bulk collect时,所有的into变量都必须是collections。

create table jy
(
object_id number(12),
object_name varchar2(20),
object_type varchar2(20)
)
在select into语句中使用bulk collect

declare
type object_list is table of jy.object_name%type;
objs object_list;
begin
select object_name bulk collect
into objs
from jy;
for r in objs.first .. objs.last loop
dbms_output.put_line(''|| objs(r));
end loop;
end;
/

在fetch into中使用bulk collect

declare
type objecttab is table of jy%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from jy;
begin
open cob;
fetch cob bulk collect
into objs;
close cob; -- 把结果集一次fetch到collect中,我们还可以通过limit参数,来分批fetch数据
for r in objs.first .. objs.last loop
dbms_output.put_line(' ' || objs(r).object_name);
end loop;
end;



declare
type objecttab is table of jy%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from jy;
begin
open cob;
loop
fetch cob bulk collect
into objs limit 100;--每次取一百条数据这是可以根据你的数据库性能来决定的
exit when cob%notfound;
dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||
' last:' || objs.last);
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end loop;
close cob;
end;



在returning into中使用bulk collect

declare
type id_list is table of jy.object_id%type;
ids id_list;
type name_list is table of jy.object_name%type;
names name_list;
begin
delete from jy  returning object_id, object_name bulk collect into ids,
names;
dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');
for i in ids.first .. ids.last loop
dbms_output.put_line('object #' || ids(i) || ': ' || names(i));
end loop;
end;

FORALL与BULK COLLECT的使用方法:
1.使用FORALL比FOR效率高,因为前者只切换一次上下文,而后者将是在循环次数一样多个上下文间切换。

2.使用BLUK COLLECT一次取出一个数据集合,比用游标条取数据效率高,尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
create table test_forall ( user_id number(10), user_name varchar2(20));
select into 中使用bulk collect
DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;
BEGIN
    SELECT mub.user_id,mub.user_name
         BULK COLLECT INTO v_table
    FROM mag_user_basic mub
         WHERE mub.user_id BETWEEN 10000 AND 10100;
    FORALL idx IN 1..v_table.COUNT
           INSERT INTO test_forall VALUES v_table(idx);
           --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
           --在PL/SQL中,BULK In-BIND与RECORD,%ROWTYPE是不能在一块使用的,
           --也就是说,BULK In-BIND只能与简单类型的数组一块使用
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END;
fetch into 中使用bulk collect
DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;

  CURSOR c1 IS
    SELECT mub.user_id,mub.user_name
         FROM mag_user_basic mub
           WHERE mub.user_id BETWEEN 10000 AND 10100;
BEGIN
   OPEN c1;
   --在fetch into中使用bulk collect
   FETCH c1 BULK COLLECT INTO v_table;

   FORALL idx IN 1..v_table.COUNT
         INSERT INTO test_forall VALUES v_table(idx);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
在returning into中使用bulk collect
CREATE TABLE test_forall2 AS SELECT * FROM test_forall;
----在returning into中使用bulk collect
DECLARE
   TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
   enums IdList;
   TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM test_forall2 WHERE user_id = 10100
        RETURNING user_id, user_name BULK COLLECT INTO enums, names;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
     dbms_output.put_line('User #' || enums(i) || ': ' || names(i));
   END LOOP;
   COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

END;
--批量更新中,将for改成forall
DECLARE
    TYPE NumList IS VARRAY(20) OF NUMBER;
    depts NumList := NumList(10, 30, 70, ...);
 -- department numbers
     BEGIN
   
       /*FOR i IN depts.FIRST..depts.LAST
       LOOP
       ...
       --UPDATE statement is sent to the SQL engine
       -- with each iteration of the FOR loop!
         UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
       END LOOP:
      */
       FORALL i IN depts.FIRST..depts.LAST
        UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
       commit;
    END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-716765/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
系统架构师,ACOUG MEMBER,Oracle ACE,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

注册时间:2011-10-12

  • 博文量
    515
  • 访问量
    6103819