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

View more Tutorials:

Руководства База данных Oracle

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

Сайт бесплатного изучения языков:

Следуйте за нами на нашей фан-странице, чтобы получать уведомления каждый раз, когда появляются новые статьи.

Facebook

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>

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

Сначала, команда получит все строки в таблице, соответствующие условиям в пункте 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;

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

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

3.2- Убрать 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;

View more Tutorials:

Руководства База данных Oracle