oracle繫結變數的測試

繫結變數的測試[@more@]

10:16:39 > 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
已寫入檔案 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
32.66 seconds...

PL/SQL 過程已成功完成。

已用時間: 00: 00: 33.06
已用繫結變數
00:30:38
已寫入檔案 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
2.14 seconds...

PL/SQL 過程已成功完成。

已用時間: 00: 00: 02.01

透過以上的測試可以看出，使用繫結變數比未用繫結變數的效率要高的多。