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 (с версии 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 и, как
будет продемонстрировано, одно это может принципиально повлиять на производитель-
ность.

set pagesize 30

set pause on

prompt He забудьте нажать ENTER, чтобы получить результа
Не забудьте нажать ENTER, чтобы получить результат

select text

 from all_source

 where name = 'DBMS_SQL'

 and type = 'PACKAGE'

order by line

/

TEXT

package dbms_sql is

-- OVERVIEW

-- This package provides a means to use dynamic SQL to access the

database.

-- RULES AND LIMITATIONS

Если необходимо узнать возможности или просмотреть примеры, используйте этот
прием для всех стандартных пакетов DBMS_ или UTL_.
Пакет DBMS_SQL реализует процедурный подход к использованию динамическо-
го SQL. Этот подход сходен с тем, который используется в других языках (например,при программировании на Java с использованием JDBC или на С с использованием биб-
лиотеки OCI) В общем случае, процесс, использующий пакет DBMS_SQL, будет иметь
следующую структуру.
• Вызов OPEN_CURSOR для получения дескриптора курсора.
• Вызов PARSE для анализа оператора. Один и тот же дескриптор курсора можно
использовать для обработки нескольких операторов. В каждый момент времени,
однако, обрабатывается только один оператор.
• Вызов BIND_VARIABLE или BIND_ARRAY для передачи входных данных опе-
ратору.
• Если обрабатывается запрос (оператор SELECT), необходимо вызвать процедуру
DEFINE_COLUMN или DEFINE_ARRAY, чтобы указать серверу Oracle, как пе-
редавать результаты (как массивы или как скалярные величины и какой тип дан-
ных при этом использовать).
• Вызов EXECUTE для выполнения оператора.
• Если выполняется запрос, необходимо вызвать FETCH_ROWS для выборки дан-
ных. Для получения данных по порядковому месту в списке выбора используется
вызов COLUMN_VALUE.
• Если же выполняется блок кода PL/SQL или оператор ЯМД с конструкцией
RETURN, можно вызвать процедуру VARIABLE_VALUE для получения резуль-
татов (параметров типа OUT) из блока по имени.
• Вызов CLOSE_CURSOR.
В следующем псевдокоде продемонстрирована последовательность шагов для дина-
мического выполнения запроса:
1) Открыть курсор
2) Проанализировать оператор
3) При необходимости получить описание оператора, чтобы выяснить
количество и типы возвращаемых столбцов
4) Выполнить цикл по i no связываемым переменным (входным)
Связать i-ую входную переменную с оператором
5) Выполнить цикл по i по возвращаемым столбцам
Определить i-ый столбец, сообщив серверу Oracle тип переменной, в
которую будут выбираться данные
6) Выполнить оператор
7) Выполнять цикл пока удается выбрать строку
Выполнить цикл по i по возвращаемым столбцам
Получить значение i-го столбца строки с помощью column_value
Конец цикла по строкам
9) Закрыть курсор
Для выполнения PL/SQL-блока или оператора ЯМД используется следующий псев-
докод:
1) Открыть курсор
2) Проанализировать оператор
3) Выполнить цикл по i по связываемым переменным (входным и выходным)Связать i-ую переменную с оператором
4) Выполнить оператор
5) Выполнить цикл по i по выходным связываемым переменным
Получить значение i-й выходной переменной с помощью variable_value
6) Закрыть курсор

Наконец, при выполнении операторов ЯОД (в которых нельзя использовать связы-
ваемые переменные), PL/SQL-блоков или операторов ЯМД, в которых нет связывае-
мых переменных, представленный выше алгоритм упрощается (хотя, для этого типа опе-
раторов я всегда предпочитаю использовать не пакет DBMS_SQL, а встроенный
динамический SQL):

1) Открыть курсор
2) Проанализировать оператор
3) Выполнить оператор
4) Закрыть курсор
Рассмотрим пример использования пакета DBMS_SQL для выполнения запроса, под-
считывающего количество строк в таблице базы данных, к которой пользователь имеет
доступ:

create or replace
 function get_row_cnts(p_tname in varchar2) return number

 as
l_theCursor integer;
l_columnValue number default NULL;
 l_status integer;
begin

-- Шаг 1, открыть курсор.
 theCursor := dbms_sql.open_cursor;

Мы начинаем блок с обработчиком исключительных ситуаций. Если по ходу работы
этого блока возникает ошибка, необходимо закрыть только что открытый курсор в об-
работчике исключительных ситуаций, чтобы предотвратить «утечку курсоров», когда дес-
криптор курсора теряется при распространении исключительной ситуации за пределы
функции.
begin

 -- Шаг 2, проанализировать запрос.

dbms_sql.parse(c => l_theCursor,

 statement => 'select count(*) from ' ||

->p_tname,

 language_flag => dbms_sql.native);

Обратите внимание, что параметр language_flag получает значение одной из констант
пакета DBMS_SQL.NATIVE. Это вызывает анализ запроса по правилам сервера, вы-
полняющего код. Можно также задать значения DBMS_SQL.V6 или DBMS_SQL.V7.
Я всегда использую значение NATIVE.

Шаги 3 и 4 из представленного ранее псевдокода нам не нужны, поскольку резуль-
таты известны и никаких связываемых переменных в этом примере нет.

– Шаг 5, убедиться, что запрос возвращает данные типа NUMBER.

 dbms_sql.define_column (с => l_theCursor,

 position => 1,

 column => l_columnValue);

Процедура DEFINE_COLUMN — перегруженная, так что компилятор сам опреде-
ляет, когда надо вызывать версию для типа NUMBER, а когда — для DATE или
VARCHAR.

 -- Шаг 6, выполнить оператор.

 l_status := dbms_sql.execute(l_theCursor);

Если бы выполнялся оператор ЯМД, переменная L_STATUS получила бы значение,
равное количеству возвращенных строк. Для оператора SELECT возвращаемое значе-
ние несущественно.

-- Шаг 7, выбрать строки.

 if (dbms_sql.fetch_rows(c => l_theCursor) > 0)

 then

-- Шаг 8, получить значения из очередной строки.

dbms_sql.column_value(c => l_theCursor,

position => 1,

value => l_columnValue);

 end if;

-- Шаг 9, закрыть курсор.

dbms_sql.close_cursor(c => l_theCursor);

return l_columnValue;

exception

when others then

dbms_output.put_line (' = = > ' || sqlerrm);

dbms_sql.close_cursor(с => l_theCursor);

RAISE;

end;

end;
/
Function created.
set serveroutput on
 begin
dbms_output.put_line('Emp has this many rows ' ||
get_row_cnts('emp'));

 end;

/

Emp has this many rows 14

PL/SQL procedure successfully completed.

 begin

dbms_output.put_line('Not a table has this many rows ' ||get_row_cnts('NOT_A_TABLE');

end;

/

ORA-00942: table or view does not exist

begin

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SCOTT.GET_ROW_CNTS", line 60

ORA-06512: at line 2

Рассмотренный пример начинается созданием курсора с помощью вызова
DBMS_SQL.OPEN_CURSOR. Следует отметить, что это специфический курсор
DBMS_SQL — его нельзя передать для выборки данных в приложение на Visual Basic
или использовать в качестве PL/SQL-курсора. Для выборки данных с помощью этого
курсора необходимо использовать подпрограммы пакета DBMS_SQL. Затем мы про-
анализировали запрос SELECT COUNT(*) FROM TABLE, где значение TABLE пере-
дается при вызове во время выполнения — оно просто конкатенируется со строкой зап-
роса. Приходится «вклеивать» имя таблицы в запрос, поскольку связываемые переменные
нельзя использовать в качестве идентификатора (имени таблицы или имени столб-
ца, например). После анализа запроса мы вызвали DBMS_SQL.DEFINE_COLUMN,
чтобы указать, что первый (и единственный в данном случае) столбец в списке
SELECT должен интерпретироваться при выборке как тип NUMBER. To, что мы
хотим выбирать данные именно этого типа, явно не указано — процедура
DBMS_SQL.DEFINE_COLUMN перегружена и имеет несколько версий для данных
типа VARCHAR, NUMBER, DATE, BLOB, CLOB и так далее. Тип возвращаемого зна-
чения определяется по типу переменной, в которую он помещается. Поскольку пере-
менная L_COLUMNVALUE в рассмотренном примере имеет тип NUMBER, вызыва-
ется версия процедуры DEFINE_COLUMN для чисел. Затем мы вызываем
DBMS_SQL.EXECUTE. Если бы выполнялся оператор INSERT, UPDATE или DELETE,
функция EXECUTE вернула бы количество затронутых строк. В случае запроса возвра-
щаемое значение функции не определено, и его можно проигнорировать. После выпол-
нения оператора вызывается функция DBMS_SQL.FETCH_ROWS. Функция
FETCH_ROWS возвращает количество фактически выбранных строк. В нашем случае,
поскольку связывались скалярные переменные (не массивы), функция FETCH_ROWS
будет возвращать 1 до тех пор, пока не исчерпаются данные, — тогда она вернет 0. При
извлечении каждой строки мы вызываем DBMS_SQL.COLUMN_VALUE для каждого
столбца в списке выбора, чтобы получить его значение. Наконец, мы завершаем выпол-
нение функции, закрывая курсор с помощью вызова DBMS_SQL.CLOSE_CURSOR.
Теперь рассмотрим, как использовать пакет DBMS_SQL для обработки динамичес-
ки формируемых параметризованных PL/SQL-блоков или операторов ЯМД. Я часто ис-
пользую такое динамическое формирование, например, при загрузке данных из файлов
операционной системы с помощью пакета UTL_FILE (он позволяет читать текстовые
файлы в PL/SQL). Пример подобного рода утилиты был представлен в главе 9. Там мы
использовали пакет DBMS_SQL для динамического построения операторов INSERT,
в которых количество столбцов становится известным только при выполнении и меня-

get_row_cnts('NOT_A_TABLE')>;

end;

 /
==> ORA-00942: table or view does not exist
begin
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.GET_ROW_CNTS", line 60
ORA-06512: at line 2

Рассмотренный пример начинается созданием курсора с помощью вызова
DBMS_SQL.OPEN_CURSOR. Следует отметить, что это специфический курсор
DBMS_SQL — его нельзя передать для выборки данных в приложение на Visual Basic
или использовать в качестве PL/SQL-курсора. Для выборки данных с помощью этого
курсора необходимо использовать подпрограммы пакета DBMS_SQL. Затем мы про-
анализировали запрос SELECT COUNT(*) FROM TABLE, где значение TABLE пере-
дается при вызове во время выполнения — оно просто конкатенируется со строкой зап-
роса. Приходится «вклеивать» имя таблицы в запрос, поскольку связываемые переменные
нельзя использовать в качестве идентификатора (имени таблицы или имени столб-
ца, например). После анализа запроса мы вызвали DBMS_SQL.DEFINE_COLUMN,
чтобы указать, что первый (и единственный в данном случае) столбец в списке
SELECT должен интерпретироваться при выборке как тип NUMBER. To, что мы
хотим выбирать данные именно этого типа, явно не указано — процедура
DBMS_SQL.DEFINE_COLUMN перегружена и имеет несколько версий для данных
типа VARCHAR, NUMBER, DATE, BLOB, CLOB и так далее. Тип возвращаемого зна-
чения определяется по типу переменной, в которую он помещается. Поскольку пере-
менная L_COLUMNVALUE в рассмотренном примере имеет тип NUMBER, вызыва-
ется версия процедуры DEFINE_COLUMN для чисел. Затем мы вызываем
DBMS_SQL.EXECUTE. Если бы выполнялся оператор INSERT, UPDATE или DELETE,
функция EXECUTE вернула бы количество затронутых строк. В случае запроса возвра-
щаемое значение функции не определено, и его можно проигнорировать. После выпол-
нения оператора вызывается функция DBMS_SQL.FETCH_ROWS. Функция
FETCH_ROWS возвращает количество фактически выбранных строк. В нашем случае,
поскольку связывались скалярные переменные (не массивы), функция FETCH_ROWS
будет возвращать 1 до тех пор, пока не исчерпаются данные, — тогда она вернет 0. При
извлечении каждой строки мы вызываем DBMS_SQL.COLUMN_VALUE для каждого
столбца в списке выбора, чтобы получить его значение. Наконец, мы завершаем выпол-
нение функции, закрывая курсор с помощью вызова DBMS_SQL.CLOSE_CURSOR.
Теперь рассмотрим, как использовать пакет DBMS_SQL для обработки динамичес-
ки формируемых параметризованных PL/SQL-блоков или операторов ЯМД. Я часто ис-
пользую такое динамическое формирование, например, при загрузке данных из файлов
операционной системы с помощью пакета UTL_FILE (он позволяет читать текстовые
файлы в PL/SQL). Пример подобного рода утилиты был представлен в главе 9. Там мы
использовали пакет DBMS_SQL для динамического построения операторов INSERT,
в которых количество столбцов становится известным только при выполнении и меня-ется от вызова к вызову. Нельзя использовать встроенный динамический SQL для заг-
рузки в таблицу произвольного количества столбцов, поскольку для этого уже на этапе
компиляции необходимо точно знать количество связываемых переменных. Следующий
пример создан специально, чтобы показать особенности использования подпрограмм па-
кета DBMS_SQL при работе с блоками PL/SQL и операторами ЯМД (это пример про-
ще реализовать с помощью встроенного динамического SQL, поскольку в этом случае
количество связываемых переменных известно во время компиляции):

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

 l_theCursor integer;

 l_columnValue number default NULL;

l_status integer;

l_update long;

begin

 l_update := 'update ' || p_owner || '.dept

set dname = :bvl, loc - :bv2

where deptno = to_number(:pk)

returning rowid into  ut';

-- Шаг 1, открыть курсор.

 l_theCursor := dbms_sql.open_cursor;

Начнем вложенный блок с обработчиком исключительных ситуаций. Если в этом
блоке кода возникнет ошибка, необходимо обработать ее как можно ближе к месту воз-
никновения и закрыть курсор, чтобы избежать «утечки курсоров», когда дескриптор
открытого курсора просто теряется при распространении ошибки за пределы подпрог-
раммы.

 begin

-- Шаг 2, проанализировать запрос.

dbms_sql.parse(c => l_theCursor,

statement => l_update,

language_flag => dbms_sql.native);

-- Шаг 3, связать все входные и выходные переменные.

dbms_sql.bind_variable(c => l_theCursor,

 name => ':bv1',

value => p_newDname);

 dbms_sql.bind_variable(c => l_theCursor,

 name => ':bv2',

value => p_newLoc);

dbms_sql.bind_variable(c => l_theCursor,

name => ':pk',

value => p_deptno);

 dbms_sql.bind_variable(c => l_theCursor,

 name => ':out',

value => p_rowid,

out_value_size => 4000);

Учтите, что, хотя возвращаемые переменные передаются как параметры в режиме
OUT, необходимо связать их перед выполнением. Необходимо также указать наиболь-
ший размер ожидаемого результата (OUT_VALUE_SIZE), чтобы сервер Oracle выделил
под него соответствующее пространство.

-- Шаг 4: выполнить оператор. Поскольку это оператор ЯМД,

-- в переменной L_STATUS окажется количество измененных строк.

-- Именно это значение мы и возвращаем.

 l_status := dbms_sql.execute(l_theCursor);

-- Шаг 5: выбрать OUT-переменные из результатов выполнения.

 dbms_sql.variable_value(c => l_theCursor,

 name => ':out',

value => p_rowid);

-- Шаг 6: закрыть курсор.

 dbms_sql.close_cursor(с => l_theCursor);

return l_columnValue;

 exception

 when dup_val_on_index then

dbms_output.put_line('==> ' || sqlerrm);

dbms_sql.close_cursor(с => l_theCursor);

RAISE;

end;

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.

Итак, я продемонстрировал особенности использования пакета DBMS_SQL для вы-
полнения блока кода с передачей входных данных и выборкой результатов. Повторю
еще раз: представленный выше блок кода лучше реализовать с помощью встроенного
динамического SQL (чуть ниже мы так и сделаем). Подпрограммы пакета DBMS_SQL
в нем применялись в основном для демонстрации использования соответствующего фун-
кционального интерфейса. В других главах книги, в частности в главе 9, посвященной
загрузке данных, продемонстрировано, почему пакет DBMS_SQL по-прежнему ши-
роко используется. В главе 9 рассмотрен код программ загрузки и выгрузки данных
на PL/SQL. В них средства DBMS_SQL используются в полном объеме, позволяя об-
рабатывать неизвестное количество столбцов любого типа как во входных данных (для
операторов INSERT), так и в результатах (для операторов SELECT).
Мы рассмотрели примерно 75 процентов функциональных возможностей пакета
DBMS_SQL. Чуть позже, многократно выполняя один и тот же динамически сформи-
рованный оператор, мы рассмотрим взаимодействие с помощью массивов и сравним ис-
пользование пакета DBMS_SQL и встроенного динамического SQL. Пока, однако, мы
завершим обзор пакета DBMS_SQL. Полный список входящих в него подпрограмм и
описание их входных/выходных параметров можно найти в руководстве Oracle8i Supplied
PL/SQL Packages Reference, где отдельно рассмотрена каждая подпрограмма.

• Разработка обобщенных процедур, выполняющих стандартные действия вроде
выгрузки данных в файлы.
• Разработка универсальных процедур загрузки данных в не известные заранее таб-
лицы.
• Динамический вызов других PL/SQL-процедур во время выполнения.
• Генерация условий (например, конструкции WHERE) в процессе работы на ос-
нове введенных пользователем данных. Это, пожалуй, основная причина исполь-
зования динамического SQL большинством разработчиков.
• Выполнение операторов ЯОД. Поскольку PL/SQL не разрешает включать стати-
ческие операторы ЯОД в код приложения, остается использовать динамический
SQL. Это позволит выполнять операторы, начинающиеся с ключевых слов
CREATE, ALTER, GRANT, DROP и т.п.

Решаться перечисленные задачи будут с помощью двух средств языка PL/SQL.
Сначала мы рассмотрим использование стандартного пакета DBMS_SQL. Этот па-
кет существует уже достаточно давно, он появился в версии 7.1. Пакет обеспечивает
процедурный метод выполнения динамического SQL, аналогичный использованию
функциональных интерфейсов (таких как JDBC или ODBC). Затем поговорим о
встроенном динамическим SQL (который реализуется в PL/SQL оператором EXECUTE
IMMEDIATE). Это декларативный способ выполнения динамического SQL в языке PL/
SQL и в большинстве случаев он синтаксически намного проще, чем использование
пакета DBMS_SQL; кроме того, он обеспечивает более высокую производительность.
Учтите, что многие подпрограммы пакета DBMS_SQL по-прежнему являются жиз-
ненно важными и активно используются в PL/SQL. Мы сравним два метода и попыта-
емся четко сформулировать, когда имеет смысл использовать каждый из них. Как толь-
ко стало понятно, что необходимо использовать динамический SQL (статический SQL —
лучший выбор в большинстве случаев), придется выбирать реализацию на основе паке-
та DBMS_SQL или встроенного динамического SQL.
Пакет DBMS_SQL необходимо использовать в следующих случаях.

• Если заранее не известно количество или типы столбцов, с которыми придется
работать. Пакет DBMS_SQL включает процедуры для описания результирующе-
го множества. Встроенный динамический SQL не позволяет получить такое опи-
сание. При использовании встроенного динамического SQL необходимо знать ха-
рактеристики результирующего множества при компиляции, если результаты
необходимо обрабатывать в PL/SQL.
• Если заранее не известно количество или типы связываемых переменных, с ко-
торыми придется работать. Пакет DBMS_SQL по ходу выполнения позволяет
привязать с помощью процедур входные переменные к операторам. Встроенный
динамический SQL требует учета количества и типов связываемых переменных
на этапе компиляции.
• Когда необходимо выбирать или вставлять тысячи строк и можно использовать
обработку массивов. Пакет DBMS_SQL поддерживает обработку массивов — воз-
можность выбрать N строк за раз, одним вызовом. Встроенный динамический SQL
обычно не позволяет этого сделать, но это ограничение можно обойти, как будет
показано далее.
• Если в сеансе многократно выполняется один и тот же оператор. Пакет
DBMS_SQL позволяет один раз разобрать оператор, а затем выполнять его мно-
гократно. При использовании встроенного динамического SQL мягкий разбор
будет осуществляться при каждом выполнении. Дополнительные повторные разборы нежелательны.

Встроенный динамический SQL имеет смысл использовать в следующих случаях.

• Когда количество и типы столбцов, с которыми придется работать, заранее изве-
стны.
• Когда заранее известно количество и типы связываемых переменных. (Можно так-
же использовать контексты приложений, чтобы с помощью более простого встро-
енного динамического SQL выполнять операторы с заранее неизвестным коли-
чеством или типами связываемых переменных.)
• Когда необходимо выполнять операторы ЯОД.
• Если динамически формируемые операторы будут выполняться лишь несколько
раз (оптимальный вариант — однократно).

create or replace procedure DynEmpProc(p_job in varchar2)
as
type refcursor is ref cursor;

 -- При использовании динамического SQL необходимо
 -- создать хост-переменные и выделить ресурсы.
 l_cursor refcursor;
 l_ename emp.ename%type;
 begin

 -- Начинаем с анализа запроса
 open l_cursor for
 'select ename
 from emp
 where job =  ' USING in p_job;
 loop
 -- и явно ВЫБИРАЕМ данные через курсор.
 fetch l_cursor into l_ename;

 -- Необходимо самостоятельно обрабатывать ошибки
-- и делать выборку
 exit when l_cursor%notfound;
 dbms_output.put_line(l_ename);
 end loop;

 -- He забываем освободить ресурсы
 close l_cursor;
 exception
 when others then
 -- а также перехватить и обработать все ошибки,
 -- чтобы не допустить утечки ресурсов
 -- при возникновении ошибок.
 if (l_cursor%isopen)
 then
 close l_cursor;
 end if;
 RAISE;
 end;
 /
Procedure created.

А вот что мы имеем в случае статического SQL:

create or replace procedure StaticEmpProc(p_job in varchar2)
as
begin
 for x in (select ename from emp where job = p_job)
 loop
 dbms_output.put_line(x.ename);
 end loop;
 end;
  /
Procedure created.

Эти две процедуры делают то же самое:

set serveroutput on size 1000000
exec DynEmpProc('CLERK')
SMITH
ADAMS
JAMES
MILLER
PL/SQL procedure successfully completed.

exec StaticEmpProc('CLERK')
SMITH
ADAMS
JAMBS
MILLER
PL/SQL procedure successfully completed.

Понятно, однако, что версия с динамическим SQL требует от разработчика написа-
ния гораздо большего объема кода. По опыту знаю: статический SQL обеспечивает бо-
лее высокую производительность труда программиста при написании кода (приложения
разрабатываются быстрее), но динамический SQL обеспечивает большую гибкость при
выполнении (программа в ходе работы может делать то, что не внесено в ее код явно).
Кроме того, статический SQL (особенно в среде PL/SQL) будет выполняться намного
эффективнее, чем динамический. Используя статический SQL, PL/SQL-машина при
обработке одной строки интерпретируемого кода может сделать то, на что потребуется
пять или шесть строк интерпретируемого кода с динамическим SQL. Поэтому я исполь-
зую статический SQL где только возможно и применяю динамический, только если по-
другому задачу решить нельзя. Оба они эффективны, ни один не имеет принципиаль-
ных преимуществ перед другим, и оба имеют свои специфические возможности и
средства повышения производительности.