Oracle 10g批量绑定forall bulk collect的方法-51CTO.COM

以下的文章主要是介绍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  
*/  
/*  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

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;  
*/  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

批量输入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;  
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.