oracle 使用绑定变量极大的提升性能

初始化操作

SQL> alter system flush shared_pool;
SQL> set timing on;

1. 未使用绑定变量的时候代码如下

declare 
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
        for i in 1..100
          loop
            open l_rc for 
            'select object_name from all_objects where object_id='||i;
            fetch l_rc into l_dummy;
            close l_rc;
            end loop;
            dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...'); 
end;

执行结果： 
    PL/SQL procedure successfully completed
    Executed in 14.196 secondsed

2. 使用了绑定变量的方式执行

 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..100
 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(round((dbms_utility.get_time-l_start)/100,2)||'seconds...'); 
16 end;
17 
18 执行结果：
19     PL/SQL procedure successfully completed
20     Executed in 0.141 seconds