Динамический SQL 1207

функциональных интерфейсов (таких как JDBC или ODBC). Затем поговорим о встроенном динамическим SQL (который реализуется в PL/SQL оператором EXECUTE IMMEDIATE). Это декларативный способ выполнения динамического SQL в языке PL/ SQL и в большинстве случаев он синтаксически намного проще, чем использование пакета DBMS SQL; кроме того, он обеспечивает более высокую производительность.

Учтите, что многие подпрограммы пакета DBMS SQL по-прежнему являются жизненно важными и активно используются в PL/SQL. Мы сравним два метода и попытаемся четко сформулировать, когда имеет смысл использовать каждый из них. Как только стало понятно, что необходимо использовать динамический SQL (статический SQL - лучший выбор в большинстве случаев), придется выбирать реализацию на основе пакета DBMS SQL или встроенного динамического SQL.

Пакет DBMS SQL необходимо использовать в следующих случаях.

Если заранее не известно количество или типы столбцов, с которыми придется работать. Пакет DBMS SQL включает процедуры для описания результирующего множества. Встроенный динамический SQL не позволяет получить такое описание. При использовании встроенного динамического SQL необходимо знать характеристики результирующего множества при компиляции, если результаты необходимо обрабатывать в PL/SQL.

Если заранее не известно количество или типы связываемых переменных, с которыми придется работать. Пакет DBMS SQL по ходу выполнения позволяет привязать с помощью процедур входные переменные к операторам. Встроенный динамический SQL требует учета количества и типов связываемых переменных на этапе компиляции (я приведу интересный способ решения этой проблемы).

Когда необходимо выбирать или вставлять тысячи строк и можно использовать обработку массивов. Пакет DBMS SQL поддерживает обработку массивов - возможность выбрать N строк за раз, одним вызовом. Встроенный динамический SQL обычно не позволяет этого сделать, но это ограничение можно обойти, как будет показано далее.

Если в сеансе многократно выполняется один и тот же оператор. Пакет DBMS SQL позволяет один раз разобрать оператор, а затем выполнять его многократно. При использовании встроенного динамического SQL мягкий разбор будет осуществляться при каждом выполнении. В главе 10 было показано, почему такие дополнительные повторные разборы нежелательны.

Встроенный динамический SQL имеет смысл использовать в следующих случаях.

Когда количество и типы столбцов, с которыми придется работать, заранее известны.

Когда заранее известно количество и типы связываемых переменных. (Можно также использовать контексты приложений, чтобы с помощью более простого встроенного динамического SQL выполнять операторы с заранее неизвестным количеством или типами связываемых переменных.)

Когда необходимо выполнять операторы ЯОД.

1208

Глава 16

Если динамически формируемые операторы будут выполняться лишь несколько раз (оптимальный вариант - однократно).

Использование динамического SQL

Я рассмотрю основные шаги при использовании как стандартного пакета DBMS SQL, так и возможностей встроенного динамического SQL.

Пакет DBMS SQL

DBMS SQL - это стандартный встроенный пакет, поставляемый вместе с сервером. Стандартно он устанавливается в схеме пользователя SYS, а привилегия для его выполнения предоставляется роли PUBLIC. Это означает, что не должно быть никаких проблем с доступом к нему или созданием хранимых объектов, ссылающихся на его процедуры, - никаких дополнительных или специальных привилегий для этого предоставлять не надо. Одним из положительных свойств пакета является доступность соответствующей документации. Если при использовании DBMSSQL необходимо вспомнить ту или иную особенность, можно просто выполнить следующий сценарий:

scott@TKYTE816> set pagesize 30

scott@TKYTE816> set pause on

scott@TKYTE816> prompt He забудьте нажать ENTER, чтобы получить результа Не забудьте нажать ENTER, чтобы получить результат

scott@TKYTE816> select text

2 from all source

3 where name = DBMS SQL

4 and type = PACKAGE

5 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. Этот подход сходен с тем, который используется в других языках (например,

Динамический SQL 1209

при программировании на Java с использованием JDBC или на С с использованием библиотеки OCI) В общем случае, процесс, использующий пакет DBMS SQL, будет иметь следующую структуру.

Вызов OPEN CURSOR для получения дескриптора курсора.

Вызов PARSE для анализа оператора. Один и тот же дескриптор курсора можно использовать для обработки нескольких операторов. В каждый момент времени, однако, обрабатывается только один оператор.

Вызов BIND VARIABLE или BIND ARRAY для передачи входных данных оператору.

Если обрабатывается запрос (оператор SELECT), необходимо вызвать процедуру DEFINE COLUMN или DEFINE ARRAY, чтобы указать серверу Oracle, как передавать результаты (как массивы или как скалярные величины и какой тип данных при этом использовать).

Вызов EXECUTE для выполнения оператора.

Если выполняется запрос, необходимо вызвать FETCH ROWS для выборки дан-н1х. Для получения данных по порядковому месту в списке выбора используется вызов COLUMN VALUE.

Если же выполняется блок кода PL/SQL или оператор ЯМД с конструкцией RETURN, можно вызвать процедуру VARIABLE VALUE для получения результатов (параметров типа OUT) из блока по имени.

Вызов CLOSE CURSOR.

В следующем псевдокоде продемонстрирована последовательность шагов для динамического выполнения запроса:

1) Открыть курсор

2) Проанализировать оператор

3) При необходимости получить описание оператора, чтобы выяснить

количество и типы возвращаем1х столбцов

4) Выполнить цикл по i no связываемым переменным (входным)

Связать i-ую входную переменную с оператором

5) Выполнить цикл по i по возвращаемым столбцам

Определить i-ый столбец, сообщив серверу Oracle тип переменной, в которую будут выбираться данные

6) Выполнить оператор

7) Выполнять цикл пока удается выбрать строку

8) Выполнить цикл по i по возвращаемым столбцам

Получить значение i-го столбца строки с помощью column value Конец цикла по строкам

9) Закрыть курсор

Для выполнения PL/SQL-блока или оператора ЯМД используется следующий псевдокод:

1) Открыть курсор

2) Проанализировать оператор

3) Выполнить цикл по i по связываемым переменным (вхо и выходным)