5.7.1. Динамический sql

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

комбинации этих сортировок - по доходу и числу прогулов, по фамилии и стажу в обратном порядке и т.д. Простейший подсчет показывает - вариантов раздела ORDER BY оператора SELECT будет 81 (всего 4 поля, каждое поле допускает три разных способа использования при сотрировке - сортировать в порядке возрастания, убывания, вообще не сортировать - три в четвертой степени и есть 81). Согласитесь, что весьма утомительно указывать все 81 варианта оператора SELECT внутри программы.

В системах разработки приложений (ESQL/C, NewEra, 4GL и т.д.) имеется возможность формировать и исполнять SQL-запроса в процессе работы прикладной программы, в динамике ее исполнения. Отсюда и название - "динамический SQL" (впрочем, автор не считает это название очень удачным). Фактически, это тот же самый язык - SQL, но SQL-оператор не подвергаетсясинтаксическому разбору на этапе компиляции прикладной программы, а в текстовом виде передается непосредственно серверу базу данных.

Имеются следующие операторы для работы с динамическим SQL:

PREPARE <имя оператора> FROM <текстовая строка>

EXECUTE <имя оператора>

FREE <имя оператора>

Оператор PREPARE "подготавливает" SQL-оператор для исполнения. Сам SQL-оператор указывается либо явно в виде текстовой строки, либо через значение текстовой переменной. Оператор PREPARE "связывает" с SQL-оператором имя. Это имя - самое обычное имя в среде разработки. Примеры (Informix-4GL):

PREPARE sel1 FROM "select name from items where price < 1.50"

PREPARE empty_comp FROM "INSERT INTO companies(name) VALUES ('undef')"

Оператор PREPARE посылает переданный ему текст SQL-серверу. Сервер анализирует переданные ему SQL-операторы и, если нет ошибок, переводит их во внутреннее представление.

После того, как оператор подготовлен, он может быть исполнен оператором EXECUTE. Здесь-то и надо указывать имя, данное подготовленному оператору:

EXECUTE sel1 EXECUTE empty_comp

Один и тот же подготовленный оператор можно исполнять многократно. Оператор FREE освобождает все ресурсы (память), связанные с подготовленным оператором. Выполнять оператор FREE следует тогда, когда подговленный оператор заведомо больше не потребуется:

FREE sel1

Если не выполнить оператор FREE ничего страшного не произойдет, но выделенная для оператора память будет висеть мертвым грузом.

Если на момент подготовки SQL-оператора не все конкретные значения известны, то имеется возможность подставлять эти значения в момент исполнения. Для этого SQL-оператор, подготовленный с помощью PREPARE, должен быть снабжен параметрами. Параметры, значения которых будут определяться в момент исполнения, задаются символом "?":

PREPARE select2 FROM "SELECT price FROM items WHERE name = ?" PREPARE new_comp FROM "INSERT INTO companies(name, address) VALUES (?,?)"

Для задания фактических параметров в оператор EXECUTE надо добавить раздел USING:

EXECUTE new_comp USING "Кооператив 'Эх, ухнем'", "Москва, Арбат, 21" EXECUTE new_comp USING "ИЧП 'Бумеранг'", "Магадан, п/я 777"

Параметры в операторах PREPARE/EXECUTE являются позиционными. То есть при исполнении на место первого вопросительного знака подставляется первое значение в разделе USING, на место второго вопросительного знака - второе значение из раздела USING и т.д.

Подготовленный оператор может использоваться при описании курсора. Например, если в зависимости от желания пользователя нам надо выполнить сортировку товара либо по названию, либо по цене, то это на Informix-4GL реализуется следующим образом:

DEFINE string1 CHAR(60) .......... IF flag THEN { сортируем по названию } LET string1 = "SELECT name, price FROM items ORDER BY name" ELSE { сортируем по цене } LET string1 = "SELECT name, price FROM items ORDER BY price" END IF PREPARE select_st FROM string1 DECLARE my_cursor CURSOR FOR select_st ..........

Очевидно, можно еще сократить приведенный выше фрагмент, если использовать оператор конкатенации строк.

Помимо возможности формировать запросы не на этапе написания программы, а на этапе ее иполнения, операторы PREPARE/EXECUTE/FREE могут быть полезны еще, как минимум, в двух случаях: для повышения эффективности программы и для исполнения SQL-операторов, которые понятны серверу, но которых нет в системе разработки.

Для того, чтобы понять за счет чего подготовленные операторы могут повысить эффективность, надо разобраться как отрабатываются SQL-запросы. Когда в работе приложения управление передается на SQL-оператор, то происходит следующее. Этот SQL-оператор посылает серверу запрос на исполнение

(напомним, что все серьезные реляционные СУБД выполнены по схеме клиент-сервер). SQL-сервер исполняет запрос в четыре этапа: (1) анализирует пришедший запрос, (2) выбирает оптимальный способ его исполнения, (3) исполняет и (4) отсылает результаты приложению. Если выполнять несколько одинаковых запросов, то для каждого запроса будeт делаться все четыре этапа. Если же мы подготавливаем запрос с помощью оператора PREPARE, а затем несколько раз исполняем его оператором EXECUTE, то анализ запроса и поиск оптимального способа исполнения будет делаться только один раз - в момент выполнения оператора PREPARE. А на каждое исполнение запроса оператором EXECUTE требуется только два последних этапа - непосредственное исполнение и отсылка результатов.

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

Например, Вы имеете среду разработки Informix-4GL старой версии (предположим, 4-й). Она вас вполне устраивает. Но в качестве SQL-сервера используется 7-я версия Informix DS Dynamic Scalable Architecture. Этот сервер "понимает" уже значительно более широкий набор SQL-операторов по сравнению с 4-й версией Informix-4GL. В частности, оператор создания триггеров CREATE TRIGGER (о том, что это такое, будет сказано ниже) может быть исполнен сервером, но отсутствует в продукте Informix-4GL версии 4.10. Используя операторы PREPARE/EXECUTE триггер можно создать:

PREPARE cr_trig FROM "CREATE TRIGGER trig1 ..." EXECUTE cr_trig FREE cr_trig

Подготовленные с помощью оператора PREPARE SQL-запросы доступны (видимы) только в данном приложении, а именно между операторами DATABASE ... CLOSE DATABASE. То есть, если вы завершили работу (а точнее, закрыли базу данных оператором CLOSE DATABASE), то подготовленные запросы пропадают. Или, если вы подготовили SQL-запрос, то другой пользователь за другим компьютером не может выполнить подготовленный вами запрос (этот другой пользователь, конечно, может выполнить ту же самую последовательность PREPARE/EXECUTE/FREE, но это будет уже другой SQL-запрос). Однако, существует возможность подготовить для исполнения SQL-запросы так, что эти запросы будут доступны многим пользователям. Но для этого используется уже другой механизм - хранимые процедуры.