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 ;  

批量更新中，将for改成forall

Sql代码

1.  DECLARE   

2.       TYPE NumList IS VARRAY(20) OF NUMBER;                                                   

3.       depts NumList := NumList(10, 30, 70, ...);   

4.  -- department numbers                       

5.        BEGIN            

6.        ...              

7.          FOR i IN depts. FIRST ..depts.LAST   

8.          LOOP   

9.          ...   

10.         --UPDATE statement is sent to the SQL engine   

11.         -- with each iteration of the FOR loop!   

12.           UPDATE emp SET sal = sal * 1.10WHERE deptno = depts(i);   

13.        END LOOP:   

14.      END ;            

 

Sql代码

1.  --UPDATE statement is sent to the SQL engine just once, with the entire nested table   

2.  FORALL i IN depts. FIRST ..depts. LAST   

3.     UPDATE emp SET sal = sal * 1.10WHERE deptno = depts(i);   

To maximize performance, rewrite your programs as follows:
a. If an INSERT, UPDATE, or DELETE statement executes inside a loop and References collection elements, move it into a FORALL statement.
b. If a SELECT INTO, FETCH INTO, or RETURNING INTO clause references a
Collection, incorporate the BULK COLLECT clause.
c. If possible, use host arrays to pass collections back and forth between your Programs and the database server.
d. If the failure of a DML operation on a particular row is not a serious problem,Include the keywords SAVE EXCEPTIONS in the FORALL statement and report Or clean up the errors in a subsequent loop using the %BULK_EXCEPTIONS Attribute.