Предотвращение выполнения всего скрипта
Пересказ статьи Solomon Rutzky. Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC)
Иногда, работая над SQL-скриптом, мне не нужно выполнять весь написанный код. Например, при разработке сложного скрипта мне требуется тестировать части кода. Зачастую случайные нажатия F5, когда я забываю выделить нужный фрагмент, запускают весь скрипт от начала с нежелательными последствиями.
Итак, мне требуется способ, который бы гарантировал, что скрипт не будет выполнен, если нет никакого выделенного фрагмента. Я хочу рассказать о PARSEONLY, который я прекратил использовать, и о NOEXEC, который я освоил попутно, и который может оказаться полезным в других ситуациях.
PARSEONLY и NOEXEC являются установками уровня сессии, которые разрешаются/запрещаются с помощью операторов SET (подобно NOCOUNT). Чтобы понять эффект, который они оказывают, нам потребуется знать основы того, как SQL Server обрабатывает непосредственно передаваемые запросы - ad hoc (хранимые процедуры выполняются как RPC - удаленные вызовы процедур - и обрабатываются несколько иначе; здесь мы их не рассматриваем).
Чтобы понять как работают эти сессионные установки, следует знать два главных аспекта непосредственной обработки запросов:
1. SQL Server получает и обрабатывает один пакет за раз. Пакет запросов - это один или более операторов T-SQL. От клиентского приложения могут приходить несколько пакетов, разделенных "разделителем пакетов". Разделителем пакетов, используемым в SSMS и SQLCMD, является GO на отдельной строке (ну, технически вы можете добавить после GO значение типа INT, чтобы исполнить пакет соответствующее число раз). Этот разделитель можно изменить в конфигурации, однако я не видел никого, кто бы использовал что-то иное, кроме принимаемого по умолчанию GO.
При наличии множества пакетов каждый из них передается в SQL Server, когда клиентское приложение достигает разделителя пакетов. Пакеты представляют собой полностью независимые "запросы" (т.е. sys.dm_exec_requests). Вот почему локальные переменные неизвестны между пакетами, и поэтому вам необходимы конструкции уровня сессии подобные CONTEXT_INFO, session_context и временные объекты для передачи информации от одного пакета в следующий. И пакет должен быть завершен прежде, чем будет принят следующий пакет.
2. Выполнение проходит три фазы:
Парсинг -> Компиляция (и оптимизация) -> Выполнение
1) На стадии парсинга выполняется только синтаксический разбор операторов. Т.е. проверяется, что операторы представляют собой правильные инструкции T-SQL, что переменные описаны, что простые идентификаторы (не имеющие ограничителей) соответствуют правилам именования и т.д.
2) На этапе компиляции проверяются ограничения, гарантирующие, что объекты существуют, проводится оптимизация и строится план выполнения.
3) На этапе выполнения операторы в пакете выполняются и возвращаются все сообщения и/или результаты.
В предположении отсутствия ошибок, каждый пакет запросов будет проходить все эти три фазы, прежде чем будет обработан следующий пакет.
Хотя это не вполне по теме данной статьи, поскольку последующая информация имеет отношение в тому, как обрабатываются скрипты и пакеты, я замечу, что команды и переменные SQLCMD (доступные для SQLCMD.exe и в "режиме SQLCMD" при работе в SSMS) обрабатываются:
Установка PARSEONLY прекратит обработку на фазе компиляции (и, если это не очевидно, фаза выполнения будет пропущена). Поскольку установка разрешает только фазу парсинга, она имеет эффект только на этом этапе. Это означает следующее:
1. она может быть применена повсюду в пакете;
2. она не может быть применена по условию, поскольку оператор IF оценивается на фазе выполнения;
3. если вы разрешаете, а затем запрещаете эту установку в одном и том же пакете, то пакет будет выполняться в обычном режиме, поскольку запрет опции происходит на фазе парсинга, разрешая обработку следующих двух этапов.
Для лучшей иллюстрации поведения, давайте рассмотрим пять простых примеров.
Следующий пример демонстрирует ожидаемую ошибку, когда запрос ссылается на несуществующий столбец в существующей таблице:
Следующий пример показывает, что разрешение и запрещение PARSEONLY в одном и том же пакете запросов не оказывает эффекта:
Следующий пример показывает, что:
1. вам необходимо запрещать опцию в отдельном пакете, и
2. где бы в пакете ни была включена опция, это не имеет значения, поскольку все происходит в фазе парсинга.
Следующий пример показывает, что даже с включенной опцией, синтаксис пакета все равно будет проверяться:
Следующий пример показывает, что когда эта опция включена, проверяется только правильность синтаксиса, но не существование объектов, использование типов данных и т.д.
Имя это в виду, легче понять, что происходит в следующих примерах:
и
Пакет выше возвращает следующий результат:
Вот шаблон, который я использую в своих скриптах. Операторы PRINT сверху и снизу выполняются только тогда, если запускается весь скрипт; в этом случае ничего больше не выполняется.
Установка NOEXEC будет только предотвращать фазу выполнения, но только для операторов, которые идут после неё. Поскольку эта установка обрабатывается на фазе выполнения:
1. её следует включать до любых операторов, выполнение которых вы хотите предотвратить;
2. она может быть применена по условию, поскольку операторы IF также оцениваются на этапе выполнения;
3. вы можете включать и выключать эту установку в одном и том же пакете.
Эта опция работает на фазе выполнения и, следовательно, требует меньше объяснений, поскольку работает как и многие другие операторы.
Следующий пример показывает, что пока операторы в пакете не вызывали ошибок синтаксического разбора, они конечно не компилировались:
Следующий пример показывает, что эта опция действительно оказывает влияние на фазе выполнения, и может быть использована в том же пакете:
Следующий пример иллюстрирует использование этой опции по условию:
Если @StopExecuting = 1, на выходе будет получено:
Однако если @StopExecuting = 0, на выходе будет получено:
Если вы комбинируете эти две опции, то получите эффект только от применения PARSEONLY, поскольку обработка не дойдет о фазы компиляции. Например:
Выполнение приведенного выше кода T-SQL не даст никаких ошибок. Это объясняется тем, что там нет ошибок синтаксического анализа, и первый пакет завершается включением опции PARSEONLY. Поскольку эта опция обрабатывается на фазе парсинга: а) обработка не доходит до фазы компиляции, и б) не имеет значения, где размещена опция в пакете. Обработка продолжается во втором пакете. Здесь тоже не возникает ошибок, поскольку все еще включена опция PARSEONLY.
Если закомментировать SET PARSEONLY ON; а затем выполнить код, вы получите следующие ошибки:
Почему мы получили ошибку "Деление на ноль"? Потому что опция NOEXEC не была включена. Ошибка "Неверное имя столбца" прервала выполнение пакета на стадии компиляции, поэтому оператор SET NOEXEC ON; не выполнялся. Обработка продолжилась со следующего пакета, и ни одна из опций не была установлена.
Если вы также закомментируете строку SELECT x..., то опять не получите никаких ошибок. Это из-за того, что NOEXEC теперь будет включена.
О чем это говорит нам? Нет смысла использовать обе опции сразу. Используйте одну из них.
Установка этих двух опций оказывает следующий эффект на обработку:
Для моих целей я предпочитаю использовать только PARSEONLY, поскольку а) мне не нужно обрабатывать что-нибудь по условию, и б) ошибки, которые могут иметь место, являются ошибками синтаксического анализа. Так что по большей части я вижу только ту ошибку, которая говорит мне, что я случайно выполнил весь скрипт (вместо потенциальных ошибок компиляции, которые иногда заставляют меня думать, что опция NOEXEC не сработала и что некоторые утверждения фактически выполнялись).
Обзор
PARSEONLY и NOEXEC являются установками уровня сессии, которые разрешаются/запрещаются с помощью операторов SET (подобно NOCOUNT). Чтобы понять эффект, который они оказывают, нам потребуется знать основы того, как SQL Server обрабатывает непосредственно передаваемые запросы - ad hoc (хранимые процедуры выполняются как RPC - удаленные вызовы процедур - и обрабатываются несколько иначе; здесь мы их не рассматриваем).
Чтобы понять как работают эти сессионные установки, следует знать два главных аспекта непосредственной обработки запросов:
1. SQL Server получает и обрабатывает один пакет за раз. Пакет запросов - это один или более операторов T-SQL. От клиентского приложения могут приходить несколько пакетов, разделенных "разделителем пакетов". Разделителем пакетов, используемым в SSMS и SQLCMD, является GO на отдельной строке (ну, технически вы можете добавить после GO значение типа INT, чтобы исполнить пакет соответствующее число раз). Этот разделитель можно изменить в конфигурации, однако я не видел никого, кто бы использовал что-то иное, кроме принимаемого по умолчанию GO.
При наличии множества пакетов каждый из них передается в SQL Server, когда клиентское приложение достигает разделителя пакетов. Пакеты представляют собой полностью независимые "запросы" (т.е. sys.dm_exec_requests). Вот почему локальные переменные неизвестны между пакетами, и поэтому вам необходимы конструкции уровня сессии подобные CONTEXT_INFO, session_context и временные объекты для передачи информации от одного пакета в следующий. И пакет должен быть завершен прежде, чем будет принят следующий пакет.
2. Выполнение проходит три фазы:
Парсинг -> Компиляция (и оптимизация) -> Выполнение
1) На стадии парсинга выполняется только синтаксический разбор операторов. Т.е. проверяется, что операторы представляют собой правильные инструкции T-SQL, что переменные описаны, что простые идентификаторы (не имеющие ограничителей) соответствуют правилам именования и т.д.
2) На этапе компиляции проверяются ограничения, гарантирующие, что объекты существуют, проводится оптимизация и строится план выполнения.
3) На этапе выполнения операторы в пакете выполняются и возвращаются все сообщения и/или результаты.
В предположении отсутствия ошибок, каждый пакет запросов будет проходить все эти три фазы, прежде чем будет обработан следующий пакет.
SQLCMD / режим SQLCMD
Хотя это не вполне по теме данной статьи, поскольку последующая информация имеет отношение в тому, как обрабатываются скрипты и пакеты, я замечу, что команды и переменные SQLCMD (доступные для SQLCMD.exe и в "режиме SQLCMD" при работе в SSMS) обрабатываются:
- только для текущего пакета! Если имеются дополнительные пакеты, которые содержат команды и/или переменные SQLCMD, они будут обработаны, когда клиентское приложение перейдет к этому пакету;
- перед фазой парсинга. Это обусловлено тем, что команды и переменные SQLCMD обрабатываются только клиентским приложением и неизвестны SQL Server (подобно разделителю пакетов GO);
- позволяя переменным SQLCMD сохранять свои значения во время выполнения всего множества пакетов (в отличие от переменных T-SQL).
PARSEONLY
Установка PARSEONLY прекратит обработку на фазе компиляции (и, если это не очевидно, фаза выполнения будет пропущена). Поскольку установка разрешает только фазу парсинга, она имеет эффект только на этом этапе. Это означает следующее:
1. она может быть применена повсюду в пакете;
2. она не может быть применена по условию, поскольку оператор IF оценивается на фазе выполнения;
3. если вы разрешаете, а затем запрещаете эту установку в одном и том же пакете, то пакет будет выполняться в обычном режиме, поскольку запрет опции происходит на фазе парсинга, разрешая обработку следующих двух этапов.
Основы
Для лучшей иллюстрации поведения, давайте рассмотрим пять простых примеров.
Следующий пример демонстрирует ожидаемую ошибку, когда запрос ссылается на несуществующий столбец в существующей таблице:
-- Пример 1:
DECLARE @T TABLE (Col1 INT);
SELECT [Col2] FROM @T;
/*
Msg 207, Level 16, State 1, Line XXXXX
Неправильное имя столбца 'Col2'.
*/
GO
Следующий пример показывает, что разрешение и запрещение PARSEONLY в одном и том же пакете запросов не оказывает эффекта:
-- Пример 2:
SET PARSEONLY ON;
DECLARE @T TABLE (Col1 INT);
SELECT [Col2] FROM @T;
SET PARSEONLY OFF;
/*
Msg 207, Level 16, State 1, Line XXXXX
Неправильное имя столбца 'Col2'.
*/
GO
Следующий пример показывает, что:
1. вам необходимо запрещать опцию в отдельном пакете, и
2. где бы в пакете ни была включена опция, это не имеет значения, поскольку все происходит в фазе парсинга.
-- Пример 3:
DECLARE @T TABLE (Col1 INT);
SELECT [Col2] FROM @T;
SET PARSEONLY ON;
GO
SET PARSEONLY OFF;
/*
Команды выполнены успешно.
*/
GO
Следующий пример показывает, что даже с включенной опцией, синтаксис пакета все равно будет проверяться:
-- Пример 4:
SET PARSEONLY ON;
DECLARE @T TABLE (Col1 INT);
SELECT [Col2] FROM @Tt;
GO
SET PARSEONLY OFF;
/*
Msg 1087, Level 15, State 2, Line XXXXX
Должна быть описана переменная "@Tt".
*/GO
Следующий пример показывает, что когда эта опция включена, проверяется только правильность синтаксиса, но не существование объектов, использование типов данных и т.д.
-- Пример 5:
SET PARSEONLY ON;
SELECT [GhostColumn] FROM sys.objects;
DECLARE @T INT = NEWID();
GO
SET PARSEONLY OFF;
/*
Команды выполнены успешно.
*/
Имя это в виду, легче понять, что происходит в следующих примерах:
GO
PRINT 1;
SET PARSEONLY ON;
PRINT 2;
SET PARSEONLY OFF;
PRINT 3;
GO
/*
1
2
3
*/
и
-- Динамический SQL не влияет на вызовы / внешний контекст
EXEC(N'SET PARSEONLY ON;');
PRINT 4;
GO
PRINT 5;
IF (1 = 0)
BEGIN
SET PARSEONLY ON; -- имеет эффект на фазе парсинга
END;
PRINT 6;
-- Динамический SQL не влияет на вызовы / внешний контекст
EXEC(N'PRINT 7; SET PARSEONLY OFF; PRINT 8;');
PRINT 9;
GO
PRINT 10;
SET PARSEONLY OFF; -- имеет эффект на фазе парсинга
PRINT 11;
GO
Пакет выше возвращает следующий результат:
4
10
11
Шаблон
Вот шаблон, который я использую в своих скриптах. Операторы PRINT сверху и снизу выполняются только тогда, если запускается весь скрипт; в этом случае ничего больше не выполняется.
PRINT 'Этот скрипт не предназначен для выполнения всего сразу!';
PRINT 'Пожалуйста, выделяйте и выполняйте каждый раздел отдельно.';
GO
SET PARSEONLY ON;
GO
PRINT 'Делаем что-нибудь.';
GO
-- Помещаем в конце скрипта, чтобы гарантировать, что PARSEONLY не остался включенным.
-- Это должно быть в другом пакете.
SET PARSEONLY OFF;
RAISERROR('Упс. Похоже, что вы нажали F5, Control-E, или кнопку "Выполнить".', 16, 1);
GO
NOEXEC
Установка NOEXEC будет только предотвращать фазу выполнения, но только для операторов, которые идут после неё. Поскольку эта установка обрабатывается на фазе выполнения:
1. её следует включать до любых операторов, выполнение которых вы хотите предотвратить;
2. она может быть применена по условию, поскольку операторы IF также оцениваются на этапе выполнения;
3. вы можете включать и выключать эту установку в одном и том же пакете.
Основы
Эта опция работает на фазе выполнения и, следовательно, требует меньше объяснений, поскольку работает как и многие другие операторы.
Следующий пример показывает, что пока операторы в пакете не вызывали ошибок синтаксического разбора, они конечно не компилировались:
SET NOEXEC ON;
PRINT 'Это не будет напечатано.';
GO
SELECT [GhostColumn] FROM sys.objects;
DECLARE @T INT = NEWID();
GO
SET NOEXEC OFF;
/*
Msg 207, Level 16, State 1, Line XXXXX
Неверное имя столбца 'GhostColumn'.
Msg 206, Level 16, State 2, Line XXXXX
Операнд неверного типа: uniqueidentifier несовместим с int
*/
Следующий пример показывает, что эта опция действительно оказывает влияние на фазе выполнения, и может быть использована в том же пакете:
GO
PRINT 12;
SET NOEXEC ON;
PRINT 13; -- Этот оператор не будет выполнен
SET NOEXEC OFF;
PRINT 14;
GO
/*
12
14
*/
Следующий пример иллюстрирует использование этой опции по условию:
-- Динамический SQL не влияет на вызывающий / внешний контекст
EXEC(N'SET NOEXEC ON;');
PRINT 15;
GO
DECLARE @StopExecuting BIT;
SET @StopExecuting = 1;
PRINT 16;
IF (@StopExecuting = 1)
BEGIN
SET NOEXEC ON; -- Имеет эффект на фазе выполнения
END;
PRINT 17;
-- Динамический SQL не влияет на вызывающий / внешний контекст
EXEC(N'PRINT 18; SET NOEXEC OFF; PRINT 19;');
PRINT 20;
--GO
SET NOEXEC OFF; -- Имеет эффект на фазе выполнения
PRINT 21;
GO
Если @StopExecuting = 1, на выходе будет получено:
15
16
21
Однако если @StopExecuting = 0, на выходе будет получено:
15
16
17
18
19
20
21
Шаблон
DECLARE @RequirementsMet BIT;
PRINT 'Проверяем и готовим...';
SET @RequirementsMet = 0;
IF (@RequirementsMet = 0)
BEGIN;
RAISERROR('сообщение об ошибке...', 10, 1) WITH NOWAIT;
SET NOEXEC ON;
END;
-- Это не должно выполняться, если условия не выполнены
PRINT 'Это не выполняется, если NOEXEC установлен в ON!';
-- Поместите в конце скрипта, чтобы не оставить включенным NOEXEC.
-- Это может быть в том же пакете, или же в другом.
SET NOEXEC OFF;
GO
PARSEONLY и NOEXEC вместе
Если вы комбинируете эти две опции, то получите эффект только от применения PARSEONLY, поскольку обработка не дойдет о фазы компиляции. Например:
SET NOEXEC ON; -- влияет на следующие операторы
SELECT x FROM sys.objects; -- ошибка компиляции (нет ошибки, если PARSEONLY в ON)
SET PARSEONLY ON; -- влияет на весь пакет независимо от местоположения
GO
SELECT 1 / 0; -- ошибка выполнения (нет ошибки, если PARSEONLY или NOEXEC установлены в ON)
GO
SET PARSEONLY OFF;
SET NOEXEC OFF;
SELECT 1;
-- 1
Выполнение приведенного выше кода T-SQL не даст никаких ошибок. Это объясняется тем, что там нет ошибок синтаксического анализа, и первый пакет завершается включением опции PARSEONLY. Поскольку эта опция обрабатывается на фазе парсинга: а) обработка не доходит до фазы компиляции, и б) не имеет значения, где размещена опция в пакете. Обработка продолжается во втором пакете. Здесь тоже не возникает ошибок, поскольку все еще включена опция PARSEONLY.
Если закомментировать SET PARSEONLY ON; а затем выполнить код, вы получите следующие ошибки:
Msg 207, Level 16, State 1, Line XXXXX
Неверное имя столбца 'x'.
Msg 8134, Level 16, State 1, Line XXXXX
Деление на ноль.
Почему мы получили ошибку "Деление на ноль"? Потому что опция NOEXEC не была включена. Ошибка "Неверное имя столбца" прервала выполнение пакета на стадии компиляции, поэтому оператор SET NOEXEC ON; не выполнялся. Обработка продолжилась со следующего пакета, и ни одна из опций не была установлена.
Если вы также закомментируете строку SELECT x..., то опять не получите никаких ошибок. Это из-за того, что NOEXEC теперь будет включена.
О чем это говорит нам? Нет смысла использовать обе опции сразу. Используйте одну из них.
Заключение
Установка этих двух опций оказывает следующий эффект на обработку:
| PARSEONLY | NOEXEC | Фаза выполнения | Замечания |
|---|---|---|---|
| ON | Парсинг | ||
| ON | ON | Парсинг | То же самое, что и только PARSEONLY |
| ON | Парсинг->Компиляция (и оптимизация) | ||
| Парсинг->Компиляция (и оптимизация)->Выполнение | (по умолчанию) |
Для моих целей я предпочитаю использовать только PARSEONLY, поскольку а) мне не нужно обрабатывать что-нибудь по условию, и б) ошибки, которые могут иметь место, являются ошибками синтаксического анализа. Так что по большей части я вижу только ту ошибку, которая говорит мне, что я случайно выполнил весь скрипт (вместо потенциальных ошибок компиляции, которые иногда заставляют меня думать, что опция NOEXEC не сработала и что некоторые утверждения фактически выполнялись).
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой