Создание операторов динамического 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 имеет следующее формальное описание:
EXECUTE [ GLOBAL | LOCAL ] operator_sql [ INTO {variable .,…} | { SQL DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name } ] [ USING {variable .,…} | { SQL DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name } ]
Фраза 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.
