Cấu Trúc Câu Lệnh Tìm Kiếm Theo Cấp Bậc
Trong Oracle, việc xử lý dữ liệu có quan hệ phân cấp (như cấu trúc tổ chức hoặc danh mục sản phẩm) thường sử dụng cú pháp đặc biệt. Câu lệnh này bao gồm ba phần chính hoạt động trên bảng nguồn:
- TOÀN BỘ DỮ LIỆU CẦN TRUY XUẤT: Phần chọn lọc dữ liệu thô trước khi áp dụng logic liên kết.
- MỐC KHỞI ĐẦU: Xác định nút gốc từ đó chuỗi liên kết bắt đầu được duyệt.
- MỐI QUAN HỆ LIÊN KẾT: Định nghĩa cách nối các dòng lại với nhau thông qua mệnh đề
CONNECT BY.
Bên cạnh đó, từ khóa PRIOR đóng vai trò then chốt để xác định chiều hướng so sánh giữa dòng hiện tại và dòng đã tham chiếu trước đó. Ví dụ, nếu dùng PRIOR id = parent_id, hệ thống sẽ tìm các bản ghi con dựa trên mã cha của dòng đã lấy. Ngược lại, id = PRIOR parent_id sẽ tìm kiếm dòng cha dựa trên mã của dòng con hiện tại.
Mô Hình Thực Hành Chi Tiết
Giả sử bảng dữ liệu company_dept lưu trữ thông tin phòng ban với các cột sau:
dept_id: Mã định danh duy nhất của phòng ban.parent_dept_id: Mã của phòng ban cấp cao hơn trực tiếp quản lý.is_active: Trạng thái kích hoạt (0 hoặc 1).
1. Truy xuất toàn bộ nhánh con bao gồm cả nút gốc
Sử dụng hàm LEVEL để xác định thứ bậc độ sâu của mỗi dòng trong tập dữ liệu kết quả.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
START WITH dept_id = 100
CONNECT BY PRIOR dept_id = parent_dept_id;Lệnh này trả về tất cả các phòng ban thuộc nhánh quản lý của phòng ID 100. Trình tự thực thi bắt đầu từ ID 100, sau đó lặp lại quá trình tìm các bản ghi có parent_dept_id khớp với giá trị vừa lấy được ở bước trước.
2. Thay đổi điểm khởi phát
Việc thay đổi điều kiện START WITH cho phép duyệt từ các vị trí khác trong cây mà không cần sửa lại logic liên kết.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
START WITH dept_id = 500
CONNECT BY PRIOR dept_id = parent_dept_id;Kết quả sẽ chỉ giới hạn trong phạm vi nhánh dưới nút gốc mới là ID 500, không hiển thị bất kỳ thông tin nào từ cha mẹ hay các nhánh khác.
3. Chỉ truy xuất các thành viên cấp dưới (loại trừ nút khởi đầu)
Để bỏ qua chính nút khai báo ban đầu, ta có thể thay đổi điều kiện tìm kiếm ban đầu sang việc tìm các dòng có parent_dept_id trùng khớp, hoặc lọc kết quả sau.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1 AND parent_dept_id = 100
START WITH parent_dept_id = 100
CONNECT BY PRIOR dept_id = parent_dept_id;Dù vẫn dùng logic PRIOR, nhưng việc gán điều kiện ban đầu cho parent_dept_id giúp nhấn mạnh vào việc thu thập các phần tử trực tiếp nằm trong danh sách con.
4. Hỗ trợ nhiều điểm bắt đầu đồng thời
Cú pháp cho phép kết hợp nhiều điều kiện trong phần khởi tạo để truy cập song song nhiều nhánh cây phức tạp.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
START WITH dept_id IN (100, 200, 999)
CONNECT BY PRIOR dept_id = parent_dept_id;Hệ thống sẽ tiến hành duyệt cây độc lập cho từng ID trong danh sách chứa và gộp kết quả cuối lại.
5. Duyệt ngược lên đường đi của tổ tiên
Ngược với các ví dụ trên, nếu muốn tìm ra lịch sử quản lý từ một nhân viên cụ thể đến người đứng đầu công ty, vị trí của PRIOR sẽ đảo ngược.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
START WITH dept_id = 1200
CONNECT BY dept_id = PRIOR parent_dept_id;Trong trường hợp này, mỗi lần lặp sẽ tìm dòng cha của dòng con hiện tại, xây dựng danh sách đi từ dưới lên trên cùng.
6. Sắp xếp kết quả theo thứ tự cấp bậc
Tùy chỉnh hiển thị danh sách trả về để dễ dàng đọc hiểu mối quan hệ phân cấp.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
START WITH dept_id = 100
CONNECT BY PRIOR dept_id = parent_dept_id
ORDER BY LEVEL;Câu lệnh đảm bảo các dòng ở cùng mức độ được nhóm lại và sắp xếp theo thứ tự tăng dần của số tầng (Level).
7. Lọc dữ liệu dựa trên độ sâu (Level)
Có thể áp dụng bộ lọc WHERE lên biến LEVEL để chỉ lấy những tầng cụ thể mong muốn.
SELECT dept_id, LEVEL
FROM company_dept
WHERE is_active = 1
AND LEVEL <> 2
START WITH dept_id = 100
CONNECT BY PRIOR dept_id = parent_dept_id;Lệnh trên loại bỏ tất cả các kết quả nằm chính xác ở tầng thứ 2, giữ lại các tầng còn lại của nhánh cây tương ứng với điều kiện truy vấn.