James's Blog

Oracle: binding variable or non-binding variable

No-bind
--------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
execute immediate
'select count(*) from dual where dummy = 'i into x;
end loop;
end;

with-bind
---------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
select count(*) into x from dual where dummy = i;
end loop;
end;


or

select count(*) into x from dual where dummy = :i using i;

posted by James Pei @ 8:38 PM