PL/SQL是由PL/SQL引擎执行的,块内的SQL语句是由SQL引擎执行的,所以循环处理中反复执行SQL的话,引擎会发生多次切换。
切换次数多的话开销就会很大。
因此,按数值配列的方式向SQL引擎发送SQL语句,然后统一执行,引擎切换只需要发生一次,执行速度会大幅提高。
让我们我么实际测试一下效果吧。
接着上一章的例子,上次使用的TEST01表,我们删除后从头开始。
SQL> DROP TABLE TEST01;
表已删除。
SQL> CREATE TABLE TEST01 ( A NUMBER, B VARCHAR2(10));
表创建成功。
我们使用包来保存A列用的配列和B列用的配列,这样在整个会话期间会一直保持配列的数值,
验证起来很方便。
SQL> CREATE OR REPLACE PACKAGE PAC1
2 //
3 --制作包
4 //
5 IS
6 /******************************/
7 --A列用配列
8 /******************************/
9 TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
10 INDEX BY BINARY_INTEGER;
11 A_TAB A_TAB_TYPE;
12 /******************************/
13 --B列的排列
14 /******************************/
15 TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
16 INDEX BY BINARY_INTEGER;
17 B_TAB B_TAB_TYPE;
18 END;
19 /
包已创建。
将这个包的配列存储50万个值。
SQL> BEGIN
2 //
3 --给A列、B列的配列设置50000个值
4 //
5 FOR I IN 1…500000 LOOP
6 PAC1.A TAB(I):=I;–A列用的配列保存1~50000
7 PAC1.B TAB(I):='BBBBBB’;–在B列的配列设置为固定的字符串
8 END LOOP;
9 END;
10 /
PL/SQL过程成功完成。
这样的话包的配列中有50000个值。
使用这个配列用第一个普通的循环处理方式,做500000次的INSERT,
调查一下所花费时间。
为了测量时间,执行如下语句
SQL> SET TIMING ON
SQL> BEGIN
2 //
3 --用普通循环语句执行INSERT 50000次
4 //
5 FOR I IN 1…500000 LOOP
6 INSERT INTO TEST01 VALUES (PAC1.A_TAB(I), PAC1.B_TAB(I));
7 END LOOP;
8 END;
9 /
PL/SQL过程成功完成。
经过:00:00:14.53
50万次的循环INSERT处理,花费了约15秒的时间。
我们改成批量绑定的方式
先清空表内容:
SQL> TRUNCATE TABLE TEST01;
表清空了。
用批量绑定的方式,把配列的50万数据进行一次性的INSERT处理
SQL> BEGIN
2 //
3 --批量绑定,配列的500000行,进行一次性的INSERT处理
4 //
5 FORALL I IN 1…500000
6 INSERT INTO TEST01 VALUES (PAC1.A_TAB(I), PAC1.B_TAB(I));
7 END;
8 /
PL/SQL过程成功完成。
经过:00:00:00.60
用批量绑定的方式,执行时间仅仅是0.6秒,快了20多倍。
很神奇吧。让我们在开发中,务必采用这种方式吧。