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

С помощью директивы 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.

Задача. Соединение иерархической таблицы с обычной.

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

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

create table kk_employee (id integer, fio varchar2(50), boss_id integer, wage integer);

insert into kk_employee (id, fio, boss_id, wage) values (1, 'Антон Юрьевич', null, 300);

insert into kk_employee (id, fio, boss_id, wage) values (2, 'Дмитрий Анатольевич', 1, 200);

insert into kk_employee (id, fio, boss_id, wage) values (3, 'Михаил Иванович', 1, 190);

insert into kk_employee (id, fio, boss_id, wage) values (4, 'Андрей Иванович', 2, 100);

insert into kk_employee (id, fio, boss_id, wage) values (5, 'Борис Кимович', 2, 60);

insert into kk_employee (id, fio, boss_id, wage) values (6, 'Мария Владимировна', 3, 125);

insert into kk_employee (id, fio, boss_id, wage) values (7, 'Антон Алексеевич', 3, 80);

insert into kk_employee (id, fio, boss_id, wage) values (8, 'Михаил Юрьевич', 3, 140);

insert into kk_employee (id, fio, boss_id, wage) values (9, 'Рашитзай Марданович', 3, 105);

insert into kk_employee (id, fio, boss_id, wage) values (10, 'кошка Мэгги', 5, 20);

insert into kk_employee (id, fio, boss_id, wage) values (11, 'черепашка Ниндзя', 4, 15);

insert into kk_employee (id, fio, boss_id, wage) values (12, 'кот Мурзик', 7, 10);

insert into kk_employee (id, fio, boss_id, wage) values (13, 'мышь Пухлик', 12, 3);

create table kk_hobby (id integer, hobby varchar2(50));

insert into kk_hobby (id, hobby) values (1, 'клеить самолетики');

insert into kk_hobby (id, hobby) values (2, 'лепить из пластилина');

insert into kk_hobby (id, hobby) values (3, 'рисовать карикатуры');

insert into kk_hobby (id, hobby) values (4, 'собирать марки');

insert into kk_hobby (id, hobby) values (5, 'выращивать орхидеи');

insert into kk_hobby (id, hobby) values (6, 'разводить щенков');

insert into kk_hobby (id, hobby) values (7, 'писать мемуары');

insert into kk_hobby (id, hobby) values (8, null);

insert into kk_hobby (id, hobby) values (9, null);

Можно написать итоговый запрос, выделив раскручивание иерархии в отдельный подзапрос:

with a as

(select id, lpad(chr(9), level - 1, chr(9)) || fio as name

from kk_employee

connect by prior id = boss_id

start with boss_id is null)

select a.name, h.hobby from a left join kk_hobby h on a.id = h.id;

А можно и соединить таблицы, раскручивая иерархию после соединения:

select lpad(chr(9), level - 1, chr(9)) || u.fio as name, h.hobby

from kk_employee u

left join kk_hobby h

on u.id = h.id

connect by prior u.id = u.boss_id

start with u.boss_id is null;