Я хотел бы посвятить эту статью памяти Роберта Куи (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-предложением: определение запросного списка операторов SELECT; извлечение отдельных строк после выполнения запроса; получение индивидуальных значений колонок в возвращаемом по запросу наборе.

Следующий ниже неимеющий названия блок на 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) Вставка 2.
"Несколько слов о PL/SQL и DDL SQL"
(A Word About PL/SQL and DDL SQL)

Примеры, приведенные на листинге 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, которая подсказала идею этой статьи.]