Данные в реляционных базах данных иногда могут представлять собой иерархическую структуру. В этом случае одно поле таблицы является ссылкой на другую (родительскую) запись в той же самой таблице.
В Oracle Database для этого используется START WITH ... CONNECT BY.
Для начала создадим таблицу с иерархическими данными. Пусть у нас есть таблица EMPLOYEES с тремя колонками:
- EMPLOYEE_ID
- LAST_NAME
- MANAGER_ID
Колонка MANAGER_ID ссылается на ту же самую таблицу EMPLOYEES. Вот скрипт её создания для Oracle:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE "EMPLOYEES" ( "EMPLOYEE_ID" NUMBER NOT NULL ENABLE, "LAST_NAME" NVARCHAR2(100) NOT NULL ENABLE, "MANAGER_ID" NUMBER, CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE ) ;ALTER TABLE "EMPLOYEES" ADD CONSTRAINT "EMPLOYEES_CON" FOREIGN KEY ("MANAGER_ID") REFERENCES "EMPLOYEES" ("EMPLOYEE_ID") ENABLE; CREATE OR REPLACE TRIGGER "BI_EMPLOYEES" before insert on "EMPLOYEES" for each row begin if :NEW."EMPLOYEE_ID" is null then select "EMPLOYEES_SEQ".nextval into :NEW."EMPLOYEE_ID" from dual; end if; end; / ALTER TRIGGER "BI_EMPLOYEES" ENABLE; |
Заполним её данными в соответствии со следующим деревом:
SQL для заполнения этими данными:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
BEGIN INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(1, 'Петров', NULL); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(2, 'Сидоров', 1); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(3, 'Семёнова', 1); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(4, 'Васильев', 2); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(5, 'Свиноухов', 2); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(6, 'Литвина', 5); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(7, 'Толстой', 5); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(8, 'Мартышкин', 3); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(9, 'Иванов', 3); INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, MANAGER_ID) VALUES(10, 'Пушкин', 3); END; / |
Теперь вы можете выполнить запрос:
|
1 |
SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID; |
И получить список всех записей. С этим проблем нет.
Однако нам может потребоваться как-то учитывать иерархию при обработке данных с этой таблицей. С одним уровнем иерархии проблем нет. Например, следующий скрипт покажет всех сотрудников, напрямую подчиняющихся Сидорову:
|
1 |
SELECT * FROM EMPLOYEES WHERE MANAGER_ID = 2; |
Но как получить список всех сотрудников, которые находятся в подчинении Сидорова прямо или косвенно, то есть как получить часть ветки ниже Сидорова? Для этого нужно использовать START WITH... CONNECT BY:
|
1 2 3 4 5 |
SELECT * FROM EMPLOYEES START WITH EMPLOYEE_ID = 2 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID ORDER SIBLINGS BY LAST_NAME; |
В результате получим:
| 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 можно указывать уровень иерархии для каждой записи:
|
1 2 3 4 5 |
SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, LEVEL FROM EMPLOYEES START WITH EMPLOYEE_ID = 2 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID ORDER SIBLINGS BY LAST_NAME; |
Результат:
| EMPLOYEE_ID | LAST_NAME | MANAGER_ID | LEVEL |
|---|---|---|---|
| 2 | Сидоров | 1 | 1 |
| 4 | Васильев | 2 | 2 |
| 5 | Свиноухов | 2 | 2 |
| 6 | Литвина | 5 | 3 |
| 7 | Толстой | 5 | 3 |
Иерархические данные иногда могут содержать циклические зависимости. Например, для нашего случая добавим, что Петров, который является самым главным начальником, подчиняется Васильеву:
|
1 |
UPDATE EMPLOYEES SET MANAGER_ID = 4 WHERE EMPLOYEE_ID = 1; |
Теперь предыдущие запросы будут возвращать ошибки:
|
1 |
ORA-01436: CONNECT BY loop in user data |
Параметр NOCYCLE у условия CONNECT BY заставляет Oracle возвращать записи вместо цикла. Псевдоколонка CONNECT_BY_ISCYCLE показывает, какие строки содержат циклы:
|
1 2 3 4 5 |
SELECT EMPLOYEE_ID "ID", LAST_NAME "Employee", MANAGER_ID "Manager_id", LEVEL, CONNECT_BY_ISCYCLE "Cycle" FROM EMPLOYEES START WITH EMPLOYEE_ID = 2 CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID ORDER SIBLINGS BY LAST_NAME; |
Результат:
| 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 |
Есть также возможность отображать поля родительской записи и путь иерархии у записи:
|
1 2 3 4 5 6 7 |
SELECT EMPLOYEE_ID "ID", LAST_NAME "Employee", MANAGER_ID "Manager_id", LEVEL, CONNECT_BY_ISCYCLE "Cycle", CONNECT_BY_ROOT last_name "Manager", LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM EMPLOYEES START WITH EMPLOYEE_ID = 2 CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID ORDER SIBLINGS BY LAST_NAME; |
Результат:
| 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.
Спасибо, за хорошую, полезную статью.