FORALL与BULKCOLLECT的使用方法

1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。

　　2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。

　　使用例子：

　　(1)定义一个TABLE

　　CREATE OR REPLACE TYPE string_table AS TABLE OF VARCHAR2(100);

　　(2)在存储过程里面测试

　　DECLARE

　　v_table string_table;

　　BEGIN

　　SELECT cust_name

　　BULK COLLECT INTO v_table

　　FROM cust c

　　WHERE c.cust_id BETWEEN 64561 AND 64565;

　　FORALL idx IN 1..v_table.COUNT

　　INSERT INTO cust_test VALUES(v_table(idx));

　　COMMIT;

　　END;

==============================================================================

 

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE
TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 500000;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
t1 := DBMS_UTILITY.get_time;
FOR i IN 1..iterations LOOP -- use FOR loop
INSERT INTO parts1 VALUES (pnums(i), pnames(i));
END LOOP;
t2 := DBMS_UTILITY.get_time;
FORALL i IN 1..iterations -- use FORALL statement
INSERT INTO parts2 VALUES (pnums(i), pnames(i));
t3 := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');



declare
  type numlist is varray(6) of number;
  depts numlist := numlist(10,20,30,50,60,80);
begin
  forall j in 2..4
    delete from emp where deptno=depts(j);
  commit;
end;