Что такое динамический sql

динамическая производительность SQL в ODBC

хотя статические SQL хорошо работает во многих ситуациях, существует класс приложений, в котором доступ к данным не может быть определен заранее. Например, предположим, что электронная таблица позволяет пользователю ввести запрос, который затем электронная таблица отправляет СУБД для получения данных. Очевидно, что содержимое этого запроса неизвестно программисту при написании программы электронной таблицы.

Динамическое выполнение

чтобы решить эту проблему, электронная таблица использует форму встроенных SQL с именем dynamic SQL. в отличие от статических инструкций SQL, которые жестко запрограммированы в программе, динамические SQL операторы могут быть построены во время выполнения и помещены в переменную узла строки. Затем они отправляются в СУБД для обработки. поскольку субд должна формировать план доступа во время выполнения для динамических SQL инструкций, динамические SQL обычно медленнее, чем статические SQL. при компиляции программы, содержащей динамические инструкции SQL, инструкции dynamic SQL не удаляются из программы, как в статическом SQL. Вместо этого они заменяются вызовом функции, который передает инструкцию СУБД; статические операторы SQL в одной программе обрабатываются обычным образом.

самый простой способ выполнить инструкцию dynamic SQL — с помощью инструкции execute IMMEDIATE. эта инструкция передает инструкцию SQL в субд для компиляции и выполнения.

одной из недостатков инструкции EXECUTE IMMEDIATE является то, что субд должна пройти каждый из пяти шагов обработки оператора SQL при каждом выполнении инструкции. Накладные расходы, связанные с этим процессом, могут быть значительными, если многие инструкции выполняются динамически и непроизводительна, если эти инструкции похожи.

подготовленное выполнение.

для решения описанной выше ситуации динамический SQL предлагает оптимизированную форму выполнения с именем подготовленное выполнение, которая выполняет следующие действия:

программа конструирует инструкцию SQL в буфере точно так же, как это делается для инструкции EXECUTE IMMEDIATE. Вместо переменных-узлов вопросительный знак (?) может быть заменен на константу в любом месте текста инструкции, чтобы указать, что значение константы будет передано позже. Вопросительный знак вызывается как маркер параметра.

программа передает инструкцию SQL субд в инструкцию PREPARE, которая запрашивает, что субд анализирует, проверяет и оптимизирует инструкцию и создает для нее план выполнения. Затем программа использует инструкцию EXECUTE (а не инструкцию EXECUTE IMMEDIATE) для выполнения инструкции PREPARE позже. он передает значения параметров для инструкции через специальную структуру данных, которая называется областью данных SQL или склда.

Программа может многократно использовать инструкцию EXECUTE, указывая разные значения параметров при каждом выполнении динамической инструкции.

Подготовленное выполнение по-прежнему не совпадает со статическим SQL. в статическом SQL первые четыре шага обработки SQL инструкции выполняются во время компиляции. В подготовленном выполнении эти шаги по-прежнему выполняются во время выполнения, но выполняются только один раз. Выполнение плана происходит только при вызове EXECUTE. Такое поведение помогает устранить некоторые недостатки производительности, присущие архитектуре динамических SQL.

Динамический SQL и NDS команды PL/SQL на примерах

команды PL/SQL, EXECUTE IMMEDIATE, OPEN FOR в PL/SQL

Термином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.

Пожалуй, написание динамических команд SQL и динамических программ PL/SQL было самым интересным делом из всего, что я когда-либо делал на языке PL/SQL. Конструирование и динамическое выполнение обеспечивает невероятную гибкость. У разработчика появляется возможность создавать обобщенный код с широким спек­тром применения. Несмотря на это, динамический SQL следует применять лишь там, где это необходимо; решения со статическим SQL всегда являются предпочтительны­ми, потому что динамические решения более сложны, создают больше проблем с от­ладкой и тестированием, обычно медленнее работают и усложняют сопровождение. Что же можно делать с динамическими конструкциями SQL и PL/SQL ? Лишь несколько идей:

  • Выполнение команд DDL. Со статическим кодом SQL в PL/SQL могут выпол­няться только запросы и команды DML. А если вы захотите создать таблицу или удалить индекс? Используйте динамический SQL!
  • Поддержка специализированных запросов и требований к обновлению веб-­приложений. К интернет-приложениям часто предъявляется одно стандартное требование: пользователь должен иметь возможность выбрать столбцы, которые он желает видеть, и изменить порядок просмотра данных (конечно, пользователь может и не понимать, что именно при этом происходит).
  • Оперативное изменение бизнес-правил и формул. Вместо того чтобы жестко фиксировать бизнес-правила в коде, можно разместить соответствующую логику в таблицах. Во время выполнения программа генерирует и выполняет код PL/SQL, необходимый для применения правил.

Начиная с Oracle7, поддержка динамического SQL осуществлялась в виде встроенного пакета DMBS_SQL . В Oracle8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS). NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DBMS_SQL . Впрочем, для некоторых ситуаций

лучше подходит DBMS_SQL . На практике в подавляющем большинстве случаев NDS является более предпочтительным решением.

Команды NDS в PL/SQL

Главным достоинством NDS является его простота. В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка программ и множества правил их исполь­зования, NDS представлен в PL/SQL единственной новой командой EXECUTE IMMEDIATE , которая немедленно выполняет заданную команду SQL, а также расширением существу­ющей команды OPEN FOR , позволяющей выполнять многострочные динамические запросы.

Команды EXECUTE IMMEDIATE и OPEN FOR не будут напрямую доступны в Oracle Forms Builder и Oracle Reports Builder до тех пор, пока версия PL/SQL этих инструментов не будет обновлена до Oracle8i и выше. Для более ранних версий придется создавать хранимые программы, скрывающие вызовы этих конструкций; эти хранимые программы могут выполняться в клиентском коде PL/SQL.

Команда EXECUTE IMMEDIATE

Команда EXECUTE IMMEDIATE используется для немедленного выполнения заданной команды SQL. Она имеет следующий синтаксис:

Здесь строка_SQL — строковое выражение, содержащее команду SQL или блок PL/SQL; переменная — переменная, которой присваивается содержимое поля, возвращаемого за­просом; запись — запись, основанная на пользовательском типе или типе %ROWTYPE , при­нимающая всю возвращаемую запросом строку; аргумент — либо выражение, значение которого передается команде SQL или блоку PL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL. Секция INTO используется для однострочных запросов. Для каждого значения столбца, возвращаемого запросом, необходимо указать переменную или поле записи совместимого типа. Если INTO предшествует конструкция BULK COLLECT , появляется воз­можность выборки множественных строк в одну или несколько коллекций. Секция USING предназначена для передачи аргументов строке SQL. Она используется с динамическим SQL и PL/SQL, что и позволяет задать режим параметра. Этот режим актуален только для PL/SQL и секции RETURNING . По умолчанию для параметров используется режим IN (для команд SQL допустима только эта разновидность аргументов).

Команда execute immediate может использоваться для выполнения любой команды SQL или блока PL/SQL. Строка может содержать формальные параметры, но они не могут связываться с именами объектов схемы (например, именами таблиц или столбцов).

При выполнении команды DDL в программе также происходит закрепление опе­рации. Если вы не хотите, чтобы закрепление, обусловленное DDL, отражалось на текущих изменениях в других частях приложения, поместите динамическую команду DDL в процедуру автономной транзакции. Пример такого рода приведен в файле auton_ddl.sql.

При выполнении команды исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия — например, :salary_value )

фактическими значениями параметров подстановки в секции USING . Не допускается передача литерала NULL — вместо него следует указывать выражение соответствующего типа, результат вычисления которого может быть равен NULL .

NDS поддерживает все типы данных SQL. Переменные и параметры команды могут быть коллекциями, большими объектами (LOB), экземплярами объектных типов, до­кументами XML и т. д. Однако NDS не поддерживает типы данных, специфические для PL/SQL, такие как BOOLEAN , ассоциативные массивы и пользовательские типы записей. С другой стороны, секция INTO может содержать запись PL/SQL, количество и типы полей которой соответствуют значениям, выбранным динамическим запросом. Рассмотрим несколько примеров.

  • О Создание индекса:

Проще не бывает, верно?

  • О Создание хранимой процедуры, выполняющей любую команду DDL:

При наличии процедуры exec_ddl тот же индекс может быть создан следующим образом:

Получение количества строк в произвольной таблице для заданного предложения WHERE :

Таким образом, нам больше не понадобится писать команду SELECT COUNT (*) ни в SQI*Plus , ни в программах PL/SQL . Она заменяется следующим блоком кода:

Изменение числового значения в любом столбце таблицы employees:

Безусловно, для такой гибкости объем кода получился совсем небольшим! В этом примере показано, как используется подстановка: после разбора команды UPDATE ядро PL/SQL заменяет в ней формальные параметры (:the_value, :lo и :hi) значениями переменных. Также обратите внимание, что в этом случае атрибут курсора SQL%ROWCOUNT используется точно так же, как при выполнении статических команд DML. Выполнение разных блоков кода в одно время в разные дни. Имя каждой программы строится по схеме ДЕНЬ_set_sd^edule . Все процедуры получают четыре аргумента: при вызове передается код работника employee_id и час первой встречи, а процедура возвращает имя работника и количество встреч в заданный день. Задача решается с ис­пользованием динамического PL/SQL:

  • Привязка значения BOOLEAN , специфического для PL/SQL , командой EXECUTE IMMEDIATE (новая возможность 12c):

Как видите, команда EXECUTE IMMEDIATE позволяет исключительно легко выполнять динамические команды SQL и блоки PL/SQL с удобным синтаксисом.

Команда OPEN FOR

Команда OPEN FOR изначально не была включена в PL/SQL для NDS ; она появилась в Oracle7 и предназначалась для работы с курсорными переменными. Затем ее синтаксис был расширен для реализации многострочных динамических запросов. При использо­вании пакета DBMS_SQL реализация многострочных запросов получается очень сложной: приходится производить разбор и подстановку, отдельно определять каждый столбец, выполнять команду, выбирать сначала строки, а затем — последовательно значения каждого столбца. Код получается весьма громоздким.

Для динамического SQL разработчики Oracle сохранили существующий синтаксис OPEN, но расширили его вполне естественным образом:

Здесь курсорная переменная — слаботипизированная курсорная переменная; хост_ переменная — курсорная переменная, объявленная в хост-среде PL/SQL, например в программе OCI (Oracle Call Interface); cmpoка SQL — команда SELECT , подлежащая динамическому выполнению.

Курсорные переменные рассматривались в этом блоге. Здесь мы подробно расскажем об их использовании с NDS .

В следующем примере объявляется тип REF CURSOR и основанная на нем переменная- курсор, а затем с помощью команды OPEN FOR открывается динамический запрос:

После того как запрос будет открыт командой OPEN FOR , синтаксис выборки записи, за­крытия курсорной переменной и проверки атрибутов курсора ничем не отличается от синтаксиса статических курсорных переменных и явных курсоров.

Давайте поближе познакомимся с командой OPEN FOR . При выполнении OPEN FOR ядро PL/SQL :

  1. связывает курсорную переменную с командой SQL, заданной в строке запроса;
  2. вычисляет значения параметров и заменяет ими формальные параметры в строке запроса;
  3. выполняет запрос;
  4. идентифицирует результирующий набор;
  5. устанавливает курсор на первую строку результирующего набора;
  6. обнуляет счетчик обработанных строк, возвращаемый атрибутом %rowcount . Обратите внимание: параметры подстановки, заданные в секции USING , вычисляются только при открытии курсора. Это означает, что для передачи тому же динамическому запросу другого набора параметров нужно выполнить новую команду OPEN FOR .

Для выполнения многострочного запроса (то есть запроса, возвращающего набор строк) необходимо:

  1. объявить тип ref cursor (или использовать встроенный тип sys_refcursor );
  2. объявить на его основе курсорную переменную;
  3. открыть курсорную переменную командой OPEN FOR ;
  4. с помощью команды fetch по одной извлечь записи результирующего набора;
  5. при необходимости проверить значения атрибутов ( %found, %notfound, %rowcount, %isopen) ;
  6. закрыть курсорную переменную обычной командой Как правило, после завершения работы с курсорной переменной следует явно закрыть ее.

Следующая простая программа выводит значения поля заданной таблицы в строках, отбираемых с помощью секции WHERE (столбец может содержать числа, даты или строки, файл showcol.sp):

Примерный результат выполнения этой процедуры выглядит так:

Столбцы даже можно комбинировать:

Выборка в переменные или записи

Команда FETCH в процедуре showcol из предыдущего раздела осуществляет выборку в отдельную переменную. Также возможна выборка в серию переменных:

Работа с длинным списком переменных в списке FETCH может быть громоздкой и не­достаточно гибкой; вы должны объявить переменные, поддерживать синхронизацию этого набора значений в команде FETCH и т. д. Чтобы упростить жизнь разработчика, NDS позволяет осуществить выборку в запись, как показано в следующем примере:

Конечно, во многих ситуациях выполнение команды SELECT * нежелательно; если ваша таблица содержит сотни столбцов, из которых вам нужны два-три, эта команда крайне неэффективна. Лучше создать тип записи, соответствующий разным требованиям. Эти структуры лучше всего разместить в спецификации пакета, чтобы их можно было ис­пользовать во всем приложении. Вот один из таких пакетов:

С таким пакетом приведенный выше код можно переписать следующим образом:

Секция USING в OPEN FOR

Как и в случае с командой EXECUTE IMMEDIATE , при открытии курсора можно передать ар­гументы. Для запроса можно передать только аргументы IN. Аргументы также повышают эффективность SQL, упрощая написание и сопровождение кода. Кроме того, они могут радикально сократить количество разобранных команд, хранящихся в общей памяти SGA, а это повышает вероятность того, что уже разобранная команда будет находиться в SGA в следующий раз, когда она вам потребуется.

Вернемся к процедуре showcol. Эта процедура получает полностью обобщенную секцию WHERE . Допустим, действуют более специализированные требования: я хочу вывести (или иным образом обработать) всю информацию столбцов для строк, содержащих столбец даты со значением из некоторого диапазона. Другими словами, требуется обеспечить поддержку запроса:

а также запроса:

Также нужно проследить за тем, чтобы компонент времени столбца даты не учитывался в условии WHERE .

Заголовок процедуры выглядит так:

Теперь команда OPEN FOR содержит два формальных параметра и соответствующую секцию USING :

Команда построена таким образом, что при отсутствии конечной даты секция WHERE возвращает строки, у которых значение в столбце даты совпадает с заданным значением dt1 . Остальной код процедуры showcol остается неизменным, не считая косметических изменений в выводе заголовка.

Следующий вызов новой версии showcol запрашивает имена всех работников, принятых на работу в 1982 году:

О четырех категориях динамического SQL

Итак, мы рассмотрели две основные команды, используемые для реализации динами­ческого SQL в PL/SQL . Теперь пришло время сделать шаг назад и рассмотреть четыре разновидности (категории) динамического SQL , а также команды NDS , необходимые для реализации этих категорий. Категории и соответствующие команды NDS пере­числены в табл. 1.

Таблица 1. Четыре категории динамического SQL

тип описание Команды NDS
Категория 1 Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE без параметров EXECUTE IMMEDIATE без секций USING и INTO
Категория 2 Без запросов; только команды DDL и команды
UPDATE, INSERT, MERGE и DELETE с фиксированным
количеством параметров
EXECUTE IMMEDIATE с секцией USING
Категория 3
(одна строка)
Запросы (SELECT) с фиксированным количеством
столбцов и параметров, с выборкой одной строки
данных
EXECUTE IMMEDIATE с секциями
USING и INTO
Категория 3
(несколько строк)
Запросы (SELECT) с фиксированным количеством
столбцов и параметров, с выборкой одной или
нескольких строк данных
EXECUTE IMMEDIATE с секциями
USING и BULK COLLECT INTO
или OPEN FOR с динамической строкой
Категория 4 Команда, в которой количество выбранных столб-
цов (для запроса) или количество параметров неизвестно до стадии выполнения
Для категории 4 необходим пакет DBMS_SQL

Категория 1

Следующая команда DDL является примером динамического SQL категории 1:

Команда UPDATE также относится к динамическому SQL категории 1, потому что един­ственным изменяемым аспектом является имя таблицы — параметры отсутствуют:

Категория 2

Если заменить оба жестко фиксированных значения в предыдущей команде DML фор­мальными параметрами (двоеточие, за которым следует идентификатор), появляется динамический SQL категории 2:

Секция USING содержит значения, которые будут подставлены в строку SQL после раз­бора и перед выполнением.

Категория 3

Команда динамического SQL категории 3 представляет собой запрос с фиксированным количеством параметров (или вообще без них). Вероятно, чаще всего вы будете создавать команды динамического SQL именно этого типа. Пример:

Здесь я запрашиваю всего два столбца из таблицы employees и сохраняю их значения в двух локальных переменных из секции INTO . Также используется один параметр. Так как значения этих компонентов являются статическими на стадии компиляции, я ис­пользую динамический SQL категории 3.

Категория 4

Наконец, рассмотрим самый сложный случай: динамический SQL категории 4. Возьмем предельно обобщенный запрос:

На момент компиляции кода я понятия не имею, сколько столбцов будет запраши­ваться из таблицы employees . Возникает проблема: как написать команду FETCH INTO , которая будет обеспечивать подобную изменчивость? Есть два варианта: либо вернуться к DBMS_SQL для написания относительно тривиального (хотя и объемистого) кода, либо переключиться на исполнение динамических блоков PL/SQL .

К счастью, ситуации, требующие применения категории 4, встречаются редко.

Выполнение динамических T-SQL инструкций в Microsoft SQL Server

В данном материале мы поговорим о выполнении динамического T-SQL кода, Вы узнаете, как сформировать текстовую строку, содержащую SQL инструкцию, и запустить ее на выполнение в Microsoft SQL Server.

Динамический код в Microsoft SQL Server

Динамический код в Microsoft SQL Server

Microsoft SQL Server позволяет выполнять SQL инструкции, сформированные динамически, так как иногда без этого просто не обойтись. Например, для того чтобы динамически выполнять инструкции, которые с первого взгляда кажутся статическими. К примеру, оператор PIVOT, его синтаксис, предполагает вручную перечислять выходные столбцы, количество и название которых заранее нам могут быть просто не известны. Но мы можем сформировать динамическую инструкцию, которая будет автоматически узнавать и подставлять все необходимые нам значения в SQL запрос, и тем самым нам уже не нужно знать и тем более вручную указывать выходные столбцы, в случае с оператором PIVOT, кстати, ранее я уже приводил пример реализации динамического PIVOT.

Динамическая SQL инструкция – это просто текстовая строка, которая после преобразования и подставки всех значений, исполняется SQL сервером как обычная SQL инструкция.

Таким образом, чтобы сформировать динамическую SQL инструкцию, необходимо просто сформировать текстовую строку с указанием необходимых переменных, значения которых Вы хотите подставлять, или произвести конкатенацию строк с переменными, используя оператор + (плюс).

В Microsoft SQL Server существует два способа запускать на выполнения строки, содержащие SQL инструкции, это: команда EXECUTE и системная хранимая процедура sp_executesql.

Исходные данные для примеров

Перед тем как переходить к рассмотрению примеров выполнения динамических инструкций, давайте создадим тестовые данные, например, таблицу TestTable, и добавим в нее несколько строк.

Скриншот 1

Команда EXECUTE в T-SQL

EXECUTE (сокращенно EXEC) – команда для запуска хранимых процедур и SQL инструкций в виде текстовых строк.

Перед тем как переходить к примерам, следует отметить, что использование динамического кода с использованием команды EXEC – это не безопасно! Дело в том, что для того чтобы сформировать динамическую SQL инструкцию, необходимо использовать переменные для динамически изменяющихся значений. Так вот, если эти значения будут приходить от клиентского приложения, т.е. от пользователя, злоумышленники могут передать и, соответственно, внедрить в нашу инструкцию вредоносный код в виде текста, а мы его просто исполним в БД, думая, что нам передали обычные параметры. Поэтому все такие значения следует очень хорошо проверять, перед тем как подставлять в инструкцию.

Пример использования EXEC в T-SQL

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

Текст запроса будет храниться в переменной @SQL_QUERY, в переменной @Var1 будет храниться значение, которое мы будем подставлять в наш запрос, для того чтобы этот запрос стал динамическим (в нашем случае мы вручную присвоим статическое значение в переменную, хотя это значение можно узнавать, например, с помощью запроса или каких-то вычислений).

Для формирования строки мы будет использовать конкатенацию строк, а именно оператор + (плюс), только стоит понимать, что в этом случае выражения, участвующие в операции, должны иметь текстовый тип данных. Переменная @Var1 у нас будет иметь тип данных INT, поэтому, чтобы соединить ее со строкой, мы предварительно преобразуем ее значение к типу данных VARCHAR.

Для наглядности того, какой именно SQL запрос у нас получился, мы просто посмотрим, что у нас хранится в переменной @SQL_QUERY инструкцией SELECT.

Скриншот 2

Хранимая процедура sp_executesql в T-SQL

sp_executesql – это системная хранимая процедура Microsoft SQL Server, которая выполняет SQL инструкции. Эти инструкции могут содержать параметры, тем самым делая их динамическими.

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

Все параметры процедуры sp_executesql необходимо передавать в формате Unicode (тип данных строк должен быть NVARCHAR).

Пример использования sp_executesql в T-SQL

В этом примере итоговый результат у нас будет точно таким же, как и в примере с EXEC, только динамические значения, у нас это переменная @Var1, мы объявим и передадим в виде параметров хранимой процедуры sp_executesql.

Скриншот 3

У меня на этом все, надеюсь, материал был Вам интересен и полезен, если Вас интересуют другие возможности языка T-SQL, то рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих, пока!

Добавить комментарий

Ваш адрес email не будет опубликован.