集合的批量绑定(一)forall

1、oracle9i之前只支持单行处理，如果采用forall批量绑定相比for循环速度快,这里在本机上测试insert100万行数据，批量绑定时间仅是循环DML的1/10。

 1 DECLARE
 2   TYPE list_of_name IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 3   TYPE list_of_sal IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
 4   vname list_of_name;
 5   sal   list_of_sal;
 6 BEGIN
 7   FOR i IN 1 .. 1000000 LOOP
 8     vname(i) := 'wangjin' || i;
 9     sal(i) := i;
10   END LOOP;
11   FORALL i IN 1 .. 1000000 
12       INSERT INTO namesal VALUES(vname(i), sal(i));
13 COMMIT;
14 END;
15 /
16 PL/SQL procedure successfully completed
17 Executed in 98.078 seconds
 1 --注意forall不是循环，是绑定，不带LOOP
 2 DECLARE
 3   TYPE list_of_name IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 4   TYPE list_of_sal IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
 5   vname list_of_name;
 6   sal   list_of_sal;
 7 BEGIN
 8   FOR i IN 1 .. 1000000 LOOP
 9     vname(i) := 'wangjin' || i;
10     sal(i) := i;
11   END LOOP;
12   FORALL i IN 1 .. 1000000
13       INSERT INTO namesal VALUES(vname(i), sal(i));
14 COMMIT;
15 END;
16 /
17 PL/SQL procedure successfully completed
18 Executed in 9.407 seconds

 

 

posted @ 2013-05-21 16:12  进哥  阅读(141)  评论(0)    收藏  举报