Инструменты пользователя

Инструменты сайта


execute_statement

Различия

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

Ссылка на это сравнение

Both sides previous revision Предыдущая версия
execute_statement [2013/06/13 16:36]
80.252.147.23
execute_statement [2013/06/18 15:27] (текущий)
80.252.147.23
Строка 1: Строка 1:
 +====== EXECUTE STATEMENT ​ ======
 +
 +Оператор 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 ^
 +|  -  |  -  |  -  |  -  |  -  |  -  |  -  |  -  |  -  |  Да  |  Да  |
 +
 +===== Доступно в =====
 +[[raznovidnosti_jazyka_sql|PSQL]]
 +
 +===== Формат =====
 +
 +
 +==== Первая редакция синтаксиса оператора ====
 +<code sql>
 +  [FOR] 
 +    EXECUTE STATEMENT (<​sql_statement_string>​ | :<​SQL_STMT_VARIABLE>​ ) 
 +    [INTO :<​VARIABLE_1>​ [,:<​VARIABLE_2>​ [,<​VARIABLE_N>​] ] ] 
 +  [DO 
 +    <​sql_operator>​];​
 +</​code>​
 +^ Параметр ​ ^ Значение ​ ^
 +| <​sql_statement_string> ​ | Строка,​ содержащая правильный SQL-запрос,​ или это может быть |
 +| <​SQL_STMT_VARIABLE> ​ | переменная,​ значению которой присваивается правильный SQL-запрос ​ |
 +| <​VARIABLE_1> ​ | Переменная или переменные,​ перечисленные через запятую,​ куда необходимо вернуть значения в случае [[select|селективного]] запроса. ​ |
 +| <​sql_operator> ​ | SQL-оператор,​ который необходимо выполнять в случае циклического выполнения [[select|селективного]] запроса. Оператор может быть как простой,​ так и составной - состоящий из нескольких операторов,​ заключенных в операторные скобки BEGIN...END. |
 +
 +
 +==== Вторая редакция синтаксиса оператора ====
 +<code sql>
 +  [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>;​]
 +</​code>​
 +^ Параметр ​ ^ Значение ​ ^
 +| <​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> ​ | Переменная или переменные,​ перечисленные через запятую,​ куда необходимо вернуть значения в случае [[select|селективного]] запроса. ​ |
 +| <​sql_operator> ​ | SQL-оператор,​ который необходимо выполнять в случае циклического выполнения [[select|селективного]] запроса. Оператор может быть как простой,​ так и составной - состоящий из нескольких операторов,​ заключенных в операторные скобки BEGIN...END. |
 +
 +===== Описание =====
 +
 +==== Описание к первой редакции ==== 
 +
 +__//​Описание к первой редакции синтаксиса оператора относится и ко второй в виду вертикальной совместимости версий сервера.//​__
 +
 +Оператор позволяет выполнить правильный SQL-запрос в [[execute_block|PSQL-блоках]],​ [[trigger|триггерах]] и [[procedure|процедурах]].
 +
 +:!: SQL-запрос должен быть тривиальным или атомарным. То есть Вы не можете выполнить несколько SQL-запросов,​ перечислив их через разделитель в рамках одного оператора EXECUTE STATEMENT. Для того, чтобы выполнить несколько SQL-запросов,​ Вам необходимо вызвать EXECUTE STATEMENT для каждого из них.
 +
 +SQL-запрос может быть:
 +  * [[select|селективным]] запросом,​ возвращающим набор данных.
 +  * DML-запросом,​ содержащим операторы добавления,​ изменения или удаление записей из таблиц.
 +  * вызовом выполнения [[procedure|хранимой процедуры]] [[EXECUTE PROCEDURE]],​ в том числе и [[select|селективной]],​ т.е. возвращающей набор данных.
 +  * DDL-выражением,​ содержащим операторы создания,​ изменения или удаления объектов базы данных:​ [[domain|доменов]],​ [[table|таблиц]],​ [[view|просмотров]],​ [[procedure|хранимых процедур]]. При этом нельзя выполнить оператор [[create_database|создания]] и [[drop_database|удаления]] базы данных.
 +  * запросом,​ хранящим исходный код [[execute_block|PSQL-блока]].
 +
 +:!: __В первой редакции синтаксиса__ оператора EXECUTE STATEMENT SQL-запрос не может содержать никаких параметров !
 +
 +:!: EXECUTE STATEMENT потенциально опасен:​
 +  - Не делается никакой проверки запроса на выполнения. Так же не может быть проверен результат запроса и успешность его выполнения.
 +  - Не может быть выполнена проверка зависимостей в случае выполнения DML-оператора для гарантии того, что объекты,​ указанные в строке SQL операторов не удаляются из базы данных или не изменяются таким образом,​ который нарушит функционирование вашей задачи. Например,​ допускается выполнение команды DROP TABLE для таблицы,​ которая используется в откомпилированных процедурах или триггерах,​ что повлечет за собой сбой их работы.
 +  - В основном операции с EXECUTE STATEMENT более медленые так как не делается подготовка(prepared) запроса и, соответствено,​ подготовка происходит каждый раз при выполнении.
 +
 +Это не означает что вы не должны использовать эту возможность,​ но используйте её только в том случае если другие варианты невозможны.
 +
 +:!: Для облегчения поиска багов в коде, или для их исключения,​ возвращаемые параметры жестко проверяются на соответствие объявленным типам данных. Это помогает избежать ошибок,​ когда приведение типов может как вызвать ошибку,​ так и не вызвать. Например,​ строка '​1234'​ может быть сконвертирована в целое число 1234, а строка '​abc'​ в целое число сконвертирована быть не может.
 +
 +:!:  __В первой редакции синтаксиса__ оператора EXECUTE STATEMENT если PSQL-блок,​ хранимая процедура или триггер,​ вызывающий EXECUTE STATEMENT, имеет особые права на обьект базы данных,​ то SQL-запрос выполняемый в EXECUTE STATEMENT не будет иметь таких прав, а только права пользователя под которым выполняется эта процедура. //​(прим.переводчика:​ во второй редакции синтаксиса оператора данное ограничение снято введением раздела WITH CALLER PRIVILEGES)//​
 +
 +
 +==== Описание ко второй редакции ==== 
 +
 +__// В описании ко второй редакции синтаксиса оператора следует учитывать замечания к первой редакции в виду вертикальной совместимости версий сервера.//​__
 +
 +:!: Порядок следования следующих разделов оператора EXECUTE STATEMENT ​
 +<code sql>
 +  .......
 +      [ON EXTERNAL [DATA SOURCE] <​connection_string>​]
 +      [WITH { AUTONOMOUS | COMMON } TRANSACTION]
 +      [AS USER <​user_name>​ [PASSWORD <​password>​] ]
 +      [WITH CALLER PRIVILEGES]
 +  .......
 +</​code>​
 +не является жестким,​ то есть разделы могут перечисляться в произвольном порядке. При этом повторное использование разделов оператора в рамках одного оператора EXECUTE STATEMENT запрещено!
 +
 +
 +:!: __Во второй редакции синтаксиса__ оператора разрешено использовать параметризированные запросы при соблюдении следующих условий:​
 +
 +1)Если вы желаете использовать параметризированный запрос,​ то Вы обязаны заключить строку или переменную,​ содержащую текст Вашего параметризированного запроса в круглые скобки. Например:​
 +<code sql>
 +  EXECUTE STATEMENT (:​P_SQL_STMT) (P1 := '​abc',​ P2 := :​MY_VARIABLE);​
 +</​code>​
 +
 +2) В параметризированных запросах поддерживаются оба типа параметров:​ именованные и безымянные. Например:​
 +<code sql>
 +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
 +</​code>​
 +При этом:
 +  * одновременное использование именованных и безымянных параметров в одном и том же запросе запрещено.
 +  * передача значений безымянным параметрам должна происходить в том же порядке,​ в каком они встречаются в тексте запроса.
 +  * присваивание значений параметров должно осуществляться при помощи специального оператора " := ", аналогичного оператору присваивания в языке Паскаль.
 +  * если при присваивании значения параметру передается выражение (например,​ "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"​.
 +  * В случае подключения к внешним базам данных,​ новое подключение использует ту же самую кодировку символов,​ что и у текущего подключения к базе данных. В некоторых случаях данное поведение сервера может служить источником ошибок,​ если у Вас кодировки символов,​ указанных при [[create_database|создании баз данных]],​ отличаются.
 +
 +
 +
 +:!: Раздел 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 [[execute_block|PSQL-блока]],​ [[procedure|хранимой процедуры]] или [[trigger|триггера]]. //​(прим.переводчика:​ напомним,​ что в первой редакции синтаксиса оператора EXECUTE STATEMENT SQL-запрос этого оператора выполнялся с привелегиями пользователя,​ от имени которого был запущен PSQL-блок,​ но при этом не учитывались привелегии самого PSQL-блока,​ хранимой процедуры или триггера. В некоторых случаях это заставляло давать дополнительные права пользователям на объект базы данных,​ что ставило под угрозу безопасность).// ​
 +
 +===== Пример =====
 +
 +1. Выполнение простого,​ не-[[select|селективного]] запроса.
 +<code sql>
 +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
 +</​code>​
 +
 +2.Выполнение простого,​ [[select|селективного]] запроса,​ возвращающего одну запись.
 +<code sql>
 +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
 +</​code>​
 +
 +3.Выполнение [[select|селективного]] запроса,​ возвращающего набор данных
 +<code sql>
 +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
 +</​code>​
 +
 +4.Пример работы с метаданными.
 +
 +Обновить статистику для всех индексов в базе данных:​
 +
 +<code sql>
 +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
 +</​code>​
 +
 +
 +5.Пример работы с метаданными.
 +Пусть имеется база данных,​ в которой существует следующие строгие правила создания объектов.
 +  * имена всех [[table|таблиц]] начинается на '​TABL$'​ ;
 +  * имена всех [[table|таблиц]]-справочников начинается на '​TABL$R_';​
 +  * имена всех [[trigger|триггеров]] начинается на '​TRIG$'​ и содержит в конце имени буквенное обозначение типа ( {BEFORE|AFTER} {DELETE || INSERT || UPDATE} ) триггера,​ например:​ '​_BI',​ '​_BIU',​ '​_ADIU',​ '​_AD';​
 +  * имя [[trigger|триггера]],​ следующее после служебного обозначения '​TRIG$'​ и перед обозначением его типа '​_BI',​ '​_BIU'​ совпадает с именем таблицы,​ к которой он относится,​ следующим за служебным обозначением '​TABL$'​.
 +
 +Требуется:​ создать для всех справочников триггеры,​ посылающие события после вставки,​ изменения или удаления данных из таблиц-справочников. Текстом посылаемого сообщения будет имя таблицы,​ в которой произошло изменение.
 +<code sql>
 +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
 +</​code>​
 +
 +===== См. также =====
 +[[EXECUTE PROCEDURE]], ​ [[AUTONOMOUS TRANSACTION]], ​  ​[[EXECUTE BLOCK]], ​ [[PROCEDURE]], ​ [[TRIGGER]]
 +
 +===== Источник =====
 +($firebird)/​doc/​sql.extensions/​README.execute_statement.txt
 +
 +($firebird)/​doc/​sql.extensions/​README.execute_statement2.txt
  
execute_statement.txt · Последние изменения: 2013/06/18 15:27 — 80.252.147.23