Проблема: Мне нужно использовать цикл в Transact (T-SQL), но похоже, что в Microsoft SQL Server нет конструкции цикла FOR. Как мне написать цикл в моих SQL-скриптах? Можете ли вы предоставить пример цикла с оператором SELECT, включающим начало, счетчик цикла и условную логику окончания, которую можно использовать в хранимых процедурах?

Решение: В программировании часто требуется выполнить итерацию над некоторым набором, массивом или списком. Например, для каждой строки заказа продажи в счете вы хотите рассчитать возможную скидку. Здесь ключевые слова – “для каждого”. Для каждого элемента набора вы хотите выполнить некоторое действие. Другими словами, вы хотите “пройтись по” набору. Во многих языках программирования есть оператор цикла FOR. Однако в SQL-коде нет оператора цикла FOR. Но есть альтернативные SQL-команды, которые можно использовать для реализации функциональности цикла.

Оператор цикла WHILE

Самая простая альтернатива циклу FOR в SQL Server – это цикл WHILE. Цикл WHILE выполняется до тех пор, пока логическое условие истинно. Это означает, что цикл WHILE может выполняться бесконечно долго, если в логическом условии допущена ошибка. Однако с помощью цикла WHILE можно имитировать цикл FOR. Вот пример:

DECLARE @i TINYINT = 0;
DECLARE @length TINYINT = 10;

WHILE @i <= @length
BEGIN
    PRINT @i;
    SET @i += 1;
END

В этом примере скрипт начинается с @i = 0, выводит значение переменной @i, увеличивает эту переменную на 1 и продолжает делать это, пока @i не станет равным 10. Содержимое цикла WHILE заключено между ключевыми словами BEGIN и END. Оператор += является сокращением для оператора SET @i = @i + 1;.

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

Курсоры

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

Вот пример использования курсора для реализации логики цикла:

DECLARE @i TINYINT;
DECLARE cursor_numbers CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
    SELECT Number = 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
    UNION ALL
    SELECT 7
    UNION ALL
    SELECT 8
    UNION ALL
    SELECT 9
    UNION ALL
    SELECT 10;

OPEN [cursor_numbers];
FETCH NEXT FROM [cursor_numbers] INTO @i;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @i;
    FETCH NEXT FROM [cursor_numbers] INTO @i;
END

CLOSE [cursor_numbers];
DEALLOCATE [cursor_numbers];

В этом примере объявляется и открывается курсор с именем “cursor_numbers”. Оператор FETCH NEXT извлекает следующую строку из курсора и присваивает значение переменной @i. Цикл WHILE продолжается до тех пор, пока есть еще строки для извлечения. Оператор PRINT выводит значение @i. Наконец, курсор закрывается и удаляется.

Циклический обход объектов базы данных

Иногда вам нужно пройтись по объектам базы данных или сервера вместо фактических данных. Это можно сделать с помощью курсора или цикла WHILE в сочетании с системными представлениями. Например, вы можете захотеть переорганизовать все индексы в базе данных, создать резервную копию всех пользовательских баз данных или очистить часть таблиц в базе данных.

Вот пример использования курсора для очистки всех таблиц фактов в образцовой базе данных хранилища данных Adventure Works:

DECLARE @stmt VARCHAR(500);
DECLARE @i TINYINT = 1;
DECLARE @n TINYINT;

SELECT @n = MAX(ID)
FROM [#statements];

WHILE @i <= @n
BEGIN
    SELECT @stmt = [SQLStatement]
    FROM [#statements]
    WHERE ID = @i;

    PRINT @stmt;
    --EXEC sp_executesql @stmt; -- фактическое очищение таблицы
    SET @i += 1;
END

В этом примере используется курсор для обхода временной таблицы с именем “#statements”, которая содержит операторы TRUNCATE TABLE для таблиц фактов. Оператор PRINT выводит каждый оператор, а фактическое выполнение динамического SQL закомментировано для тестирования.

Таблица Tally или Numbers Table

Таблица Tally, также известная как таблица “numbers”, представляет собой таблицу с одним столбцом, содержащим последовательные числа. Ее можно использовать для реализации цикла FOR, рассматривая каждую строку таблицы чисел как итерацию цикла. Преимущество таблицы чисел заключается в том, что она позволяет выполнять операции над наборами, которые обычно выполняются быстрее в SQL Server.

Вот пример использования таблицы чисел для разделения строки на отдельные символы:

DECLARE @mystring VARCHAR(100) = 'Hello MSSQLTips!';

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02