Пакет DBMS_SQL

DBMS_SQL — это стандартный встроенный пакет, поставляемый вместе с серве-
ром. Стандартно он устанавливается в схеме пользователя SYS, а привилегия для его
выполнения предоставляется роли PUBLIC. Это означает, что не должно быть никаких
проблем с доступом к нему или созданием хранимых объектов, ссылающихся на его
процедуры, — никаких дополнительных или специальных привилегий для этого предо-
ставлять не надо. Одним из положительных свойств пакета является доступность соот-
ветствующей документации. Если при использовании 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) Выполнять цикл пока удается выбрать строку
8) Выполнить цикл по 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 :out';
-- Шаг 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, где отдельно рассмотрена каждая подпрограмма.