Встроенный динамический SQL впервые появился в Oracle 8i. Он позволяет декларативно выполнять динамический SQL в среде PL/SQL. Большинство действий можно выполнить с помощью одного оператора, EXECUTE IMMEDIATE, а остальные — с помощью оператора OPEN FOR. Оператор EXECUTE IMMEDIATE имеет следующий синтаксис:
где:
• оператор — любой оператор SQL или PL/SQL-блок;
• переменная1, переменная2,… переменнаяN или запись — переменные PL/SQL, в которые необходимо выбрать данные (столбцы одной строки результатов оператора SELECT);
• связываемая_переменная1,… связываемая переменнаяN — набор переменных PL/SQL, используемых для передачи входных данных/результатов;
• результат1, … результатN — набор PL/SQL-переменных, используемых для размещения результатов, возвращаемых конструкцией RETURN оператора ЯМД.
В следующем примере код для функций GET_ROW_CNTS и UPDATE_ROW, которые мы ранее реализовали с помощью средств пакета DBMS_SQL, переписан с использованием оператора EXECUTE IMMEDIATE. Начнем с функции GET_ROW_CNTS:

EXECUTE IMMEDIATE 'оператор'
[INTO {переменная1., переменная2, . . . переменнаяN | запись}]
[USING [IN | ООТ | IN OUT] связываемая_переменная1, . . .
связываемая_переменнаяN]
[{RETURNING | RETURN} INTO результат1 [, . . . , р е з у л ь т а т N ] . . . ] ;

create or replace
function get_row_cnts(p_tname in varchar2) return number
as
l_cnt number;
begin
execute immediate
'select count(*) from ' || p_tname
into l_cnt;
return l_cnt;
end;
 /
Function created
set serveroutput on
exec dbms_output.put_line(get_row_cnts('emp'));
PL/SQL procedure successfully completed.

Использовав оператор SELECT…INTO… в качестве аргумента для EXECUTE IMMEDIATE, мы существенно уменьшили объем кода. Девять процедурных шагов, необходимых при использовании пакета DBMS_SQL, превратились в один шаг в случае встроенного динамического SQL. Не всегда удается свести все к одному шагу — иногда необходимо три, как будет показано ниже, — но общая идея понятна. Встроенный динамический SQL в данном случае обеспечивает более высокую производительность при написании кода (последствия его использования с точки зрения производительности мы рассмотрим чуть позже). Также бросается в глаза отсутствие раздела EXCEPTION -обработка исключительных ситуаций не нужна, поскольку все происходит неявно. Нет курсора, который необходимо закрывать, ничего не нужно освобождать. Сервер Oracle все делает сам.

Теперь реализуем с помощью встроенного динамического SQL функцию UPDATE_ROW

create or replace
 function update_row(p_owner in varchar2,
 p_newDname in varchar2,
 p_newLoc in varchar2,
 p_deptno in varchar2,
 p_rowid out varchar2)
 return number
 is
 begin
 execute immediate
 'update ' || p_owner || '.dept
 set dname = :bvl, loc = :bv2
 where deptno = to_number(:pk)
 returning rowid into  ut'
 using p_newDname, p_newLoc, p_deptno
 returning into p_rowid;return sql%rowcount;
 end;
 /
Function created.

set serveroutput on
declare
 l_rowid varchar(50);
 l_rows number;
 begin
 l_rows := update_row('SCOTT', 'CONSULTING',
 'WASHINGTON', '10', l_rowid);

 dbms_output.put_line('Updated ' || l_rows || ' rows');
 dbms_output.put_line('its rowid was ' || l_rowid);
 end;
/
Updated 1 rows
its rowid was AAAGnuAAFAAAAESAAA
PL/SQL procedure successfully completed.

Снова код существенно сократился — один шаг вместо шести; такой код проще читать и сопровождать. В этих двух случаях встроенный динамический SQL, несомненно, превосходит средства пакета DBMS_SQL.
Помимо оператора EXECUTE IMMEDIATE встроенный динамический SQL поддерживает динамическую обработку курсорных переменных, REF CURSOR. Курсорные переменные достаточно давно поддерживаются сервером Oracle (oracle pl sql с версии 7.2). Первоначально они позволяли открыть (OPEN) запрос (результирующее множество) в хранимой процедуре и передать ссылку на него клиенту. С их помощью хранимые процедуры возвращают результирующие множества клиентам при использовании VB, протоколов JDBC и ODBC или библиотеки OCI. Позднее, в версии 7.3, поддержка курсорных переменных была расширена, так что в PL/SQL появилась возможность использовать их не только в операторе OPEN, но и в операторе FETCH (в качестве клиента могла использоваться другая подпрограмма на PL/SQL). Это позволило подпрограмме на PL/SQL принимать результирующее множество в качестве входных данных и обрабатывать его.
Таким образом, впервые стало возможно централизовать общую обработку результатов запросов: одна подпрограмма может выбирать данные из нескольких различных запросов (результирующих множеств). До появления версии Oracle 8i, однако, курсорные переменные по сути были исключительно статические. На этапе компиляции (при создании хранимой процедуры) надо было точно знать, какой SQL-запрос будет выполняться.
Это было весьма существенное ограничение, поскольку не позволяло динамически изменять условия запроса, запрашивать другую таблицу и т.п. Начиная с Oracle 8i встроенный динамический SQL позволяет динамически открывать для запроса курсорную переменную. При этом используется следующий синтаксис:

OPEN курсорная_переменная FOR 'select ...'
USING связываемая_переменная1, связываемая_переменная2, ...;

Итак, с помощью курсорных переменных и динамического SQL можно реализовать обобщенную процедуру запроса таблицы в зависимости от входных данных и возвращения результирующего множества клиенту для дальнейшей обработки:

create or replace package my_pkg
 as
 type refcursor_Type is ref cursor;

 procedure get_emps(p_ename in varchar2 default NULL,
 p_deptno in varchar2 default NULL,
 p_cursor in out refcursor_type);
 end;
 /
Package created.
create or replace package body my_pkg
 as

 procedure get_emps(p_ename in varchar2 default NULL,

 p_deptno in varchar2 default NULL,

 p_cursor in out refcursor_type)

 is

 l_query long;

 l_bind varchar2(30);

 begin

 l_query := 'select deptno, ename, job from emp';

 if (p_ename is not NULL)
 then
 l_query := l_query || ' where ename like  ';
 l_bind := '%' || upper(p_ename) || '%';
 elsif (p_deptno is not NULL)
 then
 l_query := l_query || ' where deptno = to_number(:x)';
 l_bind := p_deptno;
 else
 raise_application_error(-20001,'Missing search criteria');
 end if;

 open p_cursor for l_query using l_bind;
 end;
 end;
 /
Package body created.
variable С refcursor
set autoprint on
exec my_pkg.get_emps(p_ename => 'a', p_cursor => :C)
PL/SQL procedure successfully completed.

DEPTNO ENAME JOB
20 ADAMS CLERK
30 ALLEN SALESMAN
30 BLAKE MANAGER
10 CLARK MANAGER
30 JAMES CLERK
30 MARTIN SALESMAN
30 WARD SALESMAN
7 rows selected.
scott@TKYTE816> exec my_pkg.get_emps(p_deptno=> '10', p_cursor => :C)
PL/SQL procedure successfully completed.
DEPTNO ENAME JOB
10 CLARK MANAGER
10 KING PRESIDENT
10 MILLER CLERK

Если созданный динамически запрос возвращает более одной строки, надо использовать представленный выше метод, а не оператор EXECUTE IMMEDIATE.
Итак, по сравнению с представленными выше подпрограммами пакета DBMS_SQL, использование операторов EXECUTE IMMEDIATE и OPEN FOR существенно упрощает написание программ. Значит ли это, что пакет DBMS_SQL больше использовать не придется? Определенно, — не значит. Представленные выше примеры показывают, насколько простым может быть использование динамического SQL, если количество связываемых переменных известно во время компиляции. Если бы мы этого не знали, то не смогли бы использовать оператор EXECUTE IMMEDIATE так просто, как в представленных примерах. Для этого оператора количество связываемых переменных надо знать заранее. Пакет DBMS_SQL в этом отношении обеспечивает большую гибкость.
Помимо количества связываемых переменных необходимо знать еще и столбцы, входящие в результат выполнения SQL-оператора SELECT. Если количество и типы этих столбцов неизвестны, использовать оператор EXECUTE IMMEDIATE тоже не удастся.
Можно будет использовать оператор OPEN FOR, если клиент, получающий курсорную переменную, не является другой подпрограммой на языке PL/SQL.
Оператор EXECUTE IMMEDIATE обеспечит более высокую производительность по сравнению с пакетом DBMS_SQL для всех операторов, анализируемых и выполняемых однократно (все наши примеры пока были именно такими). Для выполнения подпрограмм пакета DBMS_SQL в этом отношении требуется больше ресурсов, потому что нужно вызвать пять или шесть процедур там, где достаточно одного выполнения оператора EXECUTE IMMEDIATE.
Однако пакет DBMS_SQL обеспечивает более высокую производительность, если его процедуры используются для многократного выполнения одного и того же проанализированного оператора. Оператор EXECUTE IMMEDIATE не позволяет «повторно использовать» проанализированные операторы. Он всегда разбирает оператор, и расходы ресурсов на повторные выполнения этой операции со временем превышают расходы на дополнительные вызовы процедур. Особое значение это приобретает в многопользовательской среде. Наконец, операторы EXECUTE IMMEDIATE и OPEN не позволяют обрабатывать массивы так же просто, как подпрограммы пакета DBMS_SQL и, как будет продемонстрировано, одно это может принципиально повлиять на производительность.