Я хотел бы посвятить эту статью памяти Роберта Куи (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 Gu >Таблица 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_ >ЛИСТИНГ 1.
Использование пакета DBMS_SQL для формирования динамических DDL SQL-предложений внутри хранимой процедуры (по методу 1)
Как можно увидеть из примера, приведенного на листинге 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 опредяется переменной
где SIZE — количество байтов вывода, сколько буферируется сервером Oracle7. По умолчанию это значение равно 2000, но не может превышать 1,000,000. Вывод отображается после выполнения сервером Oracle7 блока на PL/SQL.]
Теперь давайте рассмотрим несколько более сложный пример, который показывает, как представить динамически формируемое SQL-предложение для запроса в хранимой функции. Функция DEPT_LIST_BUILDER, приведенная на Листинге 2, показывает, как создать функцию, которая строит выходной буфер, содержащий список ограничений номеров отделов и имен клиентов приложения.
Использование пакета DBMS_SQL для формирования динамического SQL-предложения внутри хранимой функции DEPT_LIST_BUILDER (по методу 3)
Отметим, что функция DEPT_LIST_BUILDER демонстрирует дополнительные действия, требуемые для исполнения запроса с динамически формируемым SQL-предложением:
- определение запросного списка операторов SELECT;
- извлечение отдельных строк после выполнения запроса;
- получение индивидуальных значений колонок в
- возвращаемом по запросу наборе.
Следующий ниже неимеющий названия блок на PL/SQL показывает, как вызвать функцию и получить возвращенное значение функции DEPT_LIST_BUILDER, применяя SQL*Plus или SQL*DBA. Перед выполнением этого блока не забудьте установить переменную serveroutput в положение ON.
Реализация динамически формируемых SQL-предложений в программах на PL/SQL является достаточно простым действием, если только Вы поняли, как определять и выполнять различные типы динамически формируемых SQL-предложений, используя процедуры и функции пакета DBMS_SQL. Хотя эта статья не является руководством по динамическим SQL-предложениям и пакету DBMS_SQL, можно надеяться, что представленная здесь информация послужит расширению Ваших знаний в обоих направлениях, а результатом будет Ваша возможность лучше конструировать приложения, работающие с базами данных Oracle7.
Вставка 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), так что сервер может при необходимости автоматически сделать недействительными (inval >Теперь, помня об имеющемся в 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 не проверяет во время компиляции.
Есть проблема с использованием DBMS_SQL . Есть запрос который читает 4-е столбца. 3-и из них типа number и последний varchar2 . Раньше этого поля не было и процедура нормально работала. Но теперь пришлось добавить и почему то DBMS_SQL не пропускает 4-й столбец потому, что даёт ему тип PLS_INTEGER .
Как быть в таком случае? Вот код:
Валиться процедура на строчке while . Как раз тогда, когда fetch_rows пытается выгрести данные по строкам. Что можно сделать? Как нужно переделать последний параметр в DEFINE_COLUMNS так, что бы всё заработало? Я так понимаю в нём дело. Вот мой запрос:
В нём выбирается 4 столбца. И вот как раз их нужно использовать в DBMS_SQL.DEFINE_COLUMN как это сейчас делается у меня в цикле.
Вот вся процедура:
1 ответ 1
Ваша проблема в этой строчке:
В rec_tab (colid).col_type после describe_columns содержится численное представление типа полей, как то varchar2 -> 1 , number -> 2 , date -> 12 и т.д..
Функция define_column перегружена для всех основных типов и ожидает действительно переменную того типа, который будет в fetch_rows , а не rec_tab (colid).col_type , тип которой binary_integer . Здесь важен именно тип, можно использовать ту же переменную, которая будет в последствии использоватся в column_value , но это не обязательно. Функция describe_columns и определение rec_tab больше не используются.
Процедура будет выглядеть где-то так:
Оракловая документация не всегда лучший истоник, но основное там описано.
Второй подход к решению
Обдумайте ещё раз дизайн: DBMS_SQL нужен только тогда, когда действительно запрос динамический, т.е. ни колличество полей, ни их тип, заранее не известен. Если это не тот случай, то DBMS_SQL совершенно излишен. В вашем случае:
Часто при решении задач системной интеграции требуется представить некоторый объем данных в том или ином формате. При этом потребителем данных может быть кто угодно, а вот источником почти всегда является корпоративная база данных. К примеру, производитель может требовать у поставщика периодические отчеты о движении своих товаров в формате XLSX или XML, etc.
Существует множество инструментальных средств для преобразования данных в различные форматы, и возможность их использования зависит от принятого на предприятии технологического стека и программной архитектуры. При этом всегда хочется, чтобы цепочка, состоящая из различных библиотек, фреймворков, системных слоев, используемых для преобразования исходных данных, была как можно короче. Это бы сократило временные затраты на разработку решения и повысило его производительную эффективность.
Если принять, что по факту в корне процесса выборки данных лежит SQL-запрос, то в идеале цепочку преобразований хотелось бы видеть такой:
где
— исходные данные,
— SQL-запрос на выборку данных,
— функция, которая преобразует выборку в требуемый формат,
— данные в требуемом формате.
Для Oracle PL/SQL существует ряд встроенных и сторонних пакетов, которые реализуют подобную функциональность. Это DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL/JSON и другие.
Однако, когда встал вопрос о преобразовании данных в формат CSV, готовых решений почему-то не нашлось. Пришлось делать самому, далее будет показано, как.
Создать инструмент (пакет PL/SQL), который на входе принимает произвольный SELECT-запрос в виде строки или в виде курсорной переменной, а на выходе возвращает объект типа CLOB, инкапсулирующий данные в формате CSV. В случае любой ошибки должен возвращаться NULL. Сам формат CSV в представлении не нуждается — это строки, элементы которых разделены некоторым символом, чаще всего ";", но в общем случае в качестве разделителя может выступать произвольный символ. Примем, что для разделения строк используются символы 0x0D + 0x0A. Первая строка в CSV-файле, как правило, является заголовочной и определяет имена столбцов.
Определим интерфейс пакета
Здесь две перегруженных процедуры, разница между ними в том, что одна из них принимает запрос в виде строки, а другая — в виде ссылки на курсор. Второй параметр является выходным, это требуемый результат в CLOB-объекте. Наконец, третий параметр — CSV-разделитель.
В реализации этих процедур нам поможет встроенный пакет DBMS_SQL, который позволяет работать с динамическими курсорами, когда заранее (на этапе компиляции) неизвестно, сколько именно столбцов задействовано в выборке.
Возможности DBMS_SQL позволяют динамически разбирать и выполнять произвольные запросы. Для процедуры, принимающей запрос в виде строки, это происходит так:
Для процедуры, принимающей курсорную переменную, все проще — начиная с 11-ой версии Oracle стало доступно преобразование «курсорная переменная → номер курсора SQL».
Функция DBMS_SQL.TO_CURSOR_NUMBER преобразует переменную REFCURSOR (сильно- или слаботипизированную) в номер курсора SQL, который затем может передаваться подпрограммам DBMS_SQL. При этом курсорная переменная должна быть открыта до ее передачи функции DBMS_SQL.TO_CURSOR_NUMBER.
Таким образом, оба варианта вызова свелись к получению номера динамического курсора и связанного с ним набора данных. Следующий этап — получение информации о столбцах этого набора и извлечение самих данных.
Пакет DMBS_SQL позволяет описывать столбцы динамического курсора, возвращая информацию о каждом столбце в ассоциативном массиве записей.
Для этого необходимо объявить коллекцию PL/SQL на базе типа коллекции DBMS_SQL.DESC_TAB (или DESC_TAB2, если запрос может возвращать имена столбцов, длина которых превышает 30 символов). После этого можно использовать методы коллекций для перебора таблицы и извлечения информации о курсоре.
Далее пакету DBMS_SQL необходимо сообщить тип каждого столбца, выбранного при помощи динамического запроса. Это делается посредством вызова DEFINE_COLUMN.
Во втором аргументе DEFINE_COLUMN передается число — последовательная позиция столбца в списке. Третий аргумент задает тип данных столбца курсора. В нем передается выражение соответствующего типа. Иначе говоря, DBMS_SQL.DEFINE_COLUMN передается не строка с названием типа (скажем, «VARCHAR2»), а переменная, определенная с типом VARCHAR2.
При определении столбца символьного типа в четвертом аргументе необходимо задать максимальную длину значений, которые могут загружаться в курсор.
Подготовительные операции завершены, теперь можно сформировать строку заголовка и начинать извлекать данные.
Данные извлекаются построчно с помощью DBMS_SQL.FETCH_ROWS и последующих вызовов DBMS_SQL.COLUMN_VALUE для получения значения отдельных столбцов.
Далее остается только собрать результирующий CSV
И закрыть курсор
Варианты использования пакета
В разработке помогла книга
Фейерштейн С., Прибыл Б. — Oracle PL/SQL. Для профессионалов.
