openplanning

Truy vấn phân cấp trong Oracle

  1. Truy vấn phân cấp là gì?
  2. Cơ sở dữ liệu Demo
  3. Truy vấn phân cấp
  4. Các cột ảo sử dụng trong truy vấn phân cấp

1. Truy vấn phân cấp là gì?

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).

2. Cơ sở dữ liệu Demo

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.

3. Truy vấn phân cấp

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>
Nguyên tắc hoạt động
  • Đầ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:
Loại bỏ một Node hoặc một nhá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;

4. Các cột ảo sử dụng trong truy vấn phân cấp

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;

Các hướng dẫn về cơ sở dữ liệu Oracle

Show More