Оператор EXECUTE STATEMENT имеет две редакции. Впервые оператор EXECUTE STATEMENT появился в версии 1.5.3, в последствии в альфа релизе версии 2.5 синтаксис оператора был изменен с целью поддержки Cross-Database и параметризированных запросов таким образом, что синтаксис версии 1.5.3 является частным случаем синтаксиса версии 2.5.
Первая редакция синтаксиса оператора
0.9 | 1.0 | 1.5.3 | 1.5.4 | 1.5.5 | 2.0 | 2.0.3 | 2.0.4 | 2.1 | 2.5 | 3.0 |
---|---|---|---|---|---|---|---|---|---|---|
- | - | Да | Да | Да | Да | Да | Да | Да | Частный случай | Частный случай |
Вторая редакция синтаксиса оператора
0.9 | 1.0 | 1.5.3 | 1.5.4 | 1.5.5 | 2.0 | 2.0.3 | 2.0.4 | 2.1 | 2.5 | 3.0 |
---|---|---|---|---|---|---|---|---|---|---|
- | - | - | - | - | - | - | - | - | Да | Да |
[FOR] EXECUTE STATEMENT (<sql_statement_string> | :<SQL_STMT_VARIABLE> ) [INTO :<VARIABLE_1> [,:<VARIABLE_2> [,<VARIABLE_N>] ] ] [DO <sql_operator>];
Параметр | Значение |
---|---|
<sql_statement_string> | Строка, содержащая правильный SQL-запрос, или это может быть |
<SQL_STMT_VARIABLE> | переменная, значению которой присваивается правильный SQL-запрос |
<VARIABLE_1> | Переменная или переменные, перечисленные через запятую, куда необходимо вернуть значения в случае селективного запроса. |
<sql_operator> | SQL-оператор, который необходимо выполнять в случае циклического выполнения селективного запроса. Оператор может быть как простой, так и составной - состоящий из нескольких операторов, заключенных в операторные скобки BEGIN…END. |
[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_statement_string> | Строка, содержащая правильный SQL-запрос, или это может быть |
<SQL_STMT_VARIABLE> | переменная, значению которой присваивается правильный SQL-запрос. В отличие от первой редакции синтаксиса оператора, во второй редакции синтаксиса SQL-запрос может содержать параметры. |
<input_parameters> | Присваивание значений параметрам в случае параметризированного SQL-запроса. |
<connection_string> | Строка подключения к базе данных в формате, используемом в API-функции сервера isc_attach_database(). |
AUTONOMOUS или COMMON | Указывает, выполнять оператор в текущей или автономной транзакции. Если не задан явно, выполнение оператора происходит в текущей транзакции. |
<user_name> | Имя пользователя, от имени которого будет выполнен SQL-оператор. В качестве значения можно передавать контекстную переменную CURRENT_USER. Если параметр не задан явно, то используется текущий пользователь. |
<password> | Строка или переменная, содержащая пароль пользователя, от имени которого будет выполнен SQL-оператор, для подключения к базе данных. Если не указан параметр <user_name> или значение параметра <user_name> равно CURRENT_USER, то указание пароля можно пропустить при выполнении оператора. |
<VARIABLE_1> | Переменная или переменные, перечисленные через запятую, куда необходимо вернуть значения в случае селективного запроса. |
<sql_operator> | SQL-оператор, который необходимо выполнять в случае циклического выполнения селективного запроса. Оператор может быть как простой, так и составной - состоящий из нескольких операторов, заключенных в операторные скобки BEGIN…END. |
Описание к первой редакции синтаксиса оператора относится и ко второй в виду вертикальной совместимости версий сервера.
Оператор позволяет выполнить правильный SQL-запрос в PSQL-блоках, триггерах и процедурах.
SQL-запрос должен быть тривиальным или атомарным. То есть Вы не можете выполнить несколько SQL-запросов, перечислив их через разделитель в рамках одного оператора EXECUTE STATEMENT. Для того, чтобы выполнить несколько SQL-запросов, Вам необходимо вызвать EXECUTE STATEMENT для каждого из них.
SQL-запрос может быть:
В первой редакции синтаксиса оператора EXECUTE STATEMENT SQL-запрос не может содержать никаких параметров !
EXECUTE STATEMENT потенциально опасен:
Это не означает что вы не должны использовать эту возможность, но используйте её только в том случае если другие варианты невозможны.
Для облегчения поиска багов в коде, или для их исключения, возвращаемые параметры жестко проверяются на соответствие объявленным типам данных. Это помогает избежать ошибок, когда приведение типов может как вызвать ошибку, так и не вызвать. Например, строка '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
При этом:
Раздел оператора ON EXTERNAL DATA SOURCE позволяет выполнять cross-database запросы, т.е. запросы к другим базам данных из текущего подключения при соблюдении следующих условий:
Раздел WITH {AUTONOMOUS | COMMON} TRANSACTION позволяет выполнять SQL-оператор, соответственно, в рамках автономной или текущей транзакции при соблюдении следующих условий:
Раздел 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.Пример работы с метаданными. Пусть имеется база данных, в которой существует следующие строгие правила создания объектов.
Требуется: создать для всех справочников триггеры, посылающие события после вставки, изменения или удаления данных из таблиц-справочников. Текстом посылаемого сообщения будет имя таблицы, в которой произошло изменение.
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
($firebird)/doc/sql.extensions/README.execute_statement.txt
($firebird)/doc/sql.extensions/README.execute_statement2.txt
Обсуждение
Как вариант для There can be no dependency checks to ensure that objects referred to in the SQL statement string are not dropped from the database or modified in a manner that would break your statement. For example, a DROP TABLE request for the table used in the compiled EXECUTE PROCEDURE statement will be granted.
Не может быть выполнена проверка зависимостей, для гарантии того, что объекты, указанные в строке SQL операторов не удаляются из базы данных или не изменяются таким образом, который нарушит функционирование вашей задачи. Например, допускается выполнение команды DROP TABLE для таблицы, используемой в откомпилированной процедуре, вызываемой с помощью оператора EXECUTE PROCEDURE.