FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
1).在select into 中使用 forall bulk collect;
create table test_forall ( user_id number(10), user_name varchar2(20));
SELECT * FROM ex_edm_esp;
SELECT * FROM test_forall;
DECLARE
TYPE forall_table IS TABLE OF test_forall%ROWTYPE;
test_table forall_table;
BEGIN
SELECT ID,esp_name BULK COLLECT INTO test_table FROM ex_edm_esp;
FORALL idx IN test_table.first..test_table.last
INSERT INTO test_forall VALUES test_table(idx);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
for all 的另一种写法:
FORALL idx IN VALUE OF test table
2).在fetch into中使用 forall, indices of, bulk collect;
DECLARE
TYPE forall_table IS TABLE OF test_forall%ROWTYPE;
test_table forall_table;
CURSOR test_cur IS SELECT ID,esp_name FROM ex_edm_esp;
BEGIN
OPEN test_cur;
FETCH test_cur BULK COLLECT INTO test_table;
CLOSE test_cur;
-- 跳过集合中的null元素
FORALL idx IN INDICES OF test_table
INSERT INTO test_forall VALUES test_table(idx);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
3).在returning into 之中使用 forall in, value of, bulk collect;
DECLARE
TYPE t_user_id IS TABLE OF test_forall.user_id%TYPE;
t1 t_user_id;
TYPE t_user_name IS TABLE OF test_forall.user_name%TYPE;
t2 t_user_name;
BEGIN
DELETE FROM test_forall RETURNING user_id, user_name BULK COLLECT INTO t1,t2;
FOR i IN t1.first..t1.last
LOOP
dbms_output.put_line(t1(i));
dbms_output.put_line(t2(i));
END LOOP;
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;