Иерархические запросы в Oracle
1. Что такое иерархический запрос?
Иерархический запрос (hierarchical query) это вид запроса SQL который используется для обрабатывания иерархических данных. Являются отдельными случаями при рекурсивных запросах fixpoint (recursive fixpoint queries).
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078047-vi.webp)
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;
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078063-vi.webp)
В таблице 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;
Результаты запроса:
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078115-vi.webp)
Модель дерева:
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078121-vi.webp)
Убрать 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;
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078143-vi.webp)
Убрать одну ветку:
Чтобы убрать любую ветку: Вам нужно дать условие в процесс создания дерева, то есть включить в пункт 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;
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078155-vi.webp)
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;
![](https://s1.o7planning.com/web-rs/web-image/ru/arf-1078270-vi.webp)
Руководства База данных Oracle
- Установите PL/SQL Developer в Windows
- Пример базы данных Oracle для обучения SQL
- Установите Oracle Database 11g в Windows
- Установите Oracle Database 12c в Windows
- Установить Oracle Client в Windows
- Создать Oracle SCOTT Schema
- Пример базы данных
- Структура базы данных и облачные функции в Oracle 12c
- Импорт и экспорт базы данных Oracle
- Строковые функции Oracle
- Разделить строку запятой и передать в пункт IN команды Select в Oracle
- Иерархические запросы в Oracle
- Руководство Oracle Database Link и Synonym
- Руководство Oracle PL/SQL
- XML-парсер для Oracle PL/SQL
- Стандартный аудит базы данных в Oracle
- Создание и управление Oracle Wallet
Show More