调整方法三：使用存储过程的批量更新(2)
回到本例，我们使用两种方法来更新数据。实验如下：
-- 一次10000行批量提交.
-- 需要在待更新的表上设置标志字段,如本例中的flag
SQL> CREATE OR REPLACE PROCEDURE Sp_Test_Update_1
2 IS
3 TYPE t_id IS TABLE OF CHAR (10);
4
5 TYPE t_col1 IS TABLE OF NUMBER (20, 2);
6
7 v_id t_id;
8 v_col1 t_col1;
9 v_rowcount NUMBER;
10
11 CURSOR c_1
12 IS
13 SELECT /* +use_hash(pa p)*/
14 PA.ID,
15 PA.col1
16 * C.col1
17 FROM PA,
18 P,
19 C
20 WHERE P.ID = PA.ID
21 AND NVL (flag, '0') = '0'
22 AND P.cid = C.cid
23 AND PA.ID NOT IN (SELECT /* +hash_aj*/ ID
24 FROM D)
25 AND ROWNUM <= 10000;
26 BEGIN
27 LOOP
28 OPEN c_1;
29
30 FETCH c_1
31 BULK COLLECT INTO v_id,
32 v_col1;
33
34 v_rowcount := c_1%ROWCOUNT;
35
36 CLOSE c_1;
37
38 EXIT WHEN v_rowcount = 0;
39 FORALL v_i IN 1 .. v_rowcount
40 UPDATE PA
41 SET col1 = v_col1 (v_i),
42 flag = '1'
43 WHERE ID = v_id (v_i);
44 COMMIT;
45 END LOOP;
46 END;
47 /
过程已创建。
已用时间: 00: 00: 00.02
SQL>
SQL> exec sp_test_update_1;
PL/SQL 过程已成功完成。
已用时间: 00: 02: 05.08
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_test_update_2
2 IS
3 TYPE t_rowid IS TABLE OF VARCHAR2 (20);
4
5 TYPE t_col1 IS TABLE OF NUMBER (20, 2);
6
7 v_rowid t_rowid;
8 v_col1 t_col1;
9 v_rowcount NUMBER;
10
11 CURSOR c_1
12 IS
13 SELECT /* +use_hash(pa p)*/
14 PA.ROWID,
15 PA.col1
16 * C.col1
17 FROM PA,
18 P,
19 C
20 WHERE P.ID = PA.ID
21 AND P.cid = C.cid
22 AND PA.ID NOT IN (SELECT /* +hash_aj*/ ID
23 FROM D);
24 BEGIN
25 OPEN c_1;
26
27 FETCH c_1
28 BULK COLLECT INTO v_rowid,
29 v_col1;
30
31 v_rowcount := c_1%ROWCOUNT;
32
33 CLOSE c_1;
34
35 FORALL v_i IN 1 .. v_rowcount
36 UPDATE PA
37 SET col1 = v_col1 (v_i)
38 WHERE ROWID = v_rowid (v_i);
39 COMMIT;
40 END;
41 /
过程已创建。
已用时间: 00: 00: 00.00
SQL>
SQL> exec sp_test_update_2;
PL/SQL 过程已成功完成。
已用时间: 00: 00: 23.04
SQL>
这里有两种不同的方法，第一种方法分段提取、更新，通过在待更新的表上设置额外的标志字段（flag），每次批量更新一部分数据，由于每次都需要更新标志位，性能不高，过程的执行时间为00: 02: 05.08；第二种方法一次性提取、一次性更新，通过bulk collect一次性把需要更新表的rowid以及更新后的结果载入到服务器内存中，然后用forall子句执行批量更新，执行时间为00: 00: 23.04，略为优于调整前的update语句。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21738/，如需转载，请注明出处，否则将追究法律责任。
