(Dynamic SQL Comes to PL/SQL. Using the DBMS_SQL Package, by Steve Bobrowski. ORACLE MAGAZINE, vol.IX/num.2)

Я хотел бы посвятить эту статью памяти Роберта Куи (Robert Kooi), моего друга и блестящего разработчика, внесшего значительный вклад в развитие сервера Oracle7, особенно в части PL/SQL.

Таблица 1.

Роль пакета 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;

[Прим. переводчика: отображение вывода хранимых процедур (функция 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;
/

Использование пакета 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)

• Каталог программ Oracle
• Задать вопрос ONLine по покупке программ в интернет магазине ITShop
• Подписаться на рассылку "СУБД Oracle "с нуля"