Встроенный динамический SQL

Встроенный динамический 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 :out'
 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 (с версии 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 :x';
 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 и, как
будет продемонстрировано, одно это может принципиально повлиять на производитель-
ность.