SQL> alter system flush shared_pool
2 ;
System altered.
SQL> set timing on
SQL> 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 where object_id ='||i;
11 fetch l_rc into l_dummy;
12 close l_rc;
13 end loop;
14 dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||
15 'seconds ..');
16 end;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.09
注意,没有使用绑定变量方式,花费了11秒
SQL> 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 where object_id=:x'
11 using i;
12 fetch l_rc into l_dummy;
13 close l_rc;
14 end loop;
15 dums_output.put_line
16 (round((dbms_utility.get_time-l_start)/100,2)||
17 'seconds..');
18 end;
19 /
dums_output.put_line
*
ERROR at line 15:
ORA-06550: line 15, column 2:
PLS-00201: identifier 'DUMS_OUTPUT.PUT_LINE' must be declared
ORA-06550: line 15, column 2:
PL/SQL: Statement ignored
Elapsed: 00:00:00.08
SQL> edit
Wrote file afiedt.buf
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 where object_id=:x'
11 using i;
12 fetch l_rc into l_dummy;
13 close l_rc;
14 end loop;
15 dbms_output.put_line
16 (round((dbms_utility.get_time-l_start)/100,2)||
17 'seconds..');
18* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
而使用绑定变量的方式,只用了不到一秒的时间!