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

Что такое иерархический запрос?
База данных Demo
Иерархический запрос
Виртуальные столбцы использованные в иерархическом запросе

1. Что такое иерархический запрос?

Иерархический запрос (hierarchical query) это вид запроса SQL который используется для обрабатывания иерархических данных. Являются отдельными случаями при рекурсивных запросах fixpoint (recursive fixpoint queries).

2. База данных Demo

В данной статье я использую Schema модель SCOTT для иллюстрации примера. Если у вас нет schema вы можете ее создать по следующему Script:

** Create Table **

-- Create table
create table EMP
(
 EMPNO    NUMBER(4) not null,
 ENAME    VARCHAR2(10),
 JOB      VARCHAR2(9),
 MGR      NUMBER(4),
 HIREDATE DATE,
 SAL      NUMBER(7,2),
 COMM     NUMBER(7,2),
 DEPTNO   NUMBER(2)
);
alter table EMP
 add constraint EMP_PK primary key (EMPNO);

alter table EMP
 add constraint EMP_E_FK foreign key (MGR)
 references EMP (EMPNO);

** Insert Data **

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);

commit;

В таблице EMP:

EMPNO это ID работника (И первичный ключ таблицы)
MRG это ID прямого менеджера этого работника.

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

Синтаксис иерархического запроса:

** Syntax **

Select Column1, Column2, ...

From <Table1>, <Table2>, ...

Where <Condition3>

Connect By <Codition2>

Start With <Condition1>

Правила работы

Сначала, команда получит все строки в таблице, соответствующие условиям в пункте start with (condition1), как корень дерева(корень или уровень 1).
После, для каждого корня требуется скан всей таблицы, чтобы получить следующие записи соответствующие условиям в пункте connect by (условие2) (node уровня 2), для каждого node уровня 2 сканируется все таблица, чтобы получить следующие записи соответствующие условиям в пункте connect by (node уровня 3), и так продолжается до тех пор, пока не останется больше записей соответстующих условиям connect by то предыдущий node будет уровнем листа в дереве.
Под конец, проверяем условия пункта where (уровень3), чтобы получить записи пункта "select tree".

Например, иерархическое дерево начинается с тех, у кого нет менеджера (Mrg is null).

Select Level
     ,Emp.Empno
     ,Emp.Ename
     
     , -- Manager no
      Emp.Mgr Manager_No
     
     , -- Name of manager
     -- Prior: The Operator point to prior record.
      Prior Emp.Ename Manager_Name
     
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;

Результаты запроса:

Модель дерева:

Убрать Node или ветку

Основываясь на операционном механизме указанном выше, вы можете решить проблему: Как убрать любой 1 node или убрать всю ветку дерева.

Убрать NODE:

Убрать любой 1 node: вам нужно подождать когда завершится создание дерева(connect by завершено) и дать условия в пункте where, чтобы убрать node.

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Where  Emp.Ename != 'JONES'
Connect By Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null;

Убрать одну ветку:

Чтобы убрать любую ветку: Вам нужно дать условие в процесс создания дерева, то есть включить в пункт connect by.

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Manager_No
     ,Prior Emp.Ename Manager_Name
From   Emp
Connect By Prior Emp.Empno = Emp.Mgr
   And    Emp.Ename != 'JONES'
Start  With Emp.Mgr Is Null;

4. Виртуальные столбцы использованные в иерархическом запросе

Ключевые слова/Операторы	Значение
Level	Показывает уровень node (глубина), коренной node (root) c уровенем (level) 1
Prior	Оператор показывает предыдущую запись
Connect_By_Isleaf	Листовой node или нет? Если листовой Node возвращает 1, если нет возвращает 0
CONNECT_BY_ROOT(Cột)	Возвращает значение столбца коренного node (level = 1)
NOCYCLE	Оператор в connect by для того, чтобы убрать бесконечный цикл. Например А управляет В, управляет С, С управляет А. Тогда connect by определяет управляющего который повторяется бесконечно и оператор NOCYCLE будет полезен в данной ситуации.
CONNECT_BY_ISCYCLE	Столбец определяющий повторяется ли значение? Если да, возвращает 1 если нет, то 0. Этот столбец должен использоваться с оператором NOCYCLE в пункте connect by
SYS_CONNECT_BY_PATH(Cột, giá trị ngăn cách)	Созлать путь значения столбца от коренного node до настоящего node, каждый node отделяется "разделенными значениями".
ORDER SIBLINGS BY	Упорядочить дочерние node одинакового уровня (level)

Пример:

Select Level
     ,Emp.Empno Emp_No
     ,Lpad(' '
          ,4 * (Level - 1)) || Emp.Ename Emp_Name
     ,Emp.Mgr Mgr_No
     ,Prior Emp.Ename Mgr_Name
     ,Connect_By_Isleaf Is_Leaf -- 
     ,Connect_By_Root(Emp.Ename) Root_Mgr_Name -- Name of root employee
     ,Connect_By_Iscycle Iscycle -
     ,Sys_Connect_By_Path(Emp.Empno
                         ,':') Path -- The path
From   Emp
Connect By Nocycle Prior Emp.Empno = Emp.Mgr
Start  With Emp.Mgr Is Null
Order  Siblings By Emp.Ename;