Sql while: MS SQL Server и T-SQL

Помощник SQL Server

 Сервер: сообщение 135, уровень 16, состояние 1, строка 1
Нельзя использовать оператор BREAK вне области действия
ПОКА заявление. 

Как следует из сообщения, это сообщение об ошибке возникает при использовании инструкции BREAK вне инструкции WHILE. Вот несколько примеров возникновения этой ошибки:

 -- BREAK используется внутри условия IF, но вне оператора WHILE
ЕСЛИ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ «X» ИЗ [dbo]. [Пользователь]
               ГДЕ [имя пользователя] = 'sqlserver')
    СЛОМАТЬ
    
Сообщение 135, уровень 15, состояние 1, строка 2
Нельзя использовать инструкцию BREAK вне области действия инструкции WHILE.
 

 -- BREAK используется для выхода из хранимой процедуры
СОЗДАТЬ ПРОЦЕДУРУ [dbo].[usp_GetOrderDetails]
    @OrderID INT
КАК

ЕСЛИ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ «X» ИЗ [dbo]. [Заказ]
               ГДЕ [IDЗаказа] = @IDЗаказа)
    СЛОМАТЬ

SELECT * FROM [dbo].[OrderDetail]
ГДЕ [IDЗаказа] = @IDЗаказа
ИДТИ

Сообщение 135, уровень 15, состояние 1, строка 2
Нельзя использовать инструкцию BREAK вне области действия инструкции WHILE.
 

 -- Неуместное условие BREAK
ОБЪЯВИТЬ @Counter INT
ОБЪЯВИТЬ @UserID INT

УСТАНОВИТЕ @Счетчик = 0
УСТАНОВИТЕ @UserID = 1
ПОКА СУЩЕСТВУЕТ (SELECT * FROM [dbo]. [UserTransaction]
              ГДЕ [UserID] = @UserID)
    УДАЛИТЬ TOP (10) FROM [dbo].[UserTransaction]
    ГДЕ [UserID] = @UserID

    SET @Counter = Счетчик + 1

    ЕСЛИ @Счетчик > 10
        СЛОМАТЬ


Сообщение 135, уровень 15, состояние 1, строка 2
Нельзя использовать инструкцию BREAK вне области действия инструкции WHILE.
 

Решение/Временное решение:

Как следует из сообщения, оператор BREAK можно использовать только внутри области действия оператора WHILE. В тех случаях, когда необходимо пропустить набор инструкций Transact-SQL, если определенное условие не выполняется, вместо использования инструкции BREAK можно использовать инструкцию GOTO. Оператор GOTO изменяет поток выполнения на метку. Оператор Transact-SQL или операторы, следующие за GOTO, пропускаются, и обработка продолжается с указанной метки. Операторы GOTO и метки могут использоваться в любом месте внутри процедуры, пакета или блока операторов, а также могут быть вложенными.

Используя первый пример ранее, вот как это будет выглядеть при замене инструкции BREAK на инструкцию GOTO:

, ЕСЛИ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ «X» ИЗ [dbo]. [Пользователь]
               ГДЕ [имя пользователя] = 'sqlserver')
    ПЕРЕЙТИ к недействительному пользователю

/*
    Набор инструкций Transact-SQL для выполнения здесь
*/

Недействительный пользователь:

/*
    Другой набор операторов Transact-SQL для выполнения здесь
*/
 

В случае выхода из хранимой процедуры при выполнении определенного условия вместо использования инструкции BREAK следует использовать инструкцию RETURN. Оператор RETURN безоговорочно завершает выполнение запроса или процедуры. RETURN является немедленным и полным, и его можно использовать в любой момент для выхода из процедуры, пакета или блока операторов. Операторы, следующие за RETURN, не выполняются.

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

 СОЗДАТЬ ПРОЦЕДУРУ [dbo]. [usp_GetOrderDetails]
    @OrderID INT
КАК

ЕСЛИ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ «X» ИЗ [dbo]. [Заказ]
               ГДЕ [IDЗаказа] = @IDЗаказа)
    ВОЗВРАЩАТЬСЯ

SELECT * FROM [dbo].[OrderDetail]
ГДЕ [IDЗаказа] = @IDЗаказа
ИДТИ
 

При выполнении блока операторов или группы операторов в цикле WHILE группы операторов должны быть заключены в блоки BEGIN END. В противном случае только первая инструкция Transact-SQL в этом блоке инструкций будет частью цикла WHILE. Другая инструкция Transact-SQL будет выполнена после того, как условие в цикле WHILE больше не выполняется или в цикле WHILE встречается инструкция BREAK.

Это относится к третьему приведенному выше примеру, в котором блок операторов не заключен в блок BEGIN END. Только первая инструкция Transact-SQL, в данном случае инструкция DELETE, является частью цикла WHILE. Другие инструкции Transact-SQL не входят в цикл WHILE и будут выполняться, если условие WHILE не будет выполнено.

Вот как будет выглядеть скрипт с блоком BEGIN … END, помещенным на место и избегающим ошибки:

 DECLARE @Counter INT
ОБЪЯВИТЬ @UserID INT

УСТАНОВИТЕ @Счетчик = 0
УСТАНОВИТЕ @UserID = 1
ПОКА СУЩЕСТВУЕТ (SELECT * FROM [dbo]. [UserTransaction]
              ГДЕ [UserID] = @UserID)
НАЧИНАТЬ
    УДАЛИТЬ TOP (10) FROM [dbo].[UserTransaction]
    ГДЕ [UserID] = @UserID

    SET @Counter = Счетчик + 1

    ЕСЛИ @Счетчик > 10
        СЛОМАТЬ
КОНЕЦ
 

Структура решения, которую вы должны знать

Время чтения: 7 минут

Цикл WHILE — это один из основных инструментов, который вы должны знать, когда только начинаете работать с Microsoft SQL Server.

Иногда при работе с SQL Server требуется, чтобы определенный код выполнялся несколько раз в цикле, пока какое-то условие выполняется. Как только это условие становится ложным, выполнение цикла прекращается.

Цикл WHILE — это инструмент, который мы можем использовать для достижения этой цели.

Цикл WHILE — это одна из нескольких структур принятия решений, доступных нам в SQL Server. Другой полезной структурой принятия решений является структура IF…ELSE IF….ELSE. Это еще одна очень распространенная структура принятия решений, которую вы, вероятно, увидите и будете использовать как профессионал в области баз данных, поэтому важно, чтобы вы также понимали, как она работает. Если вы пропустили учебник по этому вопросу, вы должны проверить его.

Заявление IF…ELSE IF…ELSE: все, что вы должны знать.

В этом уроке мы поговорим только об этих 3 темах цикла WHILE:

1. Что такое цикл WHILE?
2. Пример использования цикла WHILE
3. Советы, рекомендации и ссылки

Начнем.

1. Что такое цикл WHILE?

Цикл WHILE используется для многократного выполнения блока кода, пока выполняется какое-либо условие. Каждая итерация цикла должна двигаться к тому, чтобы сделать наше условие ложным. Как только это условие становится ложным, цикл завершается.

Вот основная структура и синтаксис цикла WHILE:

 WHILE(<условие истинно>)
НАЧИНАТЬ
<выполнить некоторый код>
<движение к тому, чтобы сделать условие ложным>
КОНЕЦ 

Каждая итерация цикла должна продвигать цикл к тому, чтобы сделать условие ложным. В начале каждой итерации цикла он проверяет, действительно ли наше условие ложно .

Есть несколько вещей, которые вы должны знать о синтаксисе цикла WHILE:

1. Ключевые слова BEGIN…ELSE должны присутствовать, если тело цикла WHILE состоит более чем из одной строки. Если ваше тело состоит всего из одной строки, вы можете пропустить эти ключевые слова. Но, честно говоря, я не уверен, как вы могли бы иметь цикл WHILE длиной всего в одну строку.
2. Скобки вокруг проверки условия цикла WHILE необязательны . Мне нравится использовать круглые скобки, потому что я думаю, что это делает код более читабельным.

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

2. Пример использования цикла WHILE

Давайте начнем с очень простого примера, чтобы дать вам представление.

Допустим, мы хотим просто вывести на экран числа от 1 до 12 . Мы можем достичь этого трудным путем , выполнив следующее:

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

Давайте разберем, что происходит в цикле. Во-первых, проверьте @count переменная, которую мы используем:

Мы используем эту переменную @count для продвижения нашего цикла вперед.

Условие, которое мы проверяем, если @count меньше 13 . В первом запуске цикла @count — это просто 1 , потому что это значение, которое мы установили. Так как 1 меньше 13 , мы вводим тело цикла:

В теле цикла мы делаем две вещи. Мы просто печатаем текущее значение @count , а затем увеличиваем нашу переменную @count на 1 .

Что касается первой части, обратите внимание, что нам нужно было использовать функцию CONVERT для преобразования нашего целого числа @count в тип данных строки символов. Системная функция печати требует, чтобы все было в формате строки символов. Мы решили использовать символьный тип данных VARCHAR.

Ознакомьтесь с полным руководством по CONVERT: SQL Server CONVERT: практическое руководство с примерами0061 @count

переменная на 1. Это очень важно .

Тело цикла while должно перемещать цикл вперед, к завершению цикла.

Когда мы увеличиваем @count , мы перемещаем его все ближе и ближе к числу 13 , что завершит цикл . Запомните условие, которое мы проверяем. Мы продолжаем проходить цикл, пока наша переменная @count меньше 13 .

В конце 12-го запуска цикла наша переменная @count будет снова увеличена на 1 , как обычно. Таким образом, он будет установлен на 13 . Затем мы доходим до проверки условия:

Значит, число 13 меньше, чем 13 ?

Нет , так что берем под залог . Цикл завершен.

Что если мы забыли включить последнюю строку тела, где мы увеличиваем @count на 1? Переменная @count никогда не изменит по сравнению с исходным значением 1 , что означает, что наше условие всегда будет истинным, НАВСЕГДА.

Это, друзья мои, то, что мы называем бесконечным циклом и

(Кроме того, мне пришлось вручную завершить запрос, чтобы остановить его. Если бы я этого не сделал, одному Богу известно, как долго он выполнялся бы.)

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

Поверьте мне, я профессионал с бесконечным циклом на данный момент.

Реальный пример цикла WHILE

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

Допустим, у нас есть розничный магазин в городе Боулдер, штат Колорадо. У нас есть таблица BoulderStore для хранения основной информации о клиентах. Взгляните на содержимое этой таблицы:

Круто. Обратите внимание, что значения CustID : от 1 до 12. 

Если мы хотим напечатать адрес электронной почты каждого клиента , мы можем легко добиться этого с помощью цикла WHILE:

Давайте разберем и это.

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

(Обратите внимание, что на этот раз я не использовал круглые скобки вокруг своего условия. Опять же, использование скобок необязательно )

Затем в теле цикла мы создаем простую переменную @emailAddr . Мы заполняем эту переменную адресом электронной почты клиента, чьи CustID соответствует @IDVal , в котором мы сейчас находимся:

Затем мы печатаем собранную информацию и увеличиваем нашу переменную условия:

Не забудьте изменить свою переменную условия ! Он должен двигаться в направлении окончания цикла!

Использование циклов WHILE в курсорах

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

Информации о создании и использовании курсоров SQL Server достаточно, чтобы оправдать собственное руководство. Обязательно ознакомьтесь с ним:

Курсоры SQL Server: практическое руководство

3. Советы, рекомендации и ссылки

Вот несколько советов и рекомендаций, которые вам следует знать о цикле WHILE:

Совет № 1: Вы можете иметь более одного условия в цикле WHILE

Конечно, вы можете иметь более одной проверки условия цикла WHILE. Вы можете разделить свои чеки, используя либо И или ИЛИ операторы. Но помните, вам нужно убедиться, что все условие в конечном итоге оценивается как false , завершая цикл.

Дополнительную информацию об операторах AND и OR можно найти в учебнике IF…ELSE IF…ELSE:

Заявление IF…ELSE IF…ELSE: все, что вам следует знать.

Ссылки

Существует замечательная книга под названием T-SQL Fundamentals, написанная Ициком Бен-Ганом , в котором рассматриваются несколько основных понятий, которые вы должны знать о SQL Server, в том числе о том, как создавать циклы WHILE. Это одна из немногих книг, которые помогли мне понять многие темы SQL Server. Вы не пожалеете, что приобрели эту книгу, , поверьте мне, . Обязательно получи сегодня!

Следующие шаги:

Оставьте комментарий , если этот урок был вам полезен!

Еще одна замечательная структура принятия решений, о которой вы должны знать, — оператор CASE.