Oracle 10g批量綁定forall bulk collect的方法

以下的文章主要是介紹Oracle 10g來批量綁定forall bulk collect的具體方法，我們大家都知道批量綁定一般的情況下是通過減少在PL/SQL與SQL引擎之間的上下文切換(context switches )以此提高性能，批量綁定（Bulk binds）主要包括：

(1) Input collections, use the FORALL statement，用來改善DML（INSERT、UPDATE和DELETE) 操作的性能。

(2) Output collections, use BULK COLLECT clause，用來提高查詢（SELECT）的性能。

Oracle 10g開始forall語句可以使用三種方式：

in low..up

in indices of collection 取得集合元素下標的值。

in values of collection 取得集合元素的值。

forall語句還可以使用部分集合元素。

sql%bulk_rowcount(i)表示forall語句第i元素所作用的行數。

--drop table blktest;  
--CREATE TABLE blktest (num NUMBER(20), name varchar2(50));  
--CREATE OR REPLACE PROCEDURE p_bulktest　IS  
DECLARE  
TYPE type_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  
TYPE type_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;  
tab_num type_num;  
tab_name type_name;  
t1 NUMBER;  
t2 NUMBER;  
t3 NUMBER;  
BEGIN  
FOR i IN 1 .. 500000 LOOP  
tab_num(i) := i;  
tab_name(i) := 'name: ' || to_char(i);  
END LOOP;  
SELECT dbms_utility.get_time 　  
INTO t1 　  
FROM dual;  
FOR i IN 1 .. 500000 LOOP  
INSERT INTO blktest   
VALUES  
(tab_num(i), tab_name(i));  
END LOOP;  
SELECT dbms_utility.get_time 　  
INTO t2 　  
FROM dual;  
FORALL i IN 1 .. 500000  
INSERT INTO blktest   
VALUES  
(tab_num(i), tab_name(i));  
SELECT dbms_utility.get_time 　  
INTO t3 　  
FROM dual;  
dbms_output.put_line('Execution Time(S)');  
dbms_output.put_line('-------------------');  
dbms_output.put_line('FOR loop: '   
|| to_char((t2 - t1) / 100));  
dbms_output.put_line('FORALL:　 '   
|| to_char((t3 - t2) / 100));  
END;  
/*  
Execution Time(S)  
FOR loop: 32.78  
FORALL:　 2.64  
*/  
/*  

bulk collect 語句

用於取得批量數據，只適用於select into ,fetch into 及DML語句的返回子句

DECLARE  
TYPE type_emp IS TABLE OF   
scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;  
tab_emp type_emp;  
TYPE type_ename IS TABLE OF   
scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;  
tab_ename type_ename;  
CURSOR c IS  
SELECT *  
FROM scott.emp;  
BEGIN  
SELECT * BULK COLLECT  
INTO tab_emp  
FROM scott.emp;  
FOR i IN 1 .. tab_emp.COUNT LOOP  
dbms_output.put_line(tab_emp(i).ename);  
END LOOP;  
dbms_output.new_line;  
DELETE scott.emp RETURNING   
ename BULK COLLECT INTO tab_ename;  
FOR i IN 1 .. tab_emp.COUNT LOOP  
dbms_output.put_line(tab_emp(i).ename);  
END LOOP;  
ROLLBACK;  
OPEN c;  
FETCH c BULK COLLECT  
INTO tab_emp;  
dbms_output.new_line;  
FOR i IN 1 .. tab_emp.COUNT LOOP  
dbms_output.put_line(tab_emp(i).sal);  
END LOOP;  
END;  
*/  

批量輸入FORALL+批量輸出BULK

DECLARE，批量輸入FORALL+批量輸出BULK
 

TYPE type_num IS TABLE OF NUMBER;  
tab_1 type_num;  
tab_2 type_num;  
BEGIN  
tab_1 := type_num(1, 2, 3);   
FORALL i IN 1 .. tab_1.COUNT   
--EXECUTE IMMEDIATE 'update t2 set idid2=id*2   
where id=:1 returning id2 into :2'   
--USING tab_1(i) RETURNING BULK COLLECT INTO tab_2;  
update t2 set idid2=id*2 where id=tab_1(i)   
returning id2 bulk collect into tab_2;  
FOR i IN 1 .. tab_2.COUNT LOOP  
dbms_output.put_line(tab_2(i));  
END LOOP;  
END;