Цикл через переменную таблицы в T-SQL: альтернатива курсору

Быстрый ответ

В T-SQL предусмотрена возможность итераций по переменной таблицы с использованием цикла WHILE и курсора:

DECLARE @MyTable TABLE (ID INT, Name NVARCHAR(100));
INSERT INTO @MyTable VALUES (1, 'Alice'), (2, 'Bob');

DECLARE @ID INT, @Name NVARCHAR(100);
DECLARE myCursor CURSOR FOR SELECT ID, Name FROM @MyTable;

OPEN myCursor;
FETCH NEXT FROM myCursor INTO @ID, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Выведем на экран имена разработчиков.
   PRINT 'ID:' + CAST(@ID AS VARCHAR(10)) + ', Name:' + @Name + ' (Выбор гуру кодирования!)';
   FETCH NEXT FROM myCursor INTO @ID, @Name;
END;

CLOSE myCursor;
DEALLOCATE myCursor;

Однако, подобный подход не подойдёт для всех объёмов данных, это как водить Lamborghini по узким улицам с лежачими полицейскими — слишком мощно.

Использование реляционных альтернатив для повышения производительности

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

Курсоры кажутся удобными, но реляционный подход — это любимый выбор оперативной работы SQL. Он позволяет выполнять агрегационные операции быстрее и оставлять курсоры позади:

-- Реляционный подход, не требующий использования циклов
SELECT SUM(salary) FROM Employees WHERE DepartmentID = @DepartmentID;

Подробнее об этом расскажет наш спикер на видео

Значимость столбца identity

Столбец identity необходим в любой переменной таблицы, подразумевающей итерации. Это первый шаг к решению проблемы, без него начать невозможно:

-- В нашей таблице @MyTable есть столбец identity
DECLARE @CurrentRow INT = 1, @TotalRows INT;
-- Подсчитываем общее количество строк, словно верифицируем кол-во пицц!
SELECT @TotalRows = COUNT(*) FROM @MyTable;

WHILE @CurrentRow <= @TotalRows
BEGIN
    -- Продолжаем, пока не обработаем все строки!
    SELECT @ID, @Name FROM @MyTable WHERE ID = @CurrentRow;
    SET @CurrentRow = @CurrentRow + 1;
END;

Подсчёт строк с использованием @@ROWCOUNT

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

-- Мониторим кол-во строк в SQL.
INSERT INTO @MyTable SELECT ID, Name FROM Employees;
SET @TotalRows = @@ROWCOUNT;

Использование ROW_NUMBER при отсутствии столбца identity

Если в таблице нет столбца identity, возпользуйтесь ROW_NUMBER() для элегантного решения проблемы:

-- Запасной план для случаев, когда отсутствует столбец identity.
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum, * INTO #TempTable FROM @MyTable;

Обратный отсчёт вместо прямого итерирования

Иногда наиболее эффективным оказывается подход с обратным отсчётом:

SET @RowsToProcess = @TotalRows;

WHILE @RowsToProcess > 0
BEGIN
    -- Обратный отсчёт строк.
    SELECT @ID, @Name FROM @MyTable WHERE ID = @RowsToProcess;
    SET @RowsToProcess = @RowsToProcess – 1;
END;

Делаем работу с SQL интереснее с помощью CONCAT

Сделайте свою работу с SQL более занимательной, применяя функцию CONCAT, чтобы обогатить вывод информации:

-- Как педометр, только для SQL. Отслеживание процесса делает его более весёлым!
PRINT CONCAT('Обрабатываю строку ', @CurrentRow, ' из ', @TotalRows);

Приход на реляционный подход

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

Ситуации использования и советы по улучшению навыков

Продуманная и безопасная итерация

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

Избавляемся от RBAR (Row-By-Agonizing-Row)

Если вы видите признаки RBAR, избегайте его, выбирая реляционный подход. Будьте креативны и научитесь обходить "SQL-мины" RBAR.

Открыться для критики и помощи сообщества

Если у вас не получается преобразовать цикл в реляционную операцию, обратитесь за советом к сообществу. Это как в многопользовательской игре — помощь со стороны ускоряет достижение цели.

Фокус на производительности

Производительность ключева в мире SQL. Независимо от выбранного подхода — будь то курсоры, циклы или реляционные операции — делайте его осознанным.

Находим место для итераций

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

Визуализация

Итерация по переменной таблице подобна поездке на американских горках. Каждая итерация — это станция (строка), на которой остановился поезд:

Переменная таблицы 🎢: | Станция 1 | Станция 2 | Станция 3 | Станция 4 | 🛤️

Оператор SELECT напоминает сбор пассажиров-данных на каждой станции:

WHILE @NextStation IS NOT NULL
BEGIN
    FETCH NEXT FROM StationCursor INTO @StationData; -- 🚉 Сбор данных на станции (строке)
    EXECUTE RideThrills @StationData; -- 🛠️ Добавление динамичности (Оптимизация работы!)
    SELECT @NextStation = (SELECT MIN(RowID) FROM @TableVariable WHERE RowID > @StationData);
END;

Каждый цикл WHILE делает остановку на станции (строке), а затем переходит к следующей.

Полезные материалы

1. Пример использования курсора в SQL Server
2. Основы синтаксиса общих табличных выражений (CTE) в SQL Server — Simple Talk
3. WHILE (Transact-SQL) – SQL Server | Microsoft Learn
4. postgresql – Как добавить строку, содержащую внешний ключ? – Database Administrators Stack Exchange