ORACLE批量绑定FORALL与BULK COLLECT

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

2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。 

例子: 

Sql代码  

1. create table test_forall ( user_id number(10), user_name varchar2(20));  

select into 中使用bulk collect  

Sql代码  

1. DECLARE  
2.   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;  
3.   v_table table_forall;  
4. BEGIN  
5.     SELECT mub.user_id,mub.user_name  
6.          BULK COLLECT INTO v_table  
7.     FROM mag_user_basic mub  
8.          WHERE mub.user_id BETWEEN 10000 AND 10100;  
9.     FORALL idx IN 1..v_table.COUNT  
10.            INSERT INTO test_forall VALUES v_table(idx);  
11.            --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error  
12.            --在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，  
13.            --也就是说，BULK In-BIND只能与简单类型的数组一块使用  
14.     COMMIT;  
15.   
16. EXCEPTION  
17.     WHEN OTHERS THEN  
18.         ROLLBACK;  
19.     
20. END;  

fetch into 中使用bulk collect  

Sql代码  

1. DECLARE  
2.   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;  
3.   v_table table_forall;     
4.     
5.   CURSOR c1 IS  
6.     SELECT mub.user_id,mub.user_name  
7.          FROM mag_user_basic mub  
8.            WHERE mub.user_id BETWEEN 10000 AND 10100;  
9. BEGIN  
10.    OPEN c1;  
11.    --在fetch into中使用bulk collect  
12.    FETCH c1 BULK COLLECT INTO v_table;  
13.      
14.    FORALL idx IN 1..v_table.COUNT  
15.          INSERT INTO test_forall VALUES v_table(idx);  
16.     COMMIT;  
17.   
18. EXCEPTION  
19.     WHEN OTHERS THEN  
20.         ROLLBACK;  
21. END;  

在returning into中使用bulk collect  

Sql代码  

1. CREATE TABLE test_forall2 AS SELECT * FROM test_forall;  
2. ----在returning into中使用bulk collect  
3. DECLARE  
4.    TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;  
5.    enums IdList;  
6.    TYPE NameList IS TABLE OF test_forall.user_name%TYPE;  
7.    names NameList;  
8. BEGIN  
9.    DELETE FROM test_forall2 WHERE user_id = 10100  
10.         RETURNING user_id, user_name BULK COLLECT INTO enums, names;  
11.    dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');  
12.    FOR i IN enums.FIRST .. enums.LAST  
13.    LOOP  
14.      dbms_output.put_line('User #' || enums(i) || ': ' || names(i));  
15.    END LOOP;  
16.    COMMIT;  
17.      
18. EXCEPTION  
19.     WHEN OTHERS THEN  
20.         ROLLBACK;  
21.          
22. END;  

bulk collect时，需要注意：所有的into变量都必须是collections, collect into 通过内存换时间 内存不够是效率会变差

在 fetch into 中使用bulk collect  时 ：可以考虑用 exit when var_array.count=0 结束 （把游标的数据都捉取出来）

begin
  num:=0;    
  open prm_due;
  loop
    fetch prm_due bulk collect into var_array limit 5;--每次取多条
 -- exit when prm_due%notfound;  --5条之后不在捉取
     exit when var_array.count=0;   -- 把游标的数据都捉取出来
     num:=num+1;

     dbms_output.put_line('num : '||num);

     for i in var_array.first .. var_array.last 
          loop
               dbms_output.put_line(var_array(i).c_rcpt_no);
     end loop;

 
 end loop;
 close prm_due;

 
end;