FORALL與BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因為前者只切換一次上下文，而后者將是在循環(huán)次數(shù)一樣多個上下文間切換。
2．使用BLUK COLLECT一次取出一個數(shù)據(jù)集合，比用游標(biāo)條取數(shù)據(jù)效率高，尤其是在網(wǎng)絡(luò)不大好的情況下。但BLUK COLLECT需要大量內(nèi)存。
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;