Иерархические запросы в Oracle

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

В Oracle Database для этого используется START WITH ... CONNECT BY.

Для начала создадим таблицу с иерархическими данными. Пусть у нас есть таблица EMPLOYEES с тремя колонками:

  1. EMPLOYEE_ID 
  2. LAST_NAME 
  3. MANAGER_ID 

Колонка MANAGER_ID ссылается на ту же самую таблицу EMPLOYEES. Вот скрипт её создания для Oracle:

Заполним её данными в соответствии со следующим деревом:

Иерархия сотрудников
Иерархия сотрудников. Стрелки означают связь по полю MANAGER_ID.

SQL для заполнения этими данными:

Теперь вы можете выполнить запрос:

И получить список всех записей. С этим проблем нет.

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

Но как получить список всех сотрудников, которые находятся в подчинении Сидорова прямо или косвенно, то есть как получить часть ветки ниже Сидорова? Для этого нужно использовать START WITH... CONNECT BY:

В результате получим:

EMPLOYEE_ID LAST_NAME MANAGER_ID
2 Сидоров 1
4 Васильев 2
5 Свиноухов 2
6 Литвина 5
7 Толстой 5

В этом запросе с помощью START WITH мы задаём корень (начало) иерархии. С помощью CONNECT BY указываем, каким образом записи в иерархии связываются друг с другом в таблице и какие из них отобрать для результата. В CONNECT BY может быть несколько условий, но одно из них должно быть помечено PRIOR, чтобы указать, что именно оно указывает на родительский узел. С помощью ORDER SIBLINGS BY мы сортируем дочерние узлы каждого узла по отдельности (не весь результат!).

С помощью псевдоколонки LEVEL можно указывать уровень иерархии для каждой записи:

Результат:

EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
2 Сидоров 1 1
4 Васильев 2 2
5 Свиноухов 2 2
6 Литвина 5 3
7 Толстой 5 3

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

Теперь предыдущие запросы будут возвращать ошибки:

Параметр NOCYCLE у условия CONNECT BY заставляет Oracle возвращать записи вместо цикла. Псевдоколонка CONNECT_BY_ISCYCLE показывает, какие строки содержат циклы:

Результат:

ID Employee Manager_id LEVEL Cycle
2 Сидоров 1 1 0
4 Васильев 2 2 0
1 Петров 4 3 1
3 Семёнова 1 4 0
9 Иванов 3 5 0
8 Мартышкин 3 5 0
10 Пушкин 3 5 0
5 Свиноухов 2 2 0
6 Литвина 5 3 0
7 Толстой 5 3 0

Есть также возможность отображать поля родительской записи и путь иерархии у записи:

Результат:

ID Employee Manager_id LEVEL Cycle Manager Pathlen Path
2 Сидоров 1 1 0 Сидоров 0 /Сидоров
4 Васильев 2 2 0 Сидоров 1 /Сидоров/Васильев
1 Петров 4 3 1 Сидоров 2 /Сидоров/Васильев/Петров
3 Семёнова 1 4 0 Сидоров 3 /Сидоров/Васильев/Петров/Семёнова
9 Иванов 3 5 0 Сидоров 4 /Сидоров/Васильев/Петров/Семёнова/Иванов
8 Мартышкин 3 5 0 Сидоров 4 /Сидоров/Васильев/Петров/Семёнова/Мартышкин
10 Пушкин 3 5 0 Сидоров 4 /Сидоров/Васильев/Петров/Семёнова/Пушкин
5 Свиноухов 2 2 0 Сидоров 1 /Сидоров/Свиноухов
6 Литвина 5 3 0 Сидоров 2 /Сидоров/Свиноухов/Литвина
7 Толстой 5 3 0 Сидоров 2 /Сидоров/Свиноухов/Толстой

Вот такой вот полезный START WITH... CONNECT BY.



Один комментарий к “Иерархические запросы в Oracle”

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *