Я хотел бы посвятить эту статью памяти Роберта Куи (Robert Kooi), моего друга и блестящего разработчика, внесшего значительный вклад в развитие сервера Oracle7, особенно в части PL/SQL.
Администраторы и разработчики часто пренебрегают теми преимуществами, которые можно получить от применения некоторых выдающихся, но скрытых драгоценных возможностей, которыми обладает сервер Oracle7. Я прежде всего имею в виду пакеты утилит DBMS, которые позволяют разработчикам приложений применять расширенные средства сервера базы данных, такие как сигналы (alerts), коммуникационные каналы (communication pipes) и управляемые сервером блокировки ресурсов (server-managed resource locking). В Oracle7 версии 7.1 имеется поистине фантастическое добавление к семейству пакетов DBMS: новый пакет DBMS_SQL обеспечивает разработчиков инструментарием для создания динамически формируемых предложений SQL в программах на PL/SQL.
Статические и динамические предложения SQL
Многие из современных приложений Oracle на компьютерах-клиентах содержат только статические SQL-предложения. По этому сценарию разработчик конструирует некое приложение посредством планирования возможных транзакций и последующего включения в приложение соответствующих SQL-предложений (*). Следовательно, приложение ограничено некоторым количеством четко определенных обращений к базе данных, которые фактически компилируются как часть приложения.
(*) [Примечание автора: Когда разрабатывается сложное производственное приложение для работы в режиме клиент/сервер, обычно для этого приложения кодируется много SQL-предложений в хранимых процедурах, которые затем реагируют на вызовы приложения, чтобы обеспечить работу процедур с базой данных. Однако, поскольку скомпилированные приложения обладают лишь статическими вызовами процедур, а SQL-предложения в хранимых в базе данных процедурах также уже скомпилированы, то общее положение о том, что все такие приложения являются статическими, остается в силе.]
Этот метод использования статических SQL-предложений может быть хорош или плох в зависимости от того, что Вы хотите от приложения. Для примера, механизм статических SQL-предложений безупречен, когда Вы хотите сконструировать приложение, которое жестко контролирует, что пользователи могут или не могут делать. Поскольку Вы встраиваете все возможные SQL-предложения в это приложение, пользователи не могут сбиться с предназначенного им пути. Но когда Вы хотите разработать приложение, которое обладало бы гибкостью, обеспечивающей управление непредусмотренными транзакциями, SQL-предложения или даже наборы SQL-предложений (имея в виду статические SQL-предложения и их характерологические особенности), не могут помочь Вам. Таким образом, возникает потребность в динамически формируемых SQL-предложениях.
Используя метод динамически формируемых SQL-предложений, приложения строят их во время исполнения (runtine), и в этом смысле Вы можете создавать приложения, которые изменяются по выбору, определяемому пользователем. В качестве общедоступного интерфейса к динамически формируемым SQL-предложениям можно представить себе, например, инструментарий типа SQL*Plus, при помощи которого пользователи могут работать с SQL-предложениями любого типа.
Введение в динамически формируемые SQL-предложения
Основным положением в понимании использования динамических SQL-предложений является то, что если некоторое SQL-предложение является динамически формируемым, то программа должна построить правильное SQL-предложение, выполняя последовательность определенных шагов, формирующих это предложение; выполнить его, а в случае, если это SQL-предложение представляет собой запрос к базе данных [запросное и незапросные предложения - см. таблицу 1. Прим. переводчика], а также определить его колонки и выборку строк возвращаемого набора. Тип выборки и число шагов, требуемое для выполнения динамических SQL-предложений, изменяется в зависимости от типа SQL-предложения, что и вынуждает программу определенным образом определять и выполнять эти предложения. Различные возможности выделили четыре формальных метода формирования динамических SQL-предложений. Таблица 1 кратко излагает некоторые особенности каждого метода. В последней колонке таблицы 1 приводятся последовательности списков вызовов подпрограмм пакета DBMS_SQL, которые программа на PL/SQL должна выполнить, чтобы реализовать динамически формируемые SQL-предложения каждого типа.
Если Вы еще не очень свободно владеете механизмом формирования динамических SQL-предложений и хотели бы получить больше информации, рекомендую обратиться к главе о динамических SQL-предложениях в Руководстве "Programmer`s Guide to the Oracle Precompilers" (Руководство программиста по прекомпиляторам Oracle). Не дублируя сведений, которые можно получить из документации, эта статья фокусирует внимание читателей на использовании пакета DBMS_SQL, чтобы показать возможность применения динамически формируемых SQL-предложений в программах на PL/SQL.
Таблица 1.
| Метод | Тип предложения | Требуемые вызовы пакета DBMS_SQL |
| 1. | Незапросные (nonqueries), нет базовых переменных (no host variables), выполняется однократно(execute once) | открытие курсора (open cursor), разборка (parse), выполнение (execute),закрытие курсора (close cursor). |
| 2. | Незапросные, известное число базовых переменных (known nunber host variables), выполняются один или несколько раз (execute one or more times) | открытие курсора, разборка, связывание переменных (bind variables), выполнение, закрытие курсора. |
| 3. | Запросные (queries), известное число операторов SELECT и базовых переменных (known nunber of SELECT list items and host variables) | открытие курсора, разборка, связывание переменных, определение колонок (define columns), выполнение, выборка строк (fetch rows), получение значений колонок (get column values), обновление (refretch), ... закрытие курсора. |
| 4. | Запросные, неизвестное число операторов SELECT и базовых переменных колонок (unknown nunber of SELECT list items and column host variables) | открытие курсора, разборка, связывание переменных, определение колонок, выполнение, выборка строк, получение значений колонок, обновление, ... закрытие курсора. |
Роль пакета DBMS_SQL
Пакет DBMS_SQL включает много процедур и функций, которые обеспечивают процедурный интерфейс на PL/SQL для реализации различных этапов определения и выполнения динамически формируемых SQL и PL/SQL предложений в хранимых процедурах, функциях и пакетах. Вставка "Краткая сводка DBMS_SQL API" являет собой в сжатом концентрированном виде справочник (quick-reference) по наиболее общим и часто используемым процедурам и функциям программного интерфейса приложений (API - application programming interface), которым является пакет DBMS_SQL.
Для более полной характеристики конструкций, перечисленных во вставке "Краткая сводка DBMS_SQL API", рекомендую обратиться к Приложению к документации по Серверу Oracle7 (Oracle7 Server Documentation Addendum) или внимательно изучить скрипт, который Oracle7 выполняет, чтобы создать пакет DBMS_SQL (например, во всех UNIX-системах - это скрипт $ORACLE_HOME/rdbms/admin/dbmssql.sql).
Примеры простых и сложных процедур>
Давайте рассмотрим несколько примеров, иллюстрирующих использование пакета DBMS_SQL, чтобы выявить, как формируются динамические SQL-предложения в программах на PL/SQL.
На листинге 1 приведены две простые процедуры, которые некоторое приложение может использовать, чтобы создавать и удалять временные таблицы, которые можно индивидуально применять в сессии конкретного пользователя. Отметим, на что следует особо обратить внимание в этом листинге:
# Процедуры показывают, как используя пакет DBMS_SQL, динамически сформировать в хранимой процедуре SQL-предложения, относящиеся к первому типу (таблица 1);
# Процедуры показывают, как пакет DBMS_SQL позволяет программам на PL/SQL выполнить SQL-предложения языка определения данных (DDL - data_difinition_language) такие, как CREATE TABLE и DROP TABLE (см. вставку "Несколько слов о PL/SQL и DDL SQL");
# Процедуры применяют функцию UNIQUE_SESSION_ID другого пакета утилит DBMS_SESSION, чтобы идентифицировать и использовать уникальный идентификатор сессии конкретного пользователя, который вызвал эти процедуры.
ЛИСТИНГ 1.
Использование пакета DBMS_SQL для формирования динамических DDL
SQL-предложений внутри хранимой процедуры (по методу 1)
CREATE PROCEDURE create_temp_dept
(tname IN OUT VARCHAR2)
AS
cur INTEGER; - хранит идентификатор (ID) курсора
ret INTEGER; - хранит возвращаемое по вызову значение
str VARCHAR2(250); - хранит команды
BEGIN
-
- генерация временной таблицы по имени DEPT, используя заранее
- заданное (hard-coded) имя
- и возврат значения функции
DBMS_SESSION.UNIQUE_SESSION_ID
-
tname := dept_t || dbms_session.unique_session_id
-
- генерация команды CREATE TABLE по заранее заданному тексту
- и переменной tname
-
str := 'CREATE TABLE '||tname
|| ' (deptno INTEGER,'
|| ' dname VARCHAR2(14),'
|| ' loc VARCHAR2(13), '
|| 'TABLESPACE temp '
|| 'STORAGE ('
|| 'INITIAL 10K NEXT 10K MAXEXTENTS 2 )';
-
- Динамически формируемое DDL SQL-предложение по методу 1
-
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, str, dbms_sql.v7);
ret := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
END;
CREATE PROCEDURE drop_temp_dept
(tname IN OUT VARCHAR2)
AS
cur INTEGER; - хранит идентификатор (ID) курсора
ret INTEGER; - хранит возвращаемое по вызову значение
str VARCHAR2(250); - хранит команды
BEGIN
-
- генерация временной таблицы по имени DEPT, используя заранее
- заданное (hard-coded) имя
- и возврат значения функции
DBMS_SESSION.UNIQUE_SESSION_ID
-
tname := dept_t || dbms_session.unique_session_id;
-
- генерация команды DROP TABLE по заранее заданному тексту
- и переменной tname
-
str := 'DROP TABLE '||tname;
-
- Динамически формируемое DDL SQL-предложение по методу 1
-
cur := dbms_sql.open_currsor;
dbms_sql.parse(cur. str, dbms_sql.v7);
ret := dbms_sql.execute(cur);
dbms_sql.close_currsor(cur);
END;
Как можно увидеть из примера, приведенного на листинге 1, динамическое формирование SQL-предложения по методу 1 при помощи пакета DBMS_SQL требует всего несколько действий и очень просто в реализации. Приведенный ниже безымянный блок на PL/SQL служит простой иллюстрацией, как инструментальные средства SQL*Plus или SQL*DBA вызывают хранимые процедуры CREATE_TEMP_DEPT и DROP_TEMP_DEPT, чтобы создать или уничтожить временную таблицу DEPT в период сессии с базой данных. Перед выполнением этого блока не забудьте установить переменную среды SQL*Plus serveroutput в положение ON.
[Прим. переводчика: отображение вывода хранимых процедур (функция DBMS_OUTPUT.PUT_LINE) в SQL*Plus опредяется переменной
set serverout[put] {ON|OFF} [SIZE n]
где SIZE - количество байтов вывода, сколько буферируется сервером Oracle7. По умолчанию это значение равно 2000, но не может превышать 1,000,000. Вывод отображается после выполнения сервером Oracle7 блока на PL/SQL.]
DECLARE
x VARCHAR2(50);
BEGIN
create_temp_dept(x);
dbms_output.put_line(x ||' table created') ;
drop_temp_dept(x);
dbms_output.put_line(x ||' table dropped') ;
END;
/
Теперь давайте рассмотрим несколько более сложный пример, который показывает, как представить динамически формируемое SQL-предложение для запроса в хранимой функции. Функция DEPT_LIST_BUILDER, приведенная на Листинге 2, показывает, как создать функцию, которая строит выходной буфер, содержащий список ограничений номеров отделов и имен клиентов приложения.
ЛИСТИНГ 2.
Использование пакета DBMS_SQL для формирования динамического SQL-предложения внутри хранимой функции DEPT_LIST_BUILDER (по методу 3)
CREATE FUNCTION dept_list_builder
(loc_col IN CHAR DEFAULT 'Y' )
RETURN LONG
AS
stmt VARCHAR2(250); - хранит команду
select_list VARCHAR2(50); - хранит список выборки
deptid INTEGER; - хранит список данных deptno
deptnm VARCHAR2(14); - хранит список данных dname
deptlc VARCHAR2(13); - хранит список данных loc
cur INTEGER ; - хранит идентификатор курсора
ret INTEGER; - хранит возвращаемое по вызову значение
output LONG(32760); - хранит список ограничений
BEGIN
-
- Построение списка SELECT с использованием значения флажка loc_col .
- Список запросов SELECT всегда содержит колонки DETNO и DNAME .
-
select_lst := ' deptno, dname';
IF UPPER(loc_col) = 'Y' THEN
select_list := select_list ||' ,loc';
END IF;
-
- Построение команды SELECT. Владелец процедуры должен иметь
- привилегию SELECT для таблицы SCOTT.DEPT
-
stmt := 'SELECT ' || select_list
||' FROM scott.dept ORDER BY deptno';
-
- Динамическое формирование SQL-предложения по методу 3
-
- Открытие курсора и разборка запроса
-
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, str, dbms_sql.v7);
-
- Определение колонок в запросе
-
dbms_sql.define_column(cur,1,deptid);
dbms_sql.define_column(cur,2,deptnm,14);
IF UPPER(loc_col) = 'Y' THEN
dbms_sql.define_column(cur,3,deptlc,13);
END IF;
-
- Выполнение запроса
-
ret := dbms_sql.execute(cur);
-
- Извлечение записей, разграничение и помещение списка
- в выходной буфер
- ',' - ограничитель указывает на конец поля .
- ';' - ограничитель указывает на конец записи .
-
LOOP
IF dbms_sql.fetch_row(cur) > 0 THEN
dbms_sql.column_value(cur,1,deptid);
dbms_sql.column_value(cur,2,deptnm);
output := output || deptid || ',' || deptnm;
IF UPPER(loc_col) = 'Y' THEN
dbms_sql.column_value(cur,3,deptlc);
output := output || ',' || deptlc;
END IF;
output := output ||':';
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(cur);
RETURN output;
END dept_list_builder;
Отметим, что функция DEPT_LIST_BUILDER демонстрирует дополнительные действия, требуемые для исполнения запроса с динамически формируемым SQL-предложением:
Следующий ниже неимеющий названия блок на PL/SQL показывает, как вызвать функцию и получить возвращенное значение функции DEPT_LIST_BUILDER, применяя SQL*Plus или SQL*DBA. Перед выполнением этого блока не забудьте установить переменную serveroutput в положение ON.
DECLARE
outputbuffer VARCHAR2(2000);
BEGIN
outputbuffer := dept_list_builder('Y');
-
- Попробуйте выполнить вышеприведенный вызов с значением 'N',
- чтобы самим увидеть динамическое SQL-предложение в действии
-
dbms_output.put_line(outputbuffer);
END;
Заключение
Реализация динамически формируемых SQL-предложений в программах на PL/SQL
является достаточно простым действием, если только Вы поняли, как определять и
выполнять различные типы динамически формируемых SQL-предложений, используя
процедуры и функции пакета DBMS_SQL. Хотя эта статья не является руководством по
динамическим SQL-предложениям и пакету DBMS_SQL, можно надеяться, что
представленная здесь информация послужит расширению Ваших знаний в обоих
направлениях, а результатом будет Ваша возможность лучше конструировать
приложения, работающие с базами данных Oracle7.
Вставка 1.
"Краткая сводка DBMS_SQL API"
(A Quick Reference to the DBMS_SQL API)
Эта таблица описывает функции пакета DBMS_SQL,
которые используются наиболее часто.
Вырвите (лучше отксерьте - Ред.) ее и поместите возле своего компьютера,
чтобы использовать в качестве удобного справочника.
ПРОЦЕДУРЫ, ФУНКЦИИ И ПРОЧЕЕ ОПИСАНИЕ
V6 CONSTANT INTEGER := 0 Пакет DBMS_SQL содержит три глобальные
NATIVE CONSTANT INTEGER := 1 константы: V6=0, NATIVE=1 и V7=2.
V7 CONSTANT INTEGER := 2 Использование констант указывает
на применение определенного языка,
когда вызывается некоторая процедура
из пакета DBMS_SQL. V6 и V7 определяют
поведение SQL-предложения по правилам
Oracle6 или Oracle7 соответственно.
NATIVE определяет поведение предложения
согласно той версии базы данных,
с которой программа соединена в
настоящий момент времени.
BIND_VARIABLE Связывает значение c переменной
(cursor IN INTEGER, в предложении, разбираемом в курсоре.
variable IN VARCHAR2, Когда переменная является входной или
value IN NUMBER|VARCHAR2| входно/выходной, связанное значение
DATE|MLSLABEL должно быть правильно определено.
[ , size IN INTEGER ] ) Если переменная - выходная, вызов
игнорирует связанное значение.
CLOSE_CURSOR Закрывает открытый курсор.
(cursor IN OUT INTEGER)
COLUMN_VALUE Получает значение колонки. Используйте
(cursor IN INTEGER, эту процедуру для доступа к данным,
possition IN INTEGER, предварительно выбранным посредством
value OUT NUMBER| VARCHAR2| вызова функции FETCH_ROWS.
DATE|MLSLABEL
[, error OUT NUMBER,
, length OUT INTEGER ] )
DEFINE_COLUMN Определяет колонку, указанную
(cursor IN INTEGER, в курсоре. Эта процедура используется
possition IN INTEGER, только в SELECT-курсорах.
value IN NUMBER| VARCHAR2|
DATE|MLSLABEL
[ , size IN INTEGER ] )
EXECUTE Выполняет предложение, находящееся в
(cursor IN INTEGER) курсоре и возвращает число обработанных
RETURN INTEGER в процессе выполнения строк.
EXECUTE_AND_FETCH Выполняет курсор и затем
(cursor IN INTEGER, извлекает первую строку из курсора.
exact IN BOOLEAN EXECUTE_AND_FETCH дублирует действия
DEFAULT FAULSE) в последовательности вызовов EXECUTE и
RETURN INTEGER одиночного FETCH_ROWS, но реализуется
с меньшим обменом сообщениями между
клиентом и сервером. Эта функция
полезна, когда программа намеревается
выполнить курсор и затем извлечь из
него только одну запись.
FETCH_ROWS Извлекает строку из курсора. После
(cursor IN INTEGER) извлечения строки в локальный буфер,
RETURN INTEGER применяя FETCH_ROWS, программа должна
вызвать процедуру COLUMN_VALUE, чтобы
прочитать извлеченную строку. Программа
может неоднократно использовать функцию
FETCH_ROWS, чтобы извлекать строки из
курсора, до тех пор пока не будут исчерпаны
все строки.
IS_OPEN Проверяет открыт ли курсор.
(cursor IN INTEGER)
RETURN BOOLEAN
LAST_ERROR_POSITION Вызывайте эту функцию после выполнения
RETURN INTEGER операции курсора. Если имеет место
ошибка во время выполнения операции,
находящейся в курсоре, функция возвращает
относительную позицию колонки в
курсорном предложении, которая
послужила причиной ошибки.
LAST_ROW_COUNT Вызывайте эту функцию после выполнения
RETURN INTEGER операции курсора. Функция возвращает
суммарное количество строк, извлеченных
до сих пор из курсора.
LAST_ROW_ID Вызывайте эту функцию после выполнения
RETURN ROWID операции курсора. Функция возвращает
значение ROWID последней строки,
обработанной в курсоре.
LAST_SQL_FUNCTION_CODE Вызывайте эту функцию после выполнения
RETURN INTEGER операции курсора. Функция возвращает
код функции SQL-предложения.
OPEN_CURSOR Открывает новый курсор. Когда больше не
RETURN INTEGER требуется, необходимо закрыть открытый
курсор, используя функцию CLOSE_CURSOR.
PARSE Немедленная разборка предложения в
(cursor IN INTEGER, курсоре. Если разбираемое предложение
statement IN VARCHAR2, является DDL-предложением, процедура
language IN INTEGER) также выполняет это DDL-предложение.
VARIABLE_VALUE Получает одно или несколько
(cursor IN INTEGER, значений переменной в курсоре.
variable IN VARCHAR2,
value OUT NUMBER|VARCHAR2|
DATE|MLSLABEL)
Примеры, приведенные на листинге 1, показывают, как обойти стороной ограниченность PL/SQL в Oracle7 версии 7.1, а именно, отсутствие поддержки DDL (data definition language - язык определения данных) SQL-предложений. Не удивительно ли Вам, почему PL/SQL непосредственно не поддерживает предложения DDL SQL? Для того, чтобы ответить на этот вопрос, рассмотрим, как реагирует Oracle7, когда Вы создаете программу на PL/SQL.
Когда компилируется программа PL/SQL. Oracle7 производит больше, чем просто проверку синтаксиса предложений - он также проверяет зависимости объектов (object dependencies) базы данных и проверку полномочий на право доступа (security auhorizations - авторизационную защиту), чтобы удостовериться, что программа на PL/SQL сделана правильно. Кроме того, для хранимых в базе данных PL/SQL-программ, таких как процедуры и триггеры, Oracle7 автоматически сохраняет путь по цепочкам объектных зависимостей (track of object-dependency chains), так что сервер может при необходимости автоматически сделать недействительными (invalidate) или переправить (revalidate) объекты, которые зависят друг от друга. Это встроенная в сервер возможность снимает неудобство от необходимости ручного сохранения пути объектных зависимостей и ручной проверки или рекомпиляции объектов, когда имеет место что-либо простое, как например, модификация таблицы. Когда же схемы сложных приложений имеют много зависимых между собой объектов, ручное управление объектной зависимостью может стать задачей исключительной значимости.
Теперь, помня об имеющемся в Oracle7 автоматическом механизме поддержания объектных зависимостей, рассмотрим, что случилось бы, если PL/SQL непосредственно поддерживал бы DDL SQL-предложения. При наличии такой возможности программа на PL/SQL могла бы, среди прочего, создавать объекто-подобные (database-objectlike) таблицы базы данных. Но этот сценарий содержит парадокс - Oracle7 не разрешает построения правильной программа на PL/SQL, которая зависела бы от еще не существующих объектов базы данных. Этот пример демонстрирует простую мысль, что чтобы что-либо получить, чаще всего приходится от чего-то отказываться. В случае с PL/SQL программисты Oracle7 обычно выбирают отказ от возможности применения DDL SQL-предложений и взамен получают автоматический механизм проверки объектных зависимостей и правильности программ.
В Oracle7 версии 7.1 пакет DBMS_SQL обеспечивает удобное средство, чтобы обойти это ограничение DDL в PL/SQL без подрыва Oracle7-механизма поддержки обеспечения объектных зависимостей. Поскольку предложения DDL SQL являются внутренними по отношению к программе PL/SQL, поскольку они, динамически формируемые, строятся во время выполнения, поэтому Oracle7 может допустить правильность построения программы. Следует, однако, понимать, что когда программа на PL/SQL использует пакет DBMS_SQL, чтобы построить предложения DDL SQL, программа должна брать на себя ответственность за возможные ошибки, которые могут быть результатом нарушения объектных зависимостей и прав доступа, которые Oracle7 не проверяет во время компиляции.
[Об авторе: Стив Бобровски - президент софтверной компании Animated Learning, которая специализируется в области разработки мультимедийных обучающих программ для освоения технологии клиент/сервер. Он - автор книги "Mastering Oracle7 & Client/Server Computing" (Sybex, 1994). (Эту книгу вы можете купить в "Книжной лавке" ЕАГПО. Редакторы журнала SELECT признали эту книгу лучшей из всех книг, не входящих в документацию по Oracle7. Кстати, Стив Бобровски был основным автором этой документации. Наша просьба ("Мир Oracle") разрешить перепечатку одной из глав книги "Mastering Oracle7 & Client/Server Computing" в нашем бюллетене утонула в недрах издательства Sybex, которому принадлежат права на эту книгу. Вслед за редакторами Select мы также рекомендуем вам эту книгу. - Ред. "Мир Oracle"). Вы можете связаться с ним по телефону 408.688.7735 или по Internet-адресу stevebob@netcom.com. Автор благодарит консультанта корпорации Oracle Gail Turk, которая подсказала идею этой статьи.]