Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

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

Для этого используется древовидная иерархическая система, такая, где есть идентификатор и есть идентификатор родителя (родительского узла) ID, PARENTED. Для работы с подобной иерархической структурой также используется конструкция SQL — CONNECT BY.

Оператор CONNECT BY используется для запросов, где требуется работа со сложной древовидной иерархией.

Например, уровни подчинения сотрудников компании или сложная структура отделов на предприятии.

Здесь ID — идентификатор сотрудника, NAME — наименование сотрудника, IDPARENT — идентификатор руководителя данного сотрудника.

Пример позволяет показать иерархию.

В данном примере генерируется количество символов подчеркивания, соответствующее уровню иерархии LEVEL.

Рисунок 150. Использование CONNECT BY START WITH PRIOR

В данном способе воспроизведения иерархии с использованием запросов SQL CONNECT BY не работает классическая сортировка ORDER BY, попытка сортировки запроса с помощью ORDER BY ломает иерархию, поэтому для сортировки подобных запросов используется специальная директива ORDER SIBLINGS BY.

Есть возможность представить иерархию в виде пути от каталога к каталогу. Для этого необходимо воспользоваться функцией SYS_CONNECT_BY_PATH (NAME, '/»).

Рисунок 151. CONNECT BY: пример

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

Избежать подобной проблемы позволяет использование специальной директивы NOCYCLE.

Переделаем запрос из примера с использованием данной директивы.

Такая конструкция для вывода иерархических данных существует только в диалекте ORACLE SQL?

Нет, в MS SQL и PostgreSQL также существуют подобные операторы. Это оператор WITH RECURSIVE.

Для MySQL такой конструкции не существует.

Как начать обход дерева не с первого, а со второго, третьего элемента?

Для этого в директиве START WIDTH следует указать идентификатор того элемента, с которого начинается обход древовидной структуры.

Например,

Здесь START WITH IDPARENT =1, обход начинается с первого элемента.

Рисунок 152. CONNECT BY: пример SYS_CONNECT_BY_PATH

1. Повторите материалы данного шага.

2. Создайте таблицу «Детали», где одна деталь может быть частью другой, выведите на экран иерархию деталей.

3. Создайте циклическую иерархию, запустите запрос с использованием NOCYRCLE и без использования NOCYRCLE, проанализируйте результат.

В языке SQL есть специальные команды для условных выражений в запросах, то есть выражений, на критерии истинности которых осуществляется ветвление запроса. Аналогичные конструкции есть в алгоритмических языках, таких, например, как JAVA, PASCAL или C++.

В алгоритмических языках это операторы IF и CASE.

В языке SQL похожая функциональность реализуется с помощью операторов DECODE, CASE.

Условные выражения в SQL ORACLE диалекта реализуются с помощью операторов CASE и DECODE.

Разберем каждый из условных операторов отдельно с примерами.

DeCODEвыводит значение в зависимости от логического выражения.

равно 2.

Пример 2

Рисунок 153. Запрос: использование DECODE