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;

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;

pl/sql中bulk collect的用法

相關文章