Oracle Bulk Bind ForAll and bulk COLLECT

How to use ForAll and bulk collect:
1. Using ForAll is more efficient than for, because the former switches only once to the context, while the latter switches between multiple contexts in the same number of cycles.

2. Using Bluk collect to fetch a data collection at once is more efficient than using a cursor bar, especially if the network is not very good. But Bluk collect requires a lot of memory.

Example:

SQL code

1. Create table Test_forall (user_id number (ten), user_name VARCHAR2 (20));

SELECT INTO using bulk collect

SQL code

2. DECLARE
4. TYPE table_forall is table of Test_forall%rowtype;
6. V_table Table_forall;
8. BEGIN
10. SELECT Mub.user_id,mub.user_name
12. BULK COLLECT into v_table
14. From mag_user_basic Mub
16. WHERE mub.user_id between 10000 and 10100;
18. FORALL idx in 1..v_table. COUNT
20. INSERT into test_forall VALUES v_table (IDX);
22. --values (v_table (IDX). user_id,v_table (IDX). user_name); Error
24. -in PL/SQL, BULK In-bind and Record,%rowtype cannot be used in one piece,
26. -in other words, BULK in-bind can only be used with arrays of simple types
28. COMMIT;
32. EXCEPTION
34. When OTHERS Then
36. ROLLBACK;
40. END;

fetch into uses bulk collect

SQL code

2. DECLARE
4. TYPE table_forall is table of Test_forall%rowtype;
6. V_table Table_forall;
10. CURSOR C1 is
12. SELECT Mub.user_id,mub.user_name
14. From mag_user_basic Mub
16. WHERE mub.user_id between 10000 and 10100;
18. BEGIN
20. OPEN C1;
22. --use bulk collect in fetch into
24. FETCH C1 BULK COLLECT into v_table;
28. FORALL idx in 1..v_table. COUNT
30. INSERT into test_forall VALUES v_table (IDX);
32. COMMIT;
36. EXCEPTION
38. When OTHERS Then
40. ROLLBACK;
42. END;

use bulk collect in returning into

SQL code

2. CREATE TABLE test_forall2 as SELECT * from Test_forall;
4. ----using bulk collect in returning into
6. DECLARE
8. TYPE idlist is TABLE of Test_forall.  User_id%type;
10. Enums idlist;
12. TYPE namelist is TABLE of Test_forall.user_name%type;
14. Names NameList;
16. BEGIN
18. DELETE from test_forall2 WHERE user_id = 10100
20. Returning user_id, user_name BULK COLLECT into enums, names;
22. Dbms_output.put_line (' Deleted ' | | Sql%rowcount | |  ' rows: ');
24. For i in enums.  First: Enums. Last
26. LOOP
28. Dbms_output.put_line (' User # ' | | | enums (i) | |  ': ' | | | names (i));
30. END LOOP;
32. COMMIT;
36. EXCEPTION
38. When OTHERS Then
40. ROLLBACK;
44. END;

in a batch update, change the for to ForAll

SQL code

2. DECLARE
4. TYPE Numlist is Varray (a) of number ;
6. Depts numlist: = Numlist (10, 30, 70, ...);
8. --Department numbers
10. BEGIN
12. ...
14. For i in depts.first. Depts. Last
16. LOOP
18. ...
20. --update statement is sent to the SQL engine
22. --With all iteration of the for loop!
24. UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts (i);
26. END LOOP:
28. END;

SQL code

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.10  where 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 to a for All 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 are 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.

Oracle Bulk Bind ForAll and bulk COLLECT