Truy vấn phân cấp trong Oracle
Xem thêm các chuyên mục:

Là một website được viết trên công nghệ web Flutter vì vậy hỗ trợ rất tốt cho người học, kể cả những người học khó tính nhất.
Hiện tại website đang tiếp tục được cập nhập nội dung cho phong phú và đầy đủ hơn. Mong các bạn nghé thăm và ủng hộ website mới của chúng tôi.


Một truy vấn có thứ bậc (hierarchical query) là một loại truy vấn SQL để xử lý dữ liệu mô hình phân cấp. Nó là những trường hợp đặc biệt của các truy vấn đệ quy điểm cố định (recursive fixpoint queries).

Trong tài liệu này tôi sử dụng Schema mẫu SCOTT để minh họa cho các ví dụ. Nếu bạn không có schema này bạn có thể tự tạo nó theo Script dưới đây:
** 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;

Trong bảng EMP:
- EMPNO là ID của nhân viên (Và là khóa chính của bảng)
- MRG là ID của người quản lý trực tiếp nhân viên này.
Cú pháp của truy vấn phân cấp:
** Syntax **
Select Column1, Column2, ...
From <Table1>, <Table2>, ...
Where <Condition3>
Connect By <Codition2>
Start With <Condition1>
- Đầu tiên câu lệnh sẽ lấy ra tất cả các dòng trong bảng thỏa mãn điều kiện trong mệnh đề start with (condition1) làm gốc của cây (root hay mức 1).
- Tiếp theo với mỗi gốc của cây sẽ quét lại toàn bộ bảng để lấy ra các bản ghi tiếp theo thỏa mãn điều kiện trong mệnh đề connect by (condition2) (node mức 2), với mỗi node mức 2 sẽ quét lại toàn bộ bảng để lấy ra các bản ghi tiếp cho mức tiếp theo thỏa mãn điều kiện trong mệnh đề connect by (node mức 3), cứ tiếp tục như vậy cho đến khi không còn bản ghi nào thỏa mãn điều kiện connect by thì node trước đó là mức lá của cây.
- Cuối cùng mới kiểm tra điều kiện của mệnh đề where (condition3) để lấy ra được các bản ghi của mệnh đề "select tree".
Ví dụ cây phân cấp quản lý bắt đầu từ những người không có người quản lý (Mrg is null).
Select Level
,Emp.Empno
,Emp.Ename
, -- Mã người quản lý
Emp.Mgr Manager_No
, -- Tên người quản lý
-- Prior: Toán tử trỏ đến bản ghi mức trên.
Prior Emp.Ename Manager_Name
From Emp
Connect By Prior Emp.Empno = Emp.Mgr
Start With Emp.Mgr Is Null;
Kết quả truy vấn câu lệnh trên:

Mô hình cây:

Dựa vào cơ chế hoạt động như đã nói ở bước trên, bạn có thể giải quyết được vấn đề: Làm thế nào để loại bỏ 1 node bất kỳ hay loại bỏ cả 1 nhánh của cây.
Loại bỏ một NODE:
Loại bỏ 1 node bất kỳ: cần phải để cây tạo xong (connect by hoàn thành) và đưa điều kiện lọc vào mệnh đề where để loại bỏ 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;

Loại bỏ một nhánh:
Để loại bỏ 1 nhánh bất kỳ: Bạn cần phải đưa điều kiện vào trong quá trình tạo cây, tức là đưa vào mệnh đề 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;

Từ khóa/Toán tử | Ý nghĩa |
Level | Thể hiện node cấp mấy (độ sâu), node gốc (root) có level là 1 |
Prior | Toán tử thể hiện bản ghi trước |
Connect_By_Isleaf | Là node lá hay không? Node lá trả về 1, ngược lại trả về 0 |
CONNECT_BY_ROOT(Cột) | Trả về giá trị cột của node gốc (level = 1) |
NOCYCLE | Toán tử trong mệnh đề connect by để loại bỏ vòng lặp vô tận. Ví dụ A quản lý B, B quản lý C, C quản lý A. Khi đó connect by xác định người quản lý sẽ bị lặp vô tận và toán tử NOCYCLE sẽ có ích trong trường hợp này |
CONNECT_BY_ISCYCLE | Cột xác định giá trị này có lặp hay không? Có lặp trả về 1 và ngược lại trả về 0. Cột này phải được sử dụng cùng toán tử NOCYCLE trong mệnh đề connect by |
SYS_CONNECT_BY_PATH(Cột, giá trị ngăn cách) | Tạo ra đường dẫn giá trị cột từ node gốc tới node hiện thời, mỗi node cách nhau bởi “giá trị ngăn cách”. |
ORDER SIBLINGS BY | Sắp xếp giá các node con trong cùng 1 cấp (level) |
Ví dụ:
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 -- Là lá?
,Connect_By_Root(Emp.Ename) Root_Mgr_Name -- Người quản lý gốc
,Connect_By_Iscycle Iscycle -- Lặp?
,Sys_Connect_By_Path(Emp.Empno
,':') Path -- Đường dẫn
From Emp
Connect By Nocycle Prior Emp.Empno = Emp.Mgr
Start With Emp.Mgr Is Null
Order Siblings By Emp.Ename;
