EXECUTE STATEMENT

Оператор EXECUTE STATEMENT имеет две редакции. Впервые оператор EXECUTE STATEMENT появился в версии 1.5.3, в последствии в альфа релизе версии 2.5 синтаксис оператора был изменен с целью поддержки Cross-Database и параметризированных запросов таким образом, что синтаксис версии 1.5.3 является частным случаем синтаксиса версии 2.5.

Первая редакция синтаксиса оператора

Вторая редакция синтаксиса оператора

PSQL

  [FOR] 
    EXECUTE STATEMENT (<sql_statement_string> | :<SQL_STMT_VARIABLE> ) 
    [INTO :<VARIABLE_1> [,:<VARIABLE_2> [,<VARIABLE_N>] ] ] 
  [DO 
    <sql_operator>];

  [FOR] 
    EXECUTE STATEMENT {<sql_statement_string> | :<SQL_STMT_VARIABLE> } [ (<input_parameters>) ]
      [ON EXTERNAL [DATA SOURCE] <connection_string>]
      [WITH { AUTONOMOUS | COMMON } TRANSACTION]
      [AS USER <user_name> [PASSWORD <password>] ]
      [WITH CALLER PRIVILEGES]
    [INTO :<VARIABLE_1> [,:<VARIABLE_2> [,<VARIABLE_N>] ] ]
  [DO
    <sql_operator>;]

Описание к первой редакции синтаксиса оператора относится и ко второй в виду вертикальной совместимости версий сервера.

Оператор позволяет выполнить правильный SQL-запрос в PSQL-блоках, триггерах и процедурах.

SQL-запрос должен быть тривиальным или атомарным. То есть Вы не можете выполнить несколько SQL-запросов, перечислив их через разделитель в рамках одного оператора EXECUTE STATEMENT. Для того, чтобы выполнить несколько SQL-запросов, Вам необходимо вызвать EXECUTE STATEMENT для каждого из них.

SQL-запрос может быть:

• селективным запросом, возвращающим набор данных.
• DML-запросом, содержащим операторы добавления, изменения или удаление записей из таблиц.
• вызовом выполнения хранимой процедуры EXECUTE PROCEDURE, в том числе и селективной, т.е. возвращающей набор данных.
• DDL-выражением, содержащим операторы создания, изменения или удаления объектов базы данных: доменов, таблиц, просмотров, хранимых процедур. При этом нельзя выполнить оператор создания и удаления базы данных.
• запросом, хранящим исходный код PSQL-блока.

В первой редакции синтаксиса оператора EXECUTE STATEMENT SQL-запрос не может содержать никаких параметров !

EXECUTE STATEMENT потенциально опасен:

1. Не делается никакой проверки запроса на выполнения. Так же не может быть проверен результат запроса и успешность его выполнения.
2. Не может быть выполнена проверка зависимостей в случае выполнения DML-оператора для гарантии того, что объекты, указанные в строке SQL операторов не удаляются из базы данных или не изменяются таким образом, который нарушит функционирование вашей задачи. Например, допускается выполнение команды DROP TABLE для таблицы, которая используется в откомпилированных процедурах или триггерах, что повлечет за собой сбой их работы.
3. В основном операции с EXECUTE STATEMENT более медленые так как не делается подготовка(prepared) запроса и, соответствено, подготовка происходит каждый раз при выполнении.

Это не означает что вы не должны использовать эту возможность, но используйте её только в том случае если другие варианты невозможны.

Для облегчения поиска багов в коде, или для их исключения, возвращаемые параметры жестко проверяются на соответствие объявленным типам данных. Это помогает избежать ошибок, когда приведение типов может как вызвать ошибку, так и не вызвать. Например, строка '1234' может быть сконвертирована в целое число 1234, а строка 'abc' в целое число сконвертирована быть не может.

В первой редакции синтаксиса оператора EXECUTE STATEMENT если PSQL-блок, хранимая процедура или триггер, вызывающий EXECUTE STATEMENT, имеет особые права на обьект базы данных, то SQL-запрос выполняемый в EXECUTE STATEMENT не будет иметь таких прав, а только права пользователя под которым выполняется эта процедура. (прим.переводчика: во второй редакции синтаксиса оператора данное ограничение снято введением раздела WITH CALLER PRIVILEGES)

В описании ко второй редакции синтаксиса оператора следует учитывать замечания к первой редакции в виду вертикальной совместимости версий сервера.

Порядок следования следующих разделов оператора EXECUTE STATEMENT

  .......
      [ON EXTERNAL [DATA SOURCE] <connection_string>]
      [WITH { AUTONOMOUS | COMMON } TRANSACTION]
      [AS USER <user_name> [PASSWORD <password>] ]
      [WITH CALLER PRIVILEGES]
  .......

не является жестким, то есть разделы могут перечисляться в произвольном порядке. При этом повторное использование разделов оператора в рамках одного оператора EXECUTE STATEMENT запрещено!

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

1)Если вы желаете использовать параметризированный запрос, то Вы обязаны заключить строку или переменную, содержащую текст Вашего параметризированного запроса в круглые скобки. Например:

  EXECUTE STATEMENT (:P_SQL_STMT) (P1 := 'abc', P2 := :MY_VARIABLE);

2) В параметризированных запросах поддерживаются оба типа параметров: именованные и безымянные. Например:

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
BEGIN
  -- Именованные параметры запроса
  S = 'INSERT INTO TTT VALUES (:A, :B, :A)'; 
  EXECUTE STATEMENT (:S) (A := CURRENT_TRANSACTION, B := CURRENT_CONNECTION)
  -- Безымянные параметры запроса
  S = 'INSERT INTO TTT VALUES (?, ?)'; 
  EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION)
END

При этом:

• одновременное использование именованных и безымянных параметров в одном и том же запросе запрещено.
• передача значений безымянным параметрам должна происходить в том же порядке, в каком они встречаются в тексте запроса.
• присваивание значений параметров должно осуществляться при помощи специального оператора « := », аналогичного оператору присваивания в языке Паскаль.
• если при присваивании значения параметру передается выражение (например, «P1 := GEN_ID(MY_GENERATOR, 1)» или «P1 := :I + 1»), то значение этого выражения вычисляется только один раз !

Раздел оператора ON EXTERNAL DATA SOURCE позволяет выполнять cross-database запросы, т.е. запросы к другим базам данных из текущего подключения при соблюдении следующих условий:

• если при вызове оператора EXECUTE STATEMENT пропущен раздел ON EXTERNAL DATA SOURCE и пропущен раздел AS USER, то вызываемый SQL-оператор выполняется в контексте текущего подключения и от имени текущего пользователя.
• если при вызове оператора EXECUTE STATEMENT пропущен раздел ON EXTERNAL DATA SOURCE, но при этом присутствует раздел AS USER и параметр <user_name> равен имени текущего пользователя CURRENT_USER, то вызываемый SQL-оператор выполняется в контексте текущего подключения и от имени текущего пользователя.
• если при вызове оператора EXECUTE STATEMENT пропущен раздел ON EXTERNAL DATA SOURCE, но при этом присутствует раздел AS USER и параметр <user_name> не равен имени текущего пользователя CURRENT_USER, то вызываемый SQL-оператор выполнится в контексте отдельного подключения к текущей базе данных. То есть для выполнения SQL-оператора сервер создаст отдельное подключение к текущей базе данных. Данная особенность может быть полезна при необходимости выполнения SQL-операторов от имени другого пользователя, аналогично как используется команда sudo командных оболочек операционных систем семейства UNIX/LINUX/FreeBSD.
• Параметр <connection_string> раздела ON EXTERNAL DATA SOURCE должен быть в формате, используемом в API-функции сервера isc_attach_database(), в виде: [<имя_хоста (или IP-адрес)>[/<порт подключения>]:<протокол>]<имя файла базы данных (или псевдоним из aliases.conf)>. Например, «localhost:/mnt/sda1/bases/db.fdb», «192.168.0.1/3052:MAIN_CORPORATE_DATABASE», «D:\DB\MY_DATABASE.FDB».
• В случае подключения к внешним базам данных, новое подключение использует ту же самую кодировку символов, что и у текущего подключения к базе данных. В некоторых случаях данное поведение сервера может служить источником ошибок, если у Вас кодировки символов, указанных при создании баз данных, отличаются.

Раздел WITH {AUTONOMOUS | COMMON} TRANSACTION позволяет выполнять SQL-оператор, соответственно, в рамках автономной или текущей транзакции при соблюдении следующих условий:

• если оператор EXECUTE STATEMENT выполняется в контексте автономной транзакции, то автономная транзакция будет принудительно завершена при завершении текущей транзакции.
• если оператор EXECUTE STATEMENT выполняется в контексте автономной транзакции, то автономная транзакция будет подтверждением (commit) в случае успешного выполнения SQL-запроса или будет отменена (rollback) в случае не успешного выполнения SQL-запроса.
• если оператор EXECUTE STATEMENT выполняется в контексте автономной транзакции, то автономная транзакция стартует с тем же уровнем изоляции данных, что и текущая, в контексте которой вызывается оператор EXECUTE STATEMENT.
• если раздел при вызове оператора EXECUTE STATEMENT пропущен, то оператор выполняется в рамках текущей транзакции.

Раздел AS USER позволяет задавать имя пользователя, от имени которого будет выполнен SQL-запрос. Если раздел при вызове оператора EXECUTE STATEMENT пропущен, то будет использоваться текущий пользователь CURRENT_USER.

Раздел WITH CALLER PRIVILEGES можно использовать в том случае, когда оператор EXECUTE STATEMENT выполняется для текущего подключения к базе данных (раздел оператора ON EXTERNAL DATA SOURCE пропущен). Он позволяет выполнить SQL-запрос с привелегиями доступа к данным вызывающего EXECUTE STATEMENT PSQL-блока, хранимой процедуры или триггера. (прим.переводчика: напомним, что в первой редакции синтаксиса оператора EXECUTE STATEMENT SQL-запрос этого оператора выполнялся с привелегиями пользователя, от имени которого был запущен PSQL-блок, но при этом не учитывались привелегии самого PSQL-блока, хранимой процедуры или триггера. В некоторых случаях это заставляло давать дополнительные права пользователям на объект базы данных, что ставило под угрозу безопасность).

1. Выполнение простого, не-селективного запроса.

CREATE PROCEDURE DynamicSampleOne (
  Q_NAME VARCHAR(100)
)
AS
  DECLARE VARIABLE P_SQL_STMT VARCHAR(1024);
  DECLARE VARIABLE PAR      INTEGER;
BEGIN
  SELECT MIN(T1.SOME_FIELD) FROM SOME_TABLE T1 INTO :PAR;
  P_SQL_STMT = 'EXECUTE PROCEDURE ' || :Q_NAME || '(' || CAST(:PAR AS VARCHAR(20)) || ')';
  EXECUTE STATEMENT :P_SQL_STMT;
END

2.Выполнение простого, селективного запроса, возвращающего одну запись.

CREATE PROCEDURE DynamicSampleTwo (
  Q_TABLE_NAME VARCHAR(100)
)
AS
  DECLARE VARIABLE PAR INTEGER;
BEGIN
  EXECUTE STATEMENT 'SELECT MAX(T1.CHECKFIELD) FROM ' || :Q_TABLE_NAME || ' T1 ' INTO :PAR;
  IF (:PAR > 100) THEN
    EXCEPTION EX_OVERFLOW 'Overflow in ' || :Q_TABLE_NAME;
END

3.Выполнение селективного запроса, возвращающего набор данных

CREATE PROCEDURE DynamicSampleThree (
   Q_FIELD_NAME VARCHAR(100)
  ,Q_TABLE_NAME VARCHAR(100)
)RETURNS(
  LINE VARCHAR(32000)
)
AS
  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
  LINE = '';
  FOR 
    EXECUTE STATEMENT 'SELECT T1.' || :Q_FIELD_NAME || ' FROM ' || :Q_TABLE_NAME || ' T1 '
    INTO :P_ONE_LINE
  DO
    IF (:P_ONE_LINE IS NOT NULL) THEN
      LINE = :LINE || :P_ONE_LINE || ' ';
  SUSPEND;
END

4.Пример работы с метаданными.

Обновить статистику для всех индексов в базе данных:

EXECUTE BLOCK
AS
  DECLARE VARIABLE P_INDEX_NAME TYPE OF COLUMN RDB$INDICES.RDB$INDEX_NAME;
BEGIN
  FOR
    SELECT I.RDB$INDEX_NAME
    FROM   RDB$INDICES I
    WHERE  (I.RDB$SYSTEM_FLAG = 0)
    INTO   :P_INDEX_NAME
  DO
    EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :P_INDEX_NAME;
END

5.Пример работы с метаданными. Пусть имеется база данных, в которой существует следующие строгие правила создания объектов.

• имена всех таблиц начинается на 'TABL$' ;
• имена всех таблиц-справочников начинается на 'TABL$R_';
• имена всех триггеров начинается на 'TRIG$' и содержит в конце имени буквенное обозначение типа ( {BEFORE|AFTER} {DELETE || INSERT || UPDATE} ) триггера, например: '_BI', '_BIU', '_ADIU', '_AD';
• имя триггера, следующее после служебного обозначения 'TRIG$' и перед обозначением его типа '_BI', '_BIU' совпадает с именем таблицы, к которой он относится, следующим за служебным обозначением 'TABL$'.

Требуется: создать для всех справочников триггеры, посылающие события после вставки, изменения или удаления данных из таблиц-справочников. Текстом посылаемого сообщения будет имя таблицы, в которой произошло изменение.

EXECUTE BLOCK RETURNS (
  CNT INTEGER
)
AS
  DECLARE VARIABLE P_RELATION_NAME VARCHAR(32);
  DECLARE VARIABLE P_TRIGGER_NAME  VARCHAR(32);
  DECLARE VARIABLE P_SQL           BLOB SUB_TYPE 1;
BEGIN
  CNT = 0;
  FOR
    SELECT R.RDB$RELATION_NAME
    FROM   RDB$RELATIONS R
    WHERE  (R.RDB$VIEW_BLR IS NULL)                              -- отсекаем просмотры VIEW 
      AND  (R.RDB$SYSTEM_FLAG = 0)                               -- отсекаем системные таблицы
      AND  (R.RDB$RELATION_NAME STARTING WITH 'TABL$R_')         -- выбираем только таблицы-справочники
    ORDER BY R.RDB$RELATION_NAME                                 -- упорядочим по алфавиту
    INTO   :P_RELATION_NAME
  DO
    BEGIN
    P_TRIGGER_NAME = SUBSTRING(:P_RELATION_NAME FROM 5);         -- убираем из имени таблицы начальные символы 'TABL'
    P_TRIGGER_NAME = 'TRIG'||:P_TRIGGER_NAME;                    -- добавляем в начало 'TRIG'
    IF(CHAR_LENGTH(:P_TRIGGER_NAME) > 26)THEN                    -- имя триггера не может быть больше 31-го символа,
                                                                 -- а к текущему имени нам нужно еще добавить '_ADIU'
      P_TRIGGER_NAME = SUBSTRING(:P_TRIGGER_NAME FROM 1 FOR 26); -- следовательно, "хвостовые" символы отсекаем 
 
    P_SQL =                                                      -- формируем DDL оператор создания триггера
      'CREATE OR ALTER TRIGGER ' || :P_TRIGGER_NAME || '_ADIU FOR ' || 
        :P_RELATION_NAME || ' ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
      'ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 255 ' || 
        ASCII_CHAR(13) || ASCII_CHAR(10) ||
      'AS ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
      'BEGIN ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
      '  POST_EVENT ''' || :P_RELATION_NAME || '''; ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
      'END ' || ASCII_CHAR(13) || ASCII_CHAR(10);
 
    EXECUTE STATEMENT :P_SQL WITH AUTONOMOUS TRANSACTION;        -- создаем триггер
    CNT = :CNT + 1;
    END
  SUSPEND;
END

EXECUTE PROCEDURE, AUTONOMOUS TRANSACTION, EXECUTE BLOCK, PROCEDURE, TRIGGER

($firebird)/doc/sql.extensions/README.execute_statement.txt

($firebird)/doc/sql.extensions/README.execute_statement2.txt