Иерархия. START WITH, CONNECT BY

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

Самый простой пример иерархии - штатная структура "руководитель - подчиненный".
Для примеров будет использоваться представление на основе таблиц HR.EMPLOYEES и HR.JOBS.
В общем виде выглядит как:

employee_id	manager_id	job_title	name
100		President	King, Steven
101	100	Administration Vice President	Kochhar, Neena
102	100	Administration Vice President	De Haan, Lex
103	102	Programmer	Hunold, Alexander
104	103	Programmer	Ernst, Bruce
105	103	Programmer	Austin, David
106	103	Programmer	Pataballa, Valli
107	103	Programmer	Lorentz, Diana
108	101	Finance Manager	Greenberg, Nancy
109	108	Accountant	Faviet, Daniel
...

START WITH Указывает какой узел считать корневым. Например:
START WITH employee_id = 100 отобразит иерархию всей организации
START WITH employee_id = 101 отобразит только ветку от сотрудника с employee_id = 101
CONNECT BY Связывает вышестоящую запись с нижестоящей.

PRIOR Указывает "направление" построения иерархии. Например запрос:

SELECT * from employees 
START WITH employee_id = 101
CONNECT BY MANAGER_ID = PRIOR EMPLOYEE_ID

Результат:

employee_id	manager_id	job_title	name
101	100	Administration Vice President	Kochhar, Neena
108	101	Finance Manager	Greenberg, Nancy
109	108	Accountant	Faviet, Daniel
...

Запрос:

SELECT * from employees 
START WITH employee_id = 101
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID

Результат:

employee_id	manager_id	job_title	name
101	100	Administration Vice President	Kochhar, Neena
100		President	King, Steven

LEVEL Псевдополе показывающее уровень записи по отношению к корневому узлу:

SELECT LEVEL, EMPLOYEE_ID, MENEGER_IR, JOB_TITLE, NAME from employees 
START WITH employee_id = 101
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID

level	employee_id	manager_id	job_title	name
1	101	100	Administration Vice President	Kochhar, Neena
2	108	101	Finance Manager	Greenberg, Nancy
3	109	108	Accountant	Faviet, Daniel
3	110	108	Accountant	Chen, John
...

При обратной иерархии нижний узел (указанный в START WITH) будет первый, а все вышестоящие нумероваться относительно него.

Для удобного представления данных можно LEVEL использовать с LPAD.

SELECT LEVEL, LPAD('-', level*2, '-') || JOB_TITLE JOB_TITLE from employees
start with employee_id = 101
connect by MANAGER_ID =  prior EMPLOYEE_ID

level	job_title
1	--Administration Vice President
2	----Finance Manager
3	------Accountant
3	------Accountant
...

Функция SYS_CONNECT_BY_PATH отображает "путь" к должности (подобно вложенным каталогам в операционной системе). Например:

SELECT LEVEL, SYS_CONNECT_BY_PATH(JOB_TITLE, '\') JOB_TITLE, NAME from employees
start with employee_id = 101
connect by MANAGER_ID =  prior EMPLOYEE_ID

Результат:

level	job_title	name
1	\Administration Vice President	Kochhar, Neena
2	\Administration Vice President\Finance Manager	Greenberg, Nancy
3	\Administration Vice President\Finance Manager\Accountant	Faviet, Daniel
3	\Administration Vice President\Finance Manager\Accountant	Chen, John
...

Функция CONNECT_BY_ROOT выводит для каждой записи значение корневой записи:

SELECT CONNECT_BY_ROOT JOB_TITLE ROOT, JOB_TITLE, NAME from employees
start with employee_id = 101
connect by MANAGER_ID =  prior EMPLOYEE_ID

Результат:

root	job_title	name
Administration Vice President	Administration Vice President	Kochhar, Neena
Administration Vice President	Finance Manager	Greenberg, Nancy
Administration Vice President	Accountant	Faviet, Daniel
Administration Vice President	Accountant	Chen, John
...

Условие WHERE убирает только отдельные записи:

select employee_id, manager_id, job_title, name from employees
where employee_id <> 108
start with employee_id = 101
connect by MANAGER_ID =  prior EMPLOYEE_ID

В данном случае "ушла" только запись по Greenberg, Nancy (где employee_id = 108) но нижестоящие записи остались:

employee_id	manager_id	job_title	name
101	100	Administration Vice President	Kochhar, Neena
109	108	Accountant	Faviet, Daniel
110	108	Accountant	Chen, John
200	101	Administration Assistant	Whalen, Jennifer
...

Что бы убрать всю ветку необходимо условие задавать в CONNECT BY:

select employee_id, manager_id, job_title, name from employees
start with employee_id = 101
connect by MANAGER_ID =  prior EMPLOYEE_ID 
and employee_id <> 108

Ветка "фининсов" полностью скрыта:

employee_id	manager_id	job_title	name
101	100	Administration Vice President	Kochhar, Neena
200	101	Administration Assistant	Whalen, Jennifer
203	101	Human Resources Representative	Mavris, Susan
...

Псевдополе CONNECT_BY_ISLEAF. Принимает значения 0 или 1. Показывает есть ли у данного сотрудника подчиненные. Конечные узлы, т.е. узны у которых нет нижестоящих еще называют листами.

С циклами (петлями) мощно бороться при помощи NOCYCLE. Под циклом понимается что один из узлов указан как вышестоящий для узла который расположен выше его по иерархии.

Сортировка ORDER BY работает со всей результирующей таблицей. Например сортировка по имени полностью разрушает иерархию. Во избежание этого использовать ORDER SIBLINGS BY. Эта сортировка производится в пределах одного уровня иерархии, таким образом не нарушая ее.