Sql while: MS SQL Server и T-SQL

Содержание

Цикл SQL Server WHILE запускается дважды с обновлением, один раз без

Задавать вопрос

спросил
1 год, 6 месяцев назад

Изменено
1 год, 4 месяца назад

Просмотрено
1к раз

Вот это странно. Вот мой код:

 ПЕЧАТЬ 'Определить курсор'
ОБЪЯВИТЬ cursor1 КУРСОР ДЛЯ
ВЫБЕРИТЕ b.EmploymentTypeID
FROM EmploymentTypes b INNER JOIN #ListEmployments l
        на b.EmploymentID = l.EmploymentID
PRINT 'Открыть курсор'
ОТКРЫТЬ курсор1
FETCH NEXT FROM cursor1 INTO @pEmploymentTypeID
ПОКА @@FETCH_STATUS = 0
НАЧИНАТЬ
 PRINT '1) Внутри цикла WHILE. @pEmploymentTypeID: ' + convert(varchar(20), @pEmploymentTypeID)
 PRINT '2) Внутри цикла WHILE. @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
 -- поработай немного
 ОБНОВЛЕНИЕ
       SETEmploymentTypeRD = EmploymentTypeID
    ГДЕEmploymentTypeID = @pEmploymentTypeID
      И тип занятостиRD = 0
 PRINT '3) Внутри цикла WHILE.  Pre-FETCH @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
 FETCH NEXT FROM cursor1 INTO @pEmploymentTypeID
 PRINT '4) Внутри цикла WHILE. Пост-FETCH @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
  
КОНЕЦ
ЗАКРЫТЬ курсор1
УДАЛИТЬ курсор1

Когда я запускаю это, цикл выполняется дважды, и я вижу следующий вывод.

 Определить курсор
Открытый курсор
1) Внутри цикла WHILE. @pEmploymentTypeID: 695837
2) Внутри цикла WHILE. @@FETCH_STATUS: 0
3) Внутри цикла WHILE. Предварительная выборка @@FETCH_STATUS: 0
4) Внутри цикла WHILE. Пост-FETCH @@FETCH_STATUS: 0
1) Внутри цикла WHILE. @pEmploymentTypeID: 695837
2) Внутри цикла WHILE. @@FETCH_STATUS: 0
3) Внутри цикла WHILE. Предварительная выборка @@FETCH_STATUS: 0
4) Внутри цикла WHILE. Пост-FETCH @@FETCH_STATUS: -1
Убежал в ROLLBACK

Если я закомментирую оператор UPDATE после комментария «—поработайте», вывод будет следующим:

 Определить курсор
Открытый курсор
1) Внутри цикла WHILE. @pEmploymentTypeID: 695837
2) Внутри цикла WHILE.  @@FETCH_STATUS: 0
3) Внутри цикла WHILE. Предварительная выборка @@FETCH_STATUS: 0
4) Внутри цикла WHILE. Пост-FETCH @@FETCH_STATUS: -1
Убежал в ROLLBACK

Почему цикл выполняется дважды при включенном обновлении?

sql-сервер

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

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

Вместо этого выполните простое совместное обновление. Это одиночная инструкция , которая выполняет все в вашем скрипте. Сервер имеет специальную логику для учета изменяемых строк при чтении.

 ОБНОВЛЕНИЕ и др.
SETEmploymentTypeRD = EmploymentTypeID
ОТ EmploymentTypes et
ВНУТРЕННЕЕ СОЕДИНЕНИЕ #ListEmployments l ON et.EmploymentID = l.EmploymentID
ГДЕ et.EmploymentTypeRD = 0;

Для этого лучше всего подойдет операция на основе SET. Однако, если вы должны использовать курсор или просто хотите узнать, почему он работает не так, как вы ожидаете…

Вы обновляете строки базовых таблиц, используемых курсором, а затем заставляете его возвращать дополнительную строку после первого обновления. Обновление во время второго цикла ничего не делает, потому что EmploymentTypeRD больше не равен 0, значит, курсор существует. Попробуйте добавить аргумент STATIC к объявлению курсора. Это кэширует результаты курсора во временную таблицу в том состоянии, в котором они существовали до начала цикла и выполнения любых обновлений.

Просмотрите документацию Microsoft по аргументам курсора здесь: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15

Ниже приведен измененный код.

 ПЕЧАТЬ 'Определить курсор'
ОБЪЯВИТЬ cursor1 CURSOR **STATIC** ДЛЯ
ВЫБЕРИТЕ b.EmploymentTypeID
FROM EmploymentTypes b INNER JOIN #ListEmployments l
        на b.EmploymentID = l. EmploymentID
PRINT 'Открыть курсор'
ОТКРЫТЬ курсор1
FETCH NEXT FROM cursor1 INTO @pEmploymentTypeID
ПОКА @@FETCH_STATUS = 0
НАЧИНАТЬ
 PRINT '1) Внутри цикла WHILE. @pEmploymentTypeID: ' + convert(varchar(20), @pEmploymentTypeID)
 PRINT '2) Внутри цикла WHILE. @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
 -- поработай немного
 ОБНОВЛЕНИЕ
       SETEmploymentTypeRD = EmploymentTypeID
    ГДЕEmploymentTypeID = @pEmploymentTypeID
      И тип занятостиRD = 0
 PRINT '3) Внутри цикла WHILE. Pre-FETCH @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
 FETCH NEXT FROM cursor1 INTO @pEmploymentTypeID
 PRINT '4) Внутри цикла WHILE. Пост-FETCH @@FETCH_STATUS: ' + convert(varchar(20), @@FETCH_STATUS)
  
КОНЕЦ
ЗАКРЫТЬ курсор1
УДАЛИТЬ курсор1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Помощник SQL Server

Сообщения об ошибках SQL Server — сообщение 135

Сообщение об ошибке

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

Причины

Оператор BREAK выходит из самого внутреннего цикла в операторе WHILE или IF… ELSE. Любые операторы, идущие после ключевого слова END, отмечающего конец цикла, выполняются. BREAK часто, но не всегда, запускается условием проверки IF.

Как следует из сообщения, это сообщение об ошибке возникает при использовании инструкции 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].