Consider a program to populate elements of a PL/SQL collection from a SELECT query thus…
declare
type employee_ids_t is table of employees.employee_id%type
index by binary_integer;
employee_ids employee_ids_t; n integer:=0;
begin
for j in ( select employee_id from employees where salary < 3000 )
loop
n := n+1; employee_ids(n) := j.employee_id;
end loop;
end;
Each explicit row by row assignment of the
collection element to the cursor component causes a context switch between the
PL/SQL engine and the SQL engine resulting in performance overhead. The
following formulation (one of a family of constructs generically referred to as
bulk binding and available pre-Oracle9i)…
declare
type employee_ids_t is table of employees.employee_id%type
index by binary_integer;
employee_ids employee_ids_t; n integer:=0;
begin
select employee_id
bulk collect into employee_ids
from employees where salary < 3000;
end;
…substantially improves performance by
minimizing the number of context switches required to execute the block. (The
above fragments work pre-Oracle 9i.)
There are many application implementation
situations that require dynamic SQL. Native dynamic SQL (execute immediate
and related constructs) is usually preferred over Dbms_Sql because it's
easier to write and proof read and executes faster. However, pre-Oracle9i, only
Dbms_Sql could be used for dynamic bulk binding. Oracle9i introduces the
following syntax for bulk binding in native dynamic SQL …
declare
type employee_ids_t is table of employees.employee_id%type
index by binary_integer;
employee_ids employee_ids_t; n integer:=0;
begin /* new at 9i */
execute immediate 'select employee_id from employees where salary < 3000'
bulk collect into employee_ids;
end;
