Циклы в sql запросах: пишем while-цикл прямо в запросе, или «Элементарная трехходовка» / Хабр

Содержание

WHILE (Transact-SQL) — SQL Server





Twitter




LinkedIn




Facebook




Адрес электронной почты










  • Статья



Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

-- Syntax for SQL Server and Azure SQL Database  
  
WHILE Boolean_expression   
     { sql_statement | statement_block | BREAK | CONTINUE }  
  

-- Syntax for Azure Azure Synapse Analytics and Parallel Data Warehouse  
  
WHILE Boolean_expression   
     { sql_statement | statement_block | BREAK }

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

Boolean_expression
Выражение, возвращающее значение TRUE или FALSE. Если логическое выражение содержит инструкцию SELECT, инструкция SELECT должна быть заключена в скобки.

{sql_statement | statement_block}
Любая инструкция или группа инструкций Transact-SQL, определенная в виде блока инструкций. Для определения блока инструкций используйте ключевые слова потока управления BEGIN и END.

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

CONTINUE
Выполняет цикл WHILE для перезагрузки, не учитывая все инструкции, следующие после ключевого слова CONTINUE.

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

Примеры

A. Использование ключевых слов BREAK и CONTINUE внутри вложенных конструкций IF…ELSE и WHILE

В следующем примере в случае, если средняя цена продуктов из списка меньше чем $300, цикл WHILE удваивает цены, а затем выбирает максимальную. В том случае, если максимальная цена меньше или равна $500, цикл WHILE повторяется и снова удваивает цены. Этот цикл продолжает удваивать цены до тех пор, пока максимальная цена не будет больше чем $500, затем выполнение цикла WHILE прекращается, о чем выводится соответствующее сообщение.

USE AdventureWorks2012;  
GO  
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300  
BEGIN  
   UPDATE Production.Product  
      SET ListPrice = ListPrice * 2  
   SELECT MAX(ListPrice) FROM Production.Product  
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500  
      BREAK  
   ELSE  
      CONTINUE  
END  
PRINT 'Too much for the market to bear';

Б. Применение инструкции WHILE в курсоре

В следующем примере используется переменная @@FETCH_STATUS для управления действиями курсора в цикле WHILE.

DECLARE @EmployeeID as NVARCHAR(256)
DECLARE @Title as NVARCHAR(50)
DECLARE Employee_Cursor CURSOR FOR  
SELECT LoginID, JobTitle   
FROM AdventureWorks2012.HumanResources.Employee  
WHERE JobTitle = 'Marketing Specialist';  
OPEN Employee_Cursor;  
FETCH NEXT FROM Employee_Cursor INTO @EmployeeID, @Title;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      Print '   ' + @EmployeeID + '      '+  @Title 
      FETCH NEXT FROM Employee_Cursor INTO @EmployeeID, @Title;  
   END;  
CLOSE Employee_Cursor;  
DEALLOCATE Employee_Cursor;  
GO

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

В.

Простой цикл While

В следующем примере в случае, если средняя цена продуктов из списка меньше чем $300, цикл WHILE удваивает цены, а затем выбирает максимальную. В том случае, если максимальная цена меньше или равна $500, цикл WHILE повторяется и снова удваивает цены. Этот цикл продолжает удваивать цены до тех пор, пока максимальная цена не будет больше, чем $500, после чего выполнение цикла WHILE прекращается.

-- Uses AdventureWorks  
  
WHILE ( SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300  
BEGIN  
    UPDATE dbo.DimProduct  
        SET ListPrice = ListPrice * 2;  
    SELECT MAX ( ListPrice) FROM dbo.DimProduct  
    IF ( SELECT MAX (ListPrice) FROM dbo.DimProduct) > $500  
        BREAK;  
END

См. также

ALTER TRIGGER (Transact-SQL)
Язык управления потоком (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
Курсоры (Transact-SQL)
SELECT (Transact-SQL)






Prime | Обучение программированию PL/SQL (ORACLE)

Урок 1.

Основы PL/SQL

Структура PL/SQL. Блок DECLARE, блок выполнения, блок EXCEPTION.

Объявление переменных, типы переменных. Оператор присвоения.

Получение данных из базы данных с помощью SELECT .. INTO. Ветвление программы, конструкции IF..THEN, IF..THEN..ELSE, IF..THEN..ELSIF.

Стандартный пакет DBMS_OUTPUT.

Урок 2. Отладка PL/SQL программы

Назначение отладки. Тестовые окна. Пошаговое выполнение программного кода с мониторингом состояния переменных. Поиск ошибок программного кода.

Вложенные программные блоки. Обработка исключительных ситуаций. Обработка NO_DATA_FOUND, TOO_MANY_ROWS, OTHERS. Метки и оператор перехода GOTO. Рекомендации по написанию программного кода и использованию меток.

Урок 3. Написание простых хранимых функций

Создание хранимых функций и их использование в PL/SQL блоках и SQL-запросах. Требования и ограничения на применение функций в SQL-запросах. Рекомендации к оформлению программного кода и именованию параметров и переменных.

Тестирование функций. Рекомендации по использованию команд управления транзакциями (COMMIT и ROLLBACK) в процедурах и функциях при разработке информационных систем. Использование системных пакетов. Стандартный пакет DBMS_RANDOM.

Урок 4. Написание более сложных хранимых функций

Способы передачи параметров в функции и процедуры. Типы параметров функций и процедур. Значения по умолчанию.

Урок 5. Написание простых хранимых процедур

Создание хранимых процедур и их использование. Команда MERGE.

Урок 6. Блок EXCEPTION

Работа с исключениями, определение типов исключений. Пользовательские исключения. RAISE_APPLICATION_ERROR. Подпрограммы.

Урок 7. Явные курсоры в PL/SQL

Назначение курсоров в ORACLE. Объявление явных курсоров. Открытие явных курсоров. Извлечение результата из явных курсоров. Закрытие явных курсоров.

Декларация курсоров. Организация циклов перебора строк данных через LOOP..END LOOP. Обработка FETCH. Создание инструкций EXIT. Атрибуты курсоров: %FOUND, %NOTFOUND и др.

Работа с заранее не объявленными явными курсорами. Цикл FOR .. IN, LOOP .. END LOOP.

Урок 8. Неявные курсоры в PL/SQL

Работа с неявными курсорами в PL/SQL программе. Применение SQL%ROWCOUNT.

Урок 9. Использование стандартных пакетов ORACLE. Пакет UTL_SMTP

Использование стандартных возможностей ORACLE. Пакет UTL_SMTP. Отправка электронных сообщений через SMTP.

Протоколы работы с почтовыми серверами SMTP, POP3, IMAP.

Кодировка данных. BASE64. Пакеты UTL_RAW, UTL_ENCODE.

Урок 10. Циклы в ORACLE

Понятие и назначение циклов. Типы циклов. Цикл FOR. Цикл WHILE. Команда EXIT и EXIT WHEN.

Урок 11. Создание триггеров

Назначение триггеров. Примеры использования триггеров. Контроль и логирование действий пользователей. Использование SYS_CONTEXT.

Урок 12. Транзакции в ORACLE. Автономные транзакции

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

Урок 13. Пакеты ORACLE

Понятие и назначение пакетов. Создание собственных пакетов. Общая структура пакетов. Обращение к элементам пакета. Глобальные данные пакета. Переменные пакетов. Создание общих пакетов. Обращение в SQL-запросах к функциям из состава пакетов.

Урок 14. Коллекции

Синтаксис объявления типов для коллекций. Ключевое слово TYPE. Виды коллекций: ассоциативные массивы, VARRAY, NESTED TABLE. Добавление элементов в коллекции. Методы для работы с коллекциями в программе. FIRST, NEXT, LAST, EXTEND, EXISTS, DELETE. Создание собственных типов коллекций. Ключевое слово RECORD.

Урок 15. Вложенные таблицы и табличные функции

Создание вложенных таблиц — NESTED TABLE. Заполнение и редактирование вложенных таблиц. Создание функций, возвращающих таблицы данных. PIPELINED (конвейерные) функции. Использование табличных функций. Ключевое слово TABLE.

Урок 16. Оптимизация работы с большим количеством данных при помощью коллекций.

BULK COLLECT. Зависимые типы данных в процедурах и функциях

Быстрое заполнение коллекций с помощью BULK COLLECT. Обработка большого количества данных. Блочная обработка данных BULK COLLECT.. LIMIT. Дополнительные примеры работы с коллекциями. Использование коллекций в качестве параметров хранимых функций и процедур. Использование %TYPE и %ROWTYPE. Компиляция инвалидных объектов.

Урок 17. Планировщик заданий в ORACLE — DBMS_SCHEDULER

Назначение и использование планировщика заданий в ORACLE. Создание регулярно повторяющихся задач. Запуск внешних программ. Запуск процедур по расписанию. Просмотр текущих запланированных заданий в расписании и их редактирование/удаление. Сравнение ORACLE SCHEDULER и ORACLE JOB.

Урок 18. Создание представлений (VIEW) и материализованных представлений MATERIALIZED VIEW

Представления и материализованные представления. Мгновенное и отложенное создание материализованных представлений. Автоматическое обновление материализованных представлений по расписанию. Создание материализованных представлений на основе таблиц. Ручное обновление материализованных представлений. Быстрое и полное обновление материализованных представлений. CREATE MATERIALIZED VIEW LOG.

Урок 19. Системные представления (VIEW), динамический SQL

Основные системные представления и их использование. Динамический SQL — EXECUTE IMMEDIATE.

Урок 20. Чтение и запись данных из внешних файлов. Пакет UTL_FILE

Использование стандартного пакета ORACLE — UTL_FILE для чтения внешних файлов. Экспорт данных во внешние файлы.

Урок 21. Работа с XML в ORACLE

XMLType. XMLSequence. XMLTable. Чтение XML структуры. Извлечение данных из XML файлов, представление данных XML как таблицы. SELECT из XML.

Can I use… CYCLE … (Recursive Queries) in SQL

Apache DerbyBigQueryDb2 (LUW)h3MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite200520072009201120132015201720192021⊘ 3.5.7 — 3.41.0⊘ 2008R2 — 2022✓ 14+⊘ 8.3 — 13⚠ 11gR2 — 21ca⊘ 11gR1⊘ 5.0 — 8.0. 32⚠ 10.5 — 10.11ab⊘ 5.1 — 10.4⊘ 1.4.191 — 2.1.214⊘ 9.7 — 11.5.8⊘ 2.0⊘ 10.15.1.3 — 10.16.1.1

    №

    5 с использованием подпункта 00709

    6 цикл … ограничение

Предложение цикла отслеживает выбранные столбцы результата рекурсивного запроса на наличие повторяющихся значений. Если одни и те же значения появляются во второй раз, 9Предложение 0005 цикла предотвращает повторное использование этого пути. Таким образом, предложение cycle предотвращает бесконечные циклы.

 С РЕКУРСИВНЫМ путем (a, b) AS (
  ВЫБЕРИТЕ ребра.* ИЗ ребер
                ГДЕ а = 1
СОЮЗ ВСЕХ
  ВЫБЕРИТЕ ребра.* ИЗ ребер
                 ПРИСОЕДИНЯЙТЕ путь
                   НА ребрах.a = path.b
)
  ЦИКЛ а
  УСТАНОВИТЕ цикл НА 'Y' ПО УМОЛЧАНИЮ 'N'
ИСПОЛЬЗОВАНИЕ path_array 
ВЫБИРАТЬ *
  ОТ пути
 ГДЕ  цикл = 'N'

Обратите внимание, что результат будет иметь одну строку для каждого цикла дважды. 9Столбец 0005 cyclo , определенный в наборе , может использоваться для удаления дубликатов.

Предложение цикла состоит из трех частей:

CYCLE <список столбцов цикла>

Список столбцов, для которых цикл — и только цикл — вызывает повторяющиеся значения.

SET <имя столбца> [TO <значение> DEFAULT <значение>]

Добавляет столбец для идентификации строк, закрывающих цикл.

Необязательные подпункты с по и по умолчанию определяют значения, которые помечают строки, обработанные до закрытия цикла (от до ), и те, которые закрывали цикл ( по умолчанию ). Если этот параметр пропущен, по умолчанию принимает значение false по умолчанию true .0

USING <имя столбца>

Добавляет столбец, содержащий массив со списком шагов, которые привели к каждой строке.

Значения в массиве являются строковыми значениями столбцов, перечисленных сразу после цикл ключевое слово.

Несмотря на то, что стандарт SQL строго требует, чтобы задавал и с использованием подпунктов , они концептуально не требуются, если определяемые ими столбцы не нужны в результате. Некоторые продукты вообще не поддерживают эти подпункты. Обратите внимание, что пропуск набора имеет логическое значение: повторяющиеся строки не могут быть отфильтрованы, как показано в предложении , где выше. Следовательно, пропуск набора может также означать, что не повторяются строки, закрывающие цикл.

  • Циклы разрыва, если все столбцы имеют повторное значение: с рекурсивным (… Союзом [различным]…)

  • Учебник: с — Организация комплексов

Нормативные СПИСОК Предложение

определено в ISO/IEC 9075:2016-2 как часть дополнительной функции T131 «Рекурсивный запрос».

Об авторе

Маркус Винанд — представитель SQL Renaissance. Его миссия — познакомить разработчиков с эволюцией SQL в 21 веке. Маркуса можно нанять в качестве тренера, спикера и консультанта через winand.at.

Сноски

  1. Подпункты с по и по умолчанию принимают только значения типов char(1) и boolean .

Обнаружение цикла для рекурсивного поиска в иерархических деревьях

Советы

Запрос данных

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

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

Использование

MySQL 

 С РЕКУРСИВНЫМ деревом КАК (
    ВЫБЕРИТЕ идентификатор, имя, 1 уровень AS, путь JSON_ARRAY (id) AS
    ОТ сотрудников
    ГДЕ manager_id = 1
  СОЮЗ
    ВЫБЕРИТЕ employee.id, employee.name, tree.level + 1, JSON_ARRAY_APPEND (tree.path, '$', employee.id)
    ИЗ дерева
    ПРИСОЕДИНЯЙТЕСЬ к сотрудникам (tree.id = employee.manager_id)
    ГДЕ НЕ employee.id ЧЛЕН(дерево.p)
)
ВЫБЕРИТЕ * ИЗ дерева

PostgreSQL 

 С РЕКУРСИВНЫМ деревом КАК (
    ВЫБЕРИТЕ идентификатор, имя, 1 уровень AS
    ОТ сотрудников
    ГДЕ manager_id = 1
  СОЮЗ
    ВЫБЕРИТЕ сотрудников. id, сотрудников.имя, дерево.уровень + 1
    ИЗ дерева
    ПРИСОЕДИНЯЙТЕСЬ к сотрудникам (tree.id = employee.manager_id)
) CYCLE id SET is_cycle USING Cycle_path
ВЫБЕРИТЕ * ИЗ дерева

Подробное объяснение

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

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

  1. Виртуальный стол
    дерево
    создается с использованием рекурсивного общего табличного выражения (CTE), которое накапливает результаты.
    Поскольку CTE является рекурсивным, запросы внутри CTE могут ссылаться сами на себя.
  2. Базовый запрос выберет первый уровень дерева в качестве отправной точки для рекурсивного запроса:
    ВЫБЕРИТЕ id, имя, 1 уровень AS ОТ сотрудников, ГДЕ manager_id = 1
  3. Рекурсивный запрос с использованием
    дерево
    CTE будет выполняться для поиска новых строк с другого уровня дерева, увеличивая значение уровня:
    ВЫБЕРИТЕ сотрудников.id, имена сотрудников, уровень дерева + 1 ИЗ дерева ПРИСОЕДИНЯЙТЕ сотрудников ВКЛ (дерево. ид = ИД_сотрудников_менеджера)

Список смежности — это простая модель, которую легко реализовать, но она имеет один серьезный недостаток:
В отношениях не должно быть петель!
В PostgreSQL такой запрос будет выполняться бесконечно и никогда не завершится.
Однако ограничение количества строк, считываемых из рекурсивного CTE, автоматически остановит его при достижении порогового значения.
Этот обходной путь не будет работать в MySQL, поскольку рекурсивное CTE сначала должно завершить выполнение, прежде чем данные будут прочитаны из него, и будет применен LIMIT.
Тем не менее, в MySQL любой рекурсивный запрос автоматически завершится с ошибкой после 1000 рекурсивных шагов.
Этот предел можно увеличить с помощью системной переменной cte_max_recursion_depth для поиска в глубоких деревьях.

Эти меры безопасности для ограничения времени выполнения запроса прекрасны, но оптимальным решением будет остановка рекурсии при обнаружении цикла.
Эта функция называется «обнаружение цикла» и является частью стандарта SQL.
Он поддерживается начиная с PostgreSQL 14 и требует лишь небольшого расширения CTE.

ЦИКЛ
Пункт гласит, что петли должны быть обнаружены на
идентификатор
столбец, автоматически накапливая путь дерева в
цикл_путь
` столбец и использовать его для расчета условия выхода
is_cycle
:

Идентификатор ЦИКЛА НАБОР is_cycle ИСПОЛЬЗОВАНИЕ Cycle_path
.
Рекурсия автоматически остановится для любого обнаруженного цикла.

В MySQL и более ранних версиях PostgreSQL автоматическое определение циклов недоступно.
К счастью, функция CYCLE — это просто синтаксический сахар для ручного подхода, который был доступен в течение многих лет:

  • Базовый запрос добавляет идентификатор начального элемента для каждой строки в массив JSON, содержащий элементы, добавленные в путь.
  • Рекурсивный запрос добавляет текущую строку к пути, чтобы обновить путь запроса.
  • Рекурсивный запрос продолжится только со строками, не содержащимися в пути запроса для текущего шага рекурсии.

Дополнительные ресурсы

  • Документация MySQL: Введение в рекурсивные CTE.
  • Документация PostgreSQL: Рекурсивные запросы и параметры их поведения.