绑定与不绑定的区别

总是强调，那么比较一下：

--测试绑定变量效率，1秒搞定
alter system flush shared_pool;

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 .. 500
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
('binding:'|| round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/

然后看看不绑的：

--未使用绑定变量执行，耗时1分钟，慢了60倍
alter system flush shared_pool;

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 .. 500
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
('no bound:'||
 round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/