Bind variables work in PL/SQL blocks just as well as they do in SQL statements.
You can test this by running simple statements in a loop, then look at the parse counts in v$sesstat.
Create a simple table to use for inserting and deleting. Get initial parse counts.
create table test1(a number);
--Flush the pool, or else this test won't be repeatable.
alter system flush shared_pool;
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
47 parse count (total)
5 parse count (hard)
This is what hard parsing looks like:
begin
for i in 1 .. 10000 loop
execute immediate 'insert into test1 values('||i||')';
end loop;
commit;
end;
/
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
10072 parse count (total)
10007 parse count (hard)
PL/SQL blocks with bind variables do not always hard parse. Note that the parse counts are cumulative, and they only increase very slightly here.
begin
for i in 1 .. 10000 loop
execute immediate
'begin
delete from test1 where a = :i;
end;'
using i;
end loop;
commit;
end;
/
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
10106 parse count (total)
10019 parse count (hard)