 |
Создание операторов динамического SQL
|
|
|
|
Операторы динамического SQL в отличие от операторов встроенного SQL формируются не на этапе компиляции, а на этапе выполнения приложения. Динамический SQL может применяться совместно с ODBC API или в рамках SQL/CLI, представляющего собой расширенный уровень соответствия стандарта SQL-99.
Поддержка динамического SQL на начальном уровне соответствия стандарту SQL-92 не требуется.
Операторы динамического SQL формируются как текстовые переменные.
Например:
Stmt1:='SELECT * FROM tbl1';Для динамического формирования оператора можно выполнять последовательное объединение строк.
Операторы динамического SQL можно использовать:
- однократно, производя за один шаг компиляцию и выполнение оператора. Будем называть такое применение одношаговым интерфейсом;
- многократно, разделяя процесс компиляции оператора, на котором строится план выполнения, и процесс непосредственного выполнения оператора. Будем называть такое применение многошаговым интерфейсом.
Одношаговый интерфейс
одношаговый интерфейс реализуется SQL-оператором EXECUTE IMMEDIATE, который имеет в стандарте SQL-92 следующее формальное описание:
EXECUTE IMMEDIATE:variable;На оператор, указываемый переменной (variable), накладываются следующие ограничения:
- оператор не может использовать INTO-переменные;
- оператор не может использовать переменные связи.
Следующий пример иллюстрирует применение динамического SQL с одношаговым интерфейсом:
stmt_str:= 'INSERT INTO ' || table_name || ' values (:f1,:f2,:f3)';EXEC SQL EXECUTE IMMEDIATE:stmt_str;Многошаговый интерфейс
Оператор EXECUTE IMMEDIATE удобен для одноразового выполнения, но при необходимости неоднократного выполнения, например в цикле, одного и того же оператора, но с различными параметрами, более эффективно использовать многошаговый интерфейс, реализуемый операторами PREPARE и EXECUTE.
|
|
|
При выполнении оператора PREPARE, указываемый им SQL-оператор передается в СУБД. Далее выполняется синтаксический разбор оператора и строится план выполнения. После этого при каждом выполнении оператора EXECUTE используется уже "откомпилированный" SQL-оператор, что значительно повышает производительность. Дополнительно при выполнении оператора EXECUTE на сервер передаются значения переменных связи (если они есть), используемые, в частности, для вычисления предиката фразы WHERE.
Оператор PREPARE имеет в стандарте SQL-92 следующее формальное описание:
PREPARE [ GLOBAL | LOCAL ] operator_sql FROM string_variable;Параметр operator_sql определяет идентификатор SQL-оператора, указываемый далее для выполнения в операторе EXECUTE или для включения в курсор в операторах ALLOCATE CURSOR или DECLARE CURSOR.
Параметр string_variable указывает строку, содержащую динамически сформированный текст SQL-оператора.
Например:
stmt_str:= 'INSERT INTO ' || table_name || ' values (:f1,:f2,:f3)';EXEC SQL PREPARE GLOBAL stmt1 FROM:stmt_str;Фразы GLOBAL и LOCAL определяют область видимости оператора: GLOBAL указывает, что оператор с данным идентификатором доступен всем процессам данного сеанса работы с СУБД, а LOCAL ограничивает доступ рамками данного выполняемого модуля (значение по умолчанию).
Если, создаются два одноименных оператора, но один как GLOBAL, а другой как LOCAL, то СУБД создает два отдельных плана выполнения как для разных операторов. В противном случае при компиляции оператора с уже существующим именем просто строится новый план выполнения оператора.
Для освобождения подготовленного SQL-оператора используется оператор DEALLOCATE PREPARE, который освобождает все ресурсы, занимаемые подготовленным SQL-оператором.
Например:
EXEC SQL DEALLOCATE PREPARE GLOBAL stmt1;Для выполнения откомпилированного SQL-оператора используется оператор EXECUTE, который в стандарте SQL-92 имеет следующее формальное описание:
|
|
|
Фраза INTO указывается в том случае, если выполняемый SQL-оператор представляет собой запрос, возвращающий одну строку.
Динамические параметры
Значения динамических параметров передаются на сервер каждый раз при выполнении откомпилированного SQL-оператора. Динамическими параметрами могут быть как переменные связи, так и INTO-переменные.
динамические параметры можно использовать как во встроенном SQL, так и в динамическом SQL.
динамические параметры задаются в тексте SQL-оператора символами знак вопроса (?). Стандарт не определяет максимально допустимое число динамических параметров. Как правило, СУБД могут иметь ограничения только на размер вводимого SQL-оператора.
Например:
stmt_str:='INSERT INTO tbl1 VALUES (?,?,?)';EXEC SQL PREPARE stmt2 FROM:stmt_str;При выполнении данного откомпилированного оператора вместо динамических параметров значения будут подставляться в порядке, указанном в SQL-операторе EXECUTE или в области SQL-дескриптора.
Список значений для динамических параметров может быть указан:
- фразой USING оператора EXECUTE - для динамических параметров, не указываемых фразой INTO откомпилированного оператора;
- фразой INTO оператора EXECUTE - для динамических параметров, указанных во фразе INTO откомпилированного оператора.
Например:
stmt_str1:='INSERT INTO tbl1 (f1,f2,f3) VALUES (?,?,?)';EXEC SQL PREPARE stmt2 FROM:stmt_str1;EXEC SQL EXECUTE stmt2 USING:f1,:f2,:f3;Значение переменных f1, f2 и f3 основного языка программирования будут переданы на сервер для выполнения откомпилированного оператора с идентификатором stmt2.
Возможен вариант, когда откомпилированный оператор содержит динамические параметры и во фразе INTO оператора SELECT, и в предикате.
Например:
stmt_str2:='SELECT f1, f2, f3 FROM tbl1 INTO?,?,? WHERE f2=?';EXEC SQL PREPARE stmt3 FROM:stmt_str2;EXEC SQL EXECUTE stmt3 INTO:f1,:f2,:f3 USING:f4;Переменные f1, f2 и f3 основного языка программирования будут использованы как INTO-переменные, а значение переменной f4 будет передано на сервер для выполнения откомпилированного оператора с идентификатором stmt3.
SQL-дескрипторы
SQL-дескриптор - это область, которая временно создается СУБД для хранения информации о параметрах откомпилированного SQL-оператора.
SQL-дескриптор используется для описания параметров как во фразе USING, так и во фразе INTO оператора EXECUTE.
|
|
|
Для каждого откомпилированного SQL-оператора создается своя область SQL-дескриптора.
SQL-дескрипторы могут быть использованы для следующих целей:
- определения параметров при выполнении SQL-оператора;
- получения информации об INTO-переменных
- применения в динамических курсорах.
SQL-дескриптор состоит из элементов. Каждый динамический параметр описывается одним элементом, который представляет собой структуру, состоящую из списка полей, причем некоторые из них могут отсутствовать или не использоваться (в зависимости от типа элемента и от реализации). Приведем описание наиболее существенных полей элемента SQL-дескриптора:
| Имя поля | Описание |
| TYPE | Целочисленное значение, определяющее тип параметра. |
| Это поле может принимать следующие значения: | |
| CHARACTER - 1 | |
| DECIMAL - 3 | |
| INTEGER - 4 | |
| SMALLINT - 5 | |
| FLOAT - 6 | |
| REAL - 7 | |
| DOUBLE PRECISION - 8 | |
| DATE - 9 | |
| TIME - 9 | |
| TIMESTAMP - 9 | |
| INTERVAL - 10 | |
| CHARACTER VARYING - 12 | |
| BIT - 14 | |
| BIT VARYING - 15 | |
| В зависимости от значения поля TYPE могут использоваться поля PRECISION и SCALE | |
| NAME | Идентификатор параметра, соответствующего данному элементу. Это значение может отсутствовать, например, для вычислимых столбцов. В этом случае используется поле UNNAMED |
| UNNAMED | Если идентификатор параметра в поле NAME не указан, то значение данного поля будет 1; если идентификатор параметра присутствует, то 0 |
| DATA | Содержит значение параметра |
| NULLABLE | Определяет, может ли параметр принимать значение NULL: 0 - может, 1 - не может |
| INDICATOR | Используется аналогично индикаторной переменной |
Работа с SQL-дескриптором состоит из следующих этапов:
- Создание SQL-дескриптора оператором ALLOCATE DESCRIPTOR;
- Задание значений для SQL-дескриптора может выполняться:
- при выполнении оператора SET DESCRIPTOR;
- при выполнении оператора DESCRIBE для автоматической установки значений;
- Использование значений динамических параметров:
- извлечение значений параметров при выполнении оператора GET DESCRIPTOR;
- использование значений параметров при выполнении оператора EXECUTE;
- Освобождение SQL-дескриптора оператором DEALLOCATE DESCRIPTOR.
Рассмотрим более подробно все этапы работы с SQL-дескриптором.
|
|
|
Создание SQL-дескриптора выполняется оператором ALLOCATE DESCRIPTOR, который в стандарте SQL-92 имеет следующее формальное описание:
ALLOCATE DESCRIPTOR descriptor_name [ WITH MAX count_of_inctance ];Этот оператор создает область SQL-дескриптора для хранения информации о динамических параметрах.
Фраза WITH MAX позволяет установить максимально допустимое число элементов SQL-дескриптора.
Например:
EXEC SQL ALLOCATE DESCRIPTOR descr1 WITH MAX 4;Этот оператор создает SQL-дескриптор descr1, который может применяться для откомпилированного оператора, использующего не более четырех динамических параметров.
Инициализация SQL-дескриптора выполняется одним из следующих SQL-операторов:
- DESCRIBE;
- SET DESCRIPTOR.
Начальную инициализацию возможно выполнить посредством оператора DESCRIBE, что значительно проще, а затем изменить требуемые поля с помощью оператора SET DESCRIPTOR.
Оператор DESCRIBE имеет в стандарте SQL-92 следующее формальное описание:
DESCRIBE [ INPUT | OUTPUT ] operator_sql USING SQL DESCRIPTOR descriptor_name;Этот оператор сохраняет в предварительно созданном SQL-дескрипторе информацию о динамических параметрах откомпилированного SQL-оператора.
Фраза INPUT указывает, что инициируется SQL-дескриптором для динамических переменных связи (входные параметры для сервера).
Фраза OUTPUT указывает, что инициируется SQL-дескриптором для динамических INTO-переменных (выходные параметры от сервера). По умолчанию используется опция OUTPUT.
При выполнении оператора DESCRIBE для каждого элемента устанавливаются значения полей TYPE, NAME, UNNAMED и NULLABLE, а также полей, более точно специфицирующих конкретный тип данных (LENGTH, PRECISION и т.п.).
Например:
stmt1:='SELECT f1, f2 FROM tbl1 INTO?,? WHERE f2= 1';EXEC SQL ALLOCATE DESCRIPTOR descr1 WITH MAX 2;EXEC SQL DESCRIBE OUTPUT:stmt1 USING SQL DESCRIPTOR descr1;В результате таких действий при инициализации SQL-дескриптора descr1 будет создано два элемента со значениями полей NAME f1 и f2.
Хотя SQL-дескриптор для динамических переменных связи также можно инициировать оператором DESCRIBE, но сами значения этих переменных, хранимые в полях DATA, оператором DESCRIBE установить нельзя. Для этой цели можно использовать оператор SET DESCRIPTOR, который имеет в стандарте SQL-92 следующее формальное описание:
SET DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name { COUNT = integer_value } | { VALUE number_of_element field_of_element = value}.,…};Этот оператор может выполнять одно из следующих действий:
- изменять количество элементов, описываемых SQL-дескриптором, для чего используется фраза COUNT;
- изменять значение полей конкретного элемента, указываемого его номером (number_of_element).
Значения полей NAME, RETURNED_LENGHT, NULLABLE, RETURNED_OCTET_LENGHT, COLLATION_CATALOG, COLLATION_SCHEMA и COLLATION_NAME нельзя изменить с помощью оператора SET DESCRIPTOR.
|
|
|
Например:
stmt1:='SELECT f1, f2, f3 FROM tbl1 INTO?,?,? WHERE f2= 1';EXEC SQL ALLOCATE DESCRIPTOR descr1 WITH MAX 2;EXEC SQL DESCRIBE OUTPUT:stmt1 USING SQL DESCRIPTOR descr1;EXEC SQL SET DESCRIPTOR descr1 VALUE 3 TYPE=1, LENGTH=15;Таким образом, третий динамический параметр будет иметь тип CHARACTER и длину 15 символов.
Если при создании SQL-дескриптора для откомпилированного оператора не используется оператор DESCRIBE, то перед установкой значений полей каждого элемента следует задать количество элементов дескриптора (фраза COUNT). Иногда для этого может потребоваться выполнение синтаксического разбора динамически сформированного SQL-оператора, что можно избежать, применяя оператор DESCRIBE.
Значения любых полей элементов SQL-дескриптора выполняются оператором GET DESCRIPTOR, который имеет в стандарте SQL-92 следующее формальное описание:
GET DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name { integer_variable= COUNT } | { VALUE number_of_element variable = field_ of_element }.,…};Например:
EXEC SQL SET DESCRIPTOR descr1 VALUE 3 TYPE=1, LENGTH=15;EXEC SQL GET DESCRIPTOR descr1 VALUE 3:var_type = TYPE,:var_lenght = LENGTH;При выполнении такого SQL-оператора в переменную var_type будет занесено значение 1, а в переменную var_lenght - значение 15.
Значения полей DATA SQL-дескриптора можно получить только после выполнения оператора EXECUTE.
Например:
str1:='SELECT f3 FROM tbl1 INTO? WHERE f2 = 1';EXEC SQL PREPARE stmt1 FROM:str1;EXEC SQL ALLOCATE DESCRIPTOR descr1 WITH MAX 1;EXEC SQL DESCRIBE OUTPUT stmt1 USING SQL DESCRIPTOR descr1;EXEC SQL EXECUTE stmt1 INTO SQL DESCRIPTOR descr1;GET DESCRIPTOR descr1 VALUE 1:f1=DATA:fnull=NULLABLE;Динамические курсоры
В динамическом SQL можно использовать не только курсоры встроенного SQL (создаваемые статически оператором DECLARE CURSOR), но и два дополнительных типа курсоров:
- объявляемые курсоры (declared cursors), создаваемые как DECLARE CURSOR;
- размещаемые курсоры (allocated cursors), создаваемые как ALLOCATE CURSOR. Этот тип курсоров иногда называется выделенными курсорами.
Объявляемые и размещаемые курсоры могут иметь динамические параметры.
Для создания курсоров используются следующие операторы:
- ALLOCATE CURSOR, в котором курсор указывается идентификатором переменной, описывающей SQL-оператор;
- DECLARE CURSOR, в котором курсор указывается идентификатором откомпилированного SQL-оператора.
Например:
str1:='INSERT INTO tbl1 VALUES (1,10) ';EXEC SQL ALLOCATE cur1 CURSOR FOR:str1;EXEC SQL PREPARE stmt1 FROM:str1;EXEC SQL DECLARE cur2 CURSOR FOR stmt1;Открываются и закрываются динамические курсоры, как и статически создаваемые, операторами OPEN и CLOSE. Но при открытии курсора, имеющего динамические параметры, должна быть указана фраза USING.
Например:
str1:='SELECT f2 FROM tbl1 WHERE f1=? ';EXEC SQL ALLOCATE cur1 CURSOR FOR:str1;EXEC SQL OPEN cur1 USING:f2;EXEC SQL FETCH cur1 INTO:f1;Во фразе INTO оператора FETCH может быть указан как список INTO-переменных, так и SQL-дескриптор.
|
|
|
