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;

例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 empno, ename FROM emp;
BEGIN
OPEN test_cur;
FETCH test_cur BULK COLLECT
INTO test_table;
CLOSE test_cur;
-- 跳过集合中的null元素
FORALL idx IN INDICES OF test_table --test_table.first..test_table.last
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) || '----' || t2(i));
END LOOP;
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;

ITPub博客

FORALL与BULK COLLECT联合使用

最新文章