转自：
http://log-cd.iteye.com/blog/411122FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
例子:
1
create table test_forall ( user_id number(10), user_name varchar2(20));
select into 中使用bulk collect
1DECLARE
2 TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
3 v_table table_forall;
4BEGIN
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
16EXCEPTION
17 WHEN OTHERS THEN
18 ROLLBACK;
19
20END;
21 fetch into 中使用bulk collect
1DECLARE
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;
9BEGIN
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
18EXCEPTION
19 WHEN OTHERS THEN
20 ROLLBACK;
21END; 在returning into中使用bulk collect
1CREATE TABLE test_forall2 AS SELECT * FROM test_forall;
2----在returning into中使用bulk collect
3DECLARE
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;
8BEGIN
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
18EXCEPTION
19 WHEN OTHERS THEN
20 ROLLBACK;
21
22END; 批量更新中，将for改成forall
1DECLARE
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.10 WHERE deptno = depts(i);
13 END LOOP:
14 END;
1--UPDATE statement is sent to the SQL engine just once, with the entire nested table
2FORALL 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 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.
what the hell is going on ??!!
