Лабораторная работа № 3

Введение в динамически формируемые SQL-предложения.

Общие сведения

Многие задачи требуют использования динамического SQL в PL/SQL. Вот лишь некоторые из них.

1. Разработка обобщенных процедур, выполняющих стандартные действия вроде выгрузки данных в файлы.

2. Разработка универсальных процедур загрузки данных в неизвестные заранее таблицы. Мы рассмотрим использование динамического SQL для загрузки данных в таблицу.

3. Динамический вызов других PL/SQL-процедур во время выполнения.

4. Генерация условий (например, конструкции WHERE) в процессе работы на основе введенных пользователем данных. Это, пожалуй, основная причина использования динамического SQL большинством разработчиков.

5. Выполнение операторов ЯОД. Поскольку PL/SQL не разрешает включать статические операторы ЯОД в код приложения, остается использовать динамический SQL. Это позволит выполнять операторы, начинающиеся с ключевых слов CREATE, ALTER, GRANT, DROP и т.п.

Решаться перечисленные задачи будут с помощью двух средств языка PL/SQL:

• с использованием стандартного пакета DBMS_SQL. Этот пакет существует уже достаточно давно, он появился в версии 7.1. Пакет обеспечивает процедурный метод выполнения динамического SQL, аналогичный использованию функциональных интерфейсов (таких как JDBC или ODBC);

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

В лабораторной работе необходимо сравнить два метода и сформулировать, когда имеет смысл использовать каждый из них. Как только стало понятно, что необходимо использовать динамический SQL (статический SQL – лучший выбор в большинстве случаев), придется выбирать реализацию на основе пакета DBMS_SQL или встроенного динамического SQL.

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

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

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

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

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

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

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

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

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

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

Основным положением в понимании использования динамических SQL-предложений является то, что если некоторое SQL-предложение является динамически формируемым, то программа должна построить правильное SQL-предложение, выполняя последовательность определенных шагов, формирующих это предложение; выполнить его, а в случае, если это SQL-предложение представляет собой запрос к базе данных, – также определить его колонки и выборку строк возвращаемого набора. Тип выборки и число шагов, требуемое для выполнения динамических SQL-предложений, изменяется в зависимости от типа SQL-предложения, что и вынуждает программу определенным образом определять и выполнять эти предложения. Различные возможности выделили четыре формальных метода формирования динамических SQL-предложений. Таблица 1 кратко излагает некоторые особенности каждого метода. В последней колонке таблицы 1 приводятся последовательности списков вызовов подпрограмм пакета DBMS_SQL, которые программа на PL/SQL должна выполнить, чтобы реализовать динамически формируемые SQL-предложения каждого типа.

Таблица 1.

Метод	Тип предложения	Требуемые вызовы пакета DBMS_SQL
1.	Незапросные (nonqueries), нет базовых переменных (no host variables), выполняется однократно(execute once)	открытие курсора (open cursor), разборка (parse), выполнение (execute),закрытие курсора (close cursor).
2.	Незапросные, известное число базовых переменных (known number host variables), выполняются один или несколько раз (execute one or more times)	открытие курсора, разборка, связывание переменных (bind variables), выполнение, закрытие курсора.
3.	Запросные (queries), известное число операторов SELECT и базовых переменных (known number of SELECT list items and host variables)	открытие курсора, разборка, связывание переменных, определение колонок (define columns), выполнение, выборка строк (fetch rows), получение значений колонок (get column values), обновление (refretch), ... закрытие курсора.
4.	Запросные, неизвестное число операторов SELECT и базовых переменных колонок (unknown number of SELECT list items and column host variables)	открытие курсора, разборка, связывание переменных, определение колонок, выполнение, выборка строк, получение значений колонок, обновление, ... закрытие курсора.