Когда использовать динамический SQL?

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

• Разработка обобщенных процедур, выполняющих стандартные действия вроде
выгрузки данных в файлы.
• Разработка универсальных процедур загрузки данных в не известные заранее таб-
лицы.
• Динамический вызов других PL/SQL-процедур во время выполнения.
• Генерация условий (например, конструкции WHERE) в процессе работы на ос-
нове введенных пользователем данных. Это, пожалуй, основная причина исполь-
зования динамического SQL большинством разработчиков.
• Выполнение операторов ЯОД. Поскольку 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; кроме того, он обеспечивает более высокую производительность.
Учтите, что многие подпрограммы пакета 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 мягкий разбор
будет осуществляться при каждом выполнении. Дополнительные повторные разборы нежелательны.

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

• Когда количество и типы столбцов, с которыми придется работать, заранее изве-
стны.
• Когда заранее известно количество и типы связываемых переменных. (Можно так-
же использовать контексты приложений, чтобы с помощью более простого встро-
енного динамического SQL выполнять операторы с заранее неизвестным коли-
чеством или типами связываемых переменных.)
• Когда необходимо выполнять операторы ЯОД.
• Если динамически формируемые операторы будут выполняться лишь несколько
раз (оптимальный вариант — однократно).