Динамический SQL

Создание операторов динамического 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.