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;