oracle绑定变量的测试

绑定变量的测试[@more@]

10:16:39 scott@bus> select * from v$version
10:16:54 2 ;

BANNER
================================================================
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

已用时间: 00: 00: 00.00

未用绑定变量
00:28:20 scott@bus>ed
已写入文件 afiedit.sql

1 declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1..1000
8 loop
9 open l_rc for
10 'select object_name from all_objects
11 where object_id = ' || i;
12 fetch l_rc into l_dummy;
13 close l_rc;
14 end loop;
15 dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||
16 ' seconds...');
17* end;
00:28:47 scott@bus>/
32.66 seconds...

PL/SQL 过程已成功完成。

已用时间: 00: 00: 33.06
已用绑定变量
00:30:38 scott@bus>ed
已写入文件 afiedit.sql

1 declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1..1000
8 loop
9 open l_rc for
10 'select object_name from all_objects
11 where object_id = :x' using i;
12 fetch l_rc into l_dummy;
13 close l_rc;
14 end loop;
15 dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||
16 ' seconds...');
17* end;
00:31:12 scott@bus>/
2.14 seconds...

PL/SQL 过程已成功完成。

已用时间: 00: 00: 02.01

通过以上的测试可以看出，使用绑定变量比未用绑定变量的效率要高的多。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/802415/viewspace-823033/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/802415/viewspace-823033/