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

С помощью директивы connect by таблица соединяется сама с собой несколько раз по заданному условию, пока это возможно.

Подробности мы разберем в задачах ниже.

Задача. Вывод иерархии с отступами.

Создать таблицу иерархии государственных служащих KK_MKB(ID - код служащего, NAME - должность служащего, BOSS_ID - код непосредственного начальника).

Написать запрос, выдающий список сотрудников как оглавление книги (босс, а под ним его сотрудники).

Перед сотрудником следует поставить столько пробелов, сколько уровней ему осталось до вершины иерархии. Например,

Президент

Премьер-министр

Вице-премьер деловой

Министр сельского хозяйства

Министр финансов

Министр экономического развития

Вице-премьер силовой

Министр внутренних дел

Министр обороны

Генерал

Капитан

Спикер

Вице-спикер1

Вице-спикер2

Создадим таблицу и заполним ее данными:

CREATE TABLE KK_MKB(ID INTEGER, NAME VARCHAR2(50), BOSS_ID INTEGER);

insert into KK_MKB(ID, NAME, BOSS_ID) values (1,'Президент',null);

insert into KK_MKB(ID, NAME, BOSS_ID) values (2,'Премьер-министр',1);

insert into KK_MKB(ID, NAME, BOSS_ID) values (3,'Спикер',1);

insert into KK_MKB(ID, NAME, BOSS_ID) values (4,'Вице-премьер деловой',2);

insert into KK_MKB(ID, NAME, BOSS_ID) values (5,'Вице-премьер силовой',2);

insert into KK_MKB(ID, NAME, BOSS_ID) values (6,'Вице-спикер1',3);

insert into KK_MKB(ID, NAME, BOSS_ID) values (7,'Вице-спикер2',3);

insert into KK_MKB(ID, NAME, BOSS_ID) values (8,'Министр сельского хозяйства',4);

insert into KK_MKB(ID, NAME, BOSS_ID) values (9,'Министр финансов',4);

insert into KK_MKB(ID, NAME, BOSS_ID) values (10,'Министр экономического развития',4);

insert into KK_MKB(ID, NAME, BOSS_ID) values (11,'Министр внутренних дел',5);

insert into KK_MKB(ID, NAME, BOSS_ID) values (12,'Министр обороны',5);

insert into KK_MKB(ID, NAME, BOSS_ID) values (13,'Генерал',12);

insert into KK_MKB(ID, NAME, BOSS_ID) values (14,'Капитан',13);

Напишем иерархический запрос, решающий задачу:

select id, name, boss_id, level, substr(lpad(' ', level) || name, 2)

from KK_MKB

connect by prior id = boss_id

start with boss_id is null;

Внесем зацикливание в данные, нарушив структуру дерева. Подчиним Премьер-министра еще и капитану:

insert into KK_MKB(ID, NAME, BOSS_ID) values (2,'Премьер-министр',14);

Теперь для "раскручивания дерева" добавим опцию nocycle, иначе запрос выругается:

select id, name, boss_id, level, substr(lpad(' ', level) || name, 2)

from KK_MKB

connect by nocycle prior id = boss_id

start with boss_id is null;

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

А с использованием конструкции "and prior dbms_random.value is not null" можно крутить иерархию бесконечно:

select id, name, boss_id, level, substr(lpad(' ', level) || name, 2)

from KK_MKB

connect by prior id = boss_id

and prior dbms_random.value is not null

start with boss_id is null;