Oracle 3 — SQL đặc trưng: analytic, CONNECT BY, MERGE

Oracle không chỉ là một RDBMS thương mại lâu đời; nó còn là cái nôi của nhiều mở rộng SQL mà về sau trở thành chuẩn hoặc được các hệ khác mô phỏng lại. Nếu bạn đến với Oracle từ nền tảng PostgreSQL hay MySQL, bạn sẽ gặp một phương ngữ SQL vừa quen vừa lạ: quen ở phần SELECT/JOIN/GROUP BY cơ bản, lạ ở những công cụ rất riêng như CONNECT BY, MERGE, DECODE, ROWNUM, và bảng ảo DUAL. Bài này đi sâu vào các tính năng SQL đặc trưng đó — thứ khiến một truy vấn Oracle nhìn khác hẳn khi in ra.

Lưu ý quan trọng về sandbox. SQL Builder trong hệ thống này chạy trên PostgreSQL. Toàn bộ cú pháp đặc trưng Oracle dưới đây (DUAL, CONNECT BY, ROWNUM, MERGE, DECODE, (+) outer join, NVL...) KHÔNG chạy được trong sandbox. Mọi ví dụ đều được đánh dấu (minh hoạ — Oracle, không chạy trong sandbox Postgres) và bạn nên chạy chúng trên một instance Oracle thật (hoặc Oracle Live SQL) để kiểm chứng.

Schema mẫu dùng xuyên suốt: employees(employee_id, name, manager_id, department_id, salary, hire_date), departments(department_id, name, parent_id), accounts(account_id, customer_id, balance), stg_accounts(account_id, customer_id, balance) — bảng staging cho ETL.

Analytic / window functions — nơi Oracle đi tiên phong

Hàm phân tích (analytic function, hay window function theo cách gọi ANSI) cho phép tính toán trên một "cửa sổ" các dòng liên quan mà không gộp các dòng lại như GROUP BY. Oracle giới thiệu chúng từ 8i (năm 1999), sớm hơn nhiều hệ khác, và đến nay bộ hàm của Oracle vẫn thuộc loại đầy đủ nhất. Cú pháp cốt lõi là mệnh đề OVER(...):

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
<function> OVER (
    [PARTITION BY cot_chia_nhom]
    [ORDER BY cot_sap_xep]
    [window_frame]
)

PARTITION BY chia dữ liệu thành các nhóm (giống GROUP BY nhưng không làm co dòng); ORDER BY xác định thứ tự trong mỗi nhóm; window_frame giới hạn phạm vi dòng tham gia tính toán.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
FROM employees;

Ba hàm này khác nhau ở cách xử lý giá trị bằng nhau (ties):

• ROW_NUMBER() luôn cho số thứ tự duy nhất, liên tục (1, 2, 3, 4...) — nếu hai người cùng lương, thứ tự giữa họ là ngẫu nhiên (trừ khi thêm cột tiebreak).
• RANK() cho cùng hạng cho các dòng bằng nhau, rồi nhảy cóc: 1, 1, 3, 4.
• DENSE_RANK() cũng cho cùng hạng nhưng không nhảy: 1, 1, 2, 3.

Truy cập dòng lân cận: LAG / LEAD

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    hire_date,
    salary,
    LAG(salary, 1)  OVER (ORDER BY hire_date) AS luong_nguoi_truoc,
    LEAD(salary, 1) OVER (ORDER BY hire_date) AS luong_nguoi_sau,
    salary - LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS chenh_lech
FROM employees;

LAG/LEAD lấy giá trị của dòng đứng trước/sau theo thứ tự cửa sổ. Đây là công cụ chuẩn cho bài toán "so với kỳ trước" (period-over-period): tăng trưởng doanh thu tháng này so với tháng trước, chênh lệch giá trị đo liên tiếp của một cảm biến, v.v. Tham số thứ ba là giá trị mặc định khi không có dòng lân cận (thay vì NULL).

Running total và window frame (ROWS / RANGE)

Đây là phần khiến nhiều người bối rối. Khi bạn thêm ORDER BY vào OVER() mà không khai báo frame tường minh, Oracle mặc định dùng RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Điều này thường đúng cho running total, nhưng RANGE có hành vi khác ROWS khi có giá trị trùng.

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    hire_date,
    salary,
    -- Tổng lũy kế từ đầu partition tới dòng hiện tại
    SUM(salary) OVER (
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- Trung bình trượt 3 dòng (dòng hiện tại + 2 dòng trước)
    AVG(salary) OVER (
        ORDER BY hire_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM employees;

Phân biệt cốt lõi:

• ROWS đếm theo số dòng vật lý: "2 dòng trước" nghĩa là đúng 2 dòng, bất kể giá trị.
• RANGE tính theo giá trị của cột ORDER BY: tất cả các dòng có cùng giá trị ORDER BY được coi như một "peer group" và cùng nằm trong frame. Nếu ba người có cùng hire_date, RANGE ... CURRENT ROW sẽ gộp cả ba vào tổng lũy kế của mỗi người trong nhóm đó.

Khi cần running total "chính xác từng dòng", hãy dùng ROWS tường minh để tránh bất ngờ.

FIRST_VALUE / LAST_VALUE — cái bẫy frame

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    department_id,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
    ) AS nguoi_luong_cao_nhat,
    LAST_VALUE(name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS nguoi_luong_thap_nhat
FROM employees;

Bẫy kinh điển: LAST_VALUE nếu không mở rộng frame tới UNBOUNDED FOLLOWING sẽ chỉ nhìn tới dòng hiện tại (do frame mặc định), nên nó trả về... chính dòng hiện tại chứ không phải giá trị cuối nhóm. Luôn khai báo frame đầy đủ khi dùng LAST_VALUE.

RATIO_TO_REPORT và NTILE

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    department_id,
    salary,
    -- Tỷ trọng lương của nhân viên trong tổng lương phòng
    RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS ty_trong,
    -- Chia toàn bộ nhân viên thành 4 nhóm tứ phân theo lương
    NTILE(4) OVER (ORDER BY salary DESC) AS tu_phan_vi
FROM employees;

RATIO_TO_REPORT trả về tỷ lệ của giá trị dòng hiện tại so với tổng của toàn partition (kết quả trong khoảng 0..1). NTILE(n) phân phối các dòng vào n "xô" (bucket) đều nhau — hữu ích để phân loại A/B/C/D, chia phần trăm khách hàng, v.v.

Muốn ôn lại nền tảng window function theo góc nhìn ANSI/PostgreSQL, xem bài SQL nâng cao PostgreSQL. Về cơ bản OVER() giữa Oracle và Postgres gần như tương thích chuẩn — điểm khác chủ yếu nằm ở các hàm mở rộng và default frame.

Truy vấn phân cấp: CONNECT BY

Đây là "đặc sản" nổi tiếng nhất của Oracle. Trước khi recursive CTE trở thành chuẩn, Oracle đã có cú pháp CONNECT BY để duyệt cây/đồ thị từ những năm rất sớm. Bài toán kinh điển: bảng employees có cột manager_id trỏ tới employee_id của sếp — làm sao in ra cây tổ chức?

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 2) || name AS cay_to_chuc,
    employee_id,
    manager_id
FROM employees
START WITH manager_id IS NULL              -- gốc: người không có sếp
CONNECT BY PRIOR employee_id = manager_id  -- nối cha -> con
ORDER SIBLINGS BY name;

Các thành phần cần nắm:

• START WITH: điều kiện xác định (các) node gốc để bắt đầu duyệt.
• CONNECT BY PRIOR: định nghĩa quan hệ cha–con. PRIOR chỉ tới dòng cha (dòng của cấp trên trong quá trình duyệt). PRIOR employee_id = manager_id đọc là: "id của cha bằng manager_id của con" — tức đi từ trên xuống.
• LEVEL: pseudo-column trả về độ sâu (gốc = 1). Dùng LPAD/LEVEL để thụt lề in cây.
• ORDER SIBLINGS BY: sắp xếp các node cùng cấp mà vẫn giữ cấu trúc cây.

SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT, NOCYCLE

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    name,
    LEVEL,
    SYS_CONNECT_BY_PATH(name, ' / ') AS duong_dan,   -- ví dụ: / CEO / VP / Manager / Staff
    CONNECT_BY_ROOT name             AS goc_cay,      -- tên node gốc của nhánh
    CONNECT_BY_ISLEAF                AS la_node_la    -- 1 nếu là node lá, 0 nếu không
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

• SYS_CONNECT_BY_PATH(cot, dau_phan_cach): xây chuỗi đường dẫn từ gốc tới node hiện tại — cực hữu ích để hiển thị breadcrumb hoặc phát hiện thứ bậc.
• CONNECT_BY_ROOT: lấy giá trị của node gốc trên nhánh đang duyệt.
• CONNECT_BY_ISLEAF: đánh dấu node lá.
• NOCYCLE: cho phép truy vấn không lỗi khi dữ liệu có vòng lặp (cycle); kết hợp với pseudo-column CONNECT_BY_ISCYCLE để nhận diện dòng gây vòng.

Sơ đồ cây phân cấp mà CONNECT BY duyệt

START WITH chọn node xanh dương (gốc), rồi mỗi bước CONNECT BY PRIOR nhân bản đi xuống một cấp; các node xanh lá là CONNECT_BY_ISLEAF = 1. SYS_CONNECT_BY_PATH cho Nhân viên A sẽ là / CEO / VP Kỹ thuật / Trưởng nhóm Backend / Nhân viên A.

So sánh với recursive CTE chuẩn

Oracle cũng hỗ trợ WITH RECURSIVE theo chuẩn ANSI từ 11gR2 (thực ra Oracle dùng WITH ... (recursive query) không cần từ khoá RECURSIVE). Cùng bài toán trên viết theo CTE:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
WITH org (employee_id, name, manager_id, lvl) AS (
    SELECT employee_id, name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT * FROM org;

Khi nào dùng gì? CONNECT BY gọn hơn và có sẵn các pseudo-column tiện lợi (LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT), rất mạnh cho báo cáo phân cấp thuần Oracle. Recursive CTE khả chuyển hơn (chạy được trên PostgreSQL, SQL Server, MySQL 8+), linh hoạt hơn cho đồ thị phức tạp và dễ kết hợp nhiều bước. Với hệ thống có kế hoạch đa nền tảng, ưu tiên CTE; với hệ thống thuần Oracle cần code ngắn gọn, CONNECT BY vẫn rất đáng dùng.

MERGE — upsert cho ETL

MERGE (chuẩn SQL:2003, Oracle triển khai từ 9i) gộp INSERT/UPDATE/DELETE vào một câu lệnh dựa trên điều kiện khớp. Đây là công cụ chủ lực cho ETL: nạp dữ liệu từ bảng staging vào bảng đích, cập nhật dòng đã tồn tại và chèn dòng mới trong một lần quét.

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
MERGE INTO accounts t                                -- bảng đích
USING stg_accounts s                                 -- nguồn (staging)
   ON (t.account_id = s.account_id)                  -- điều kiện khớp
WHEN MATCHED THEN
    UPDATE SET t.balance     = s.balance,
               t.customer_id = s.customer_id
    WHERE t.balance <> s.balance                     -- chỉ update khi thực sự đổi
WHEN NOT MATCHED THEN
    INSERT (account_id, customer_id, balance)
    VALUES (s.account_id, s.customer_id, s.balance);

Điểm mạnh:

• Một lần quét nguồn thay vì viết riêng UPDATE rồi INSERT (giảm số lần đọc, tránh race condition giữa hai câu).
• WHEN MATCHED có thể kèm WHERE để bỏ qua các dòng không thay đổi, giảm redo/undo và trigger vô ích.
• Oracle còn cho phép DELETE bên trong mệnh đề WHEN MATCHED THEN UPDATE ... DELETE WHERE ... để dọn các dòng thoả điều kiện sau khi update.

Luồng xử lý của MERGE

So với PostgreSQL: Postgres dùng INSERT ... ON CONFLICT ... DO UPDATE (UPSERT) và từ v15 cũng có lệnh MERGE chuẩn. Cách tiếp cận ON CONFLICT của Postgres cần một unique/primary key để "phát hiện xung đột", trong khi MERGE của Oracle dựa vào điều kiện ON tùy ý — linh hoạt hơn nhưng cũng dễ tạo lỗi "unable to get a stable set of rows" nếu nguồn có nhiều dòng cùng khớp một dòng đích.

ROWNUM, ROW_NUMBER() và phân trang

ROWNUM là pseudo-column gán số thứ tự cho dòng ngay khi dòng được trả ra, trước khi ORDER BY áp dụng. Đây là nguồn gốc của một trong những lỗi phổ biến nhất với người mới dùng Oracle.

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres) — SAI!
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;
-- Câu trên lấy 5 dòng BẤT KỲ rồi mới sắp xếp, KHÔNG phải top 5 lương cao nhất.

Cách đúng theo kiểu cổ điển là bọc trong subquery để ORDER BY chạy trước, rồi mới lọc ROWNUM:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres) — ĐÚNG (top 5)
SELECT *
FROM (
    SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Phân trang cổ điển (trang thứ 3, mỗi trang 10 dòng) cần hai lớp lồng vì ROWNUM không so sánh được > n trực tiếp:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT *
FROM (
    SELECT a.*, ROWNUM AS rn
    FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) a
    WHERE ROWNUM <= 30          -- tới hết trang 3
)
WHERE rn > 20;                  -- bỏ qua 2 trang đầu

ROW_NUMBER() (analytic) thay thế cách trên gọn hơn và không dính bẫy thứ tự, vì nó áp dụng sau ORDER BY trong OVER():

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees e
)
WHERE rn BETWEEN 21 AND 30;

OFFSET / FETCH (12c trở lên)

Từ Oracle 12c, cú pháp chuẩn ANSI OFFSET ... FETCH đã có, giúp phân trang gọn như PostgreSQL:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT *
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Đây là cách khuyến nghị cho code mới trên Oracle 12c+. Lưu ý phân trang bằng OFFSET lớn vẫn tốn kém (phải quét bỏ nhiều dòng); với dữ liệu lớn, cân nhắc keyset pagination (phân trang theo con trỏ) — chủ đề bàn kỹ ở bài tối ưu.

ROWID — địa chỉ vật lý của dòng

ROWID là pseudo-column trả về địa chỉ vật lý của một dòng (mã hóa file/block/row trong datafile). Nó là cách truy cập dòng nhanh nhất có thể trong Oracle — index thực chất trỏ tới ROWID. Ứng dụng thực tế: xóa dòng trùng lặp giữ lại một bản, hoặc cập nhật lại đúng dòng vừa đọc.

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
-- Xóa dòng trùng, giữ lại ROWID nhỏ nhất mỗi nhóm
DELETE FROM employees e
WHERE ROWID > (
    SELECT MIN(ROWID) FROM employees e2
    WHERE e2.name = e.name AND e2.department_id = e.department_id
);

Lưu ý ROWID có thể thay đổi (ví dụ khi bảng bị MOVE, hoặc với bảng partition khi dòng chuyển partition), nên không dùng nó làm khóa lưu lâu dài.

Sequence và IDENTITY column

Oracle sinh khóa tự tăng qua sequence — một đối tượng độc lập với bảng:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
CREATE SEQUENCE seq_account_id START WITH 1 INCREMENT BY 1 CACHE 20;

INSERT INTO accounts (account_id, customer_id, balance)
VALUES (seq_account_id.NEXTVAL, 1001, 500000);

SELECT seq_account_id.CURRVAL FROM dual;   -- giá trị vừa cấp trong session hiện tại

• NEXTVAL cấp giá trị kế tiếp; CURRVAL trả giá trị vừa cấp trong session (phải gọi NEXTVAL trước ít nhất một lần).
• CACHE n giữ trước n giá trị trong bộ nhớ để tăng hiệu năng, đổi lại có thể có "lỗ hổng" số nếu instance restart — sequence không cam kết liên tục hay không trùng qua sự cố, chỉ cam kết duy nhất và tăng dần.

Từ 12c, Oracle hỗ trợ IDENTITY column theo chuẩn, che giấu sequence bên dưới:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
CREATE TABLE accounts (
    account_id  NUMBER GENERATED ALWAYS AS IDENTITY,
    customer_id NUMBER,
    balance     NUMBER
);

Hàm hữu ích và khác biệt phương ngữ

Nhóm hàm sau xuất hiện dày đặc trong code Oracle thực tế:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    -- DECODE: if/else dạng ngắn, đặc trưng Oracle
    DECODE(department_id, 10, 'Kế toán', 20, 'Kỹ thuật', 'Khác') AS ten_phong,
    -- NVL: thay NULL bằng giá trị mặc định (2 tham số)
    NVL(manager_id, 0) AS manager_or_zero,
    -- NVL2: rẽ nhánh theo NULL/không-NULL (3 tham số)
    NVL2(manager_id, 'Có sếp', 'Không sếp') AS trang_thai,
    -- COALESCE: chuẩn ANSI, nhiều tham số
    COALESCE(manager_id, department_id, 0) AS gia_tri_dau_khong_null,
    -- CASE: chuẩn ANSI, linh hoạt hơn DECODE
    CASE WHEN salary >= 50000 THEN 'Cao' ELSE 'Thường' END AS bac_luong
FROM employees;

Về ngày/chuỗi, Oracle dùng format model cho TO_CHAR/TO_DATE:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') AS ngay_dinh_dang,
    TO_DATE('2026-07-01', 'YYYY-MM-DD')         AS chuyen_chuoi_thanh_ngay,
    TRUNC(SYSDATE)                              AS dau_ngay_hom_nay,   -- bỏ phần giờ
    TRUNC(SYSDATE, 'MM')                        AS dau_thang,          -- ngày 1 của tháng
    SYSDATE                                     AS bay_gio
FROM dual;

TRUNC trên ngày cực kỳ hữu dụng để nhóm theo ngày/tháng/năm — TRUNC(dt) cắt bỏ phần giờ (Oracle DATE luôn kèm giờ), TRUNC(dt, 'MM') về đầu tháng, TRUNC(dt, 'YYYY') về đầu năm.

Bốn khác biệt phương ngữ dễ sập bẫy

1. DUAL: Oracle bắt buộc phải có FROM trong SELECT. Khi chỉ muốn tính một biểu thức, bạn dùng bảng ảo một dòng dual: SELECT 1 + 1 FROM dual;. PostgreSQL không cần — SELECT 1 + 1; chạy thẳng.
2. NULL và chuỗi rỗng: Oracle coi chuỗi rỗng '' là NULL. Vì vậy WHERE col = '' không bao giờ đúng, phải dùng col IS NULL. Đây là điểm khác biệt lớn và nguy hiểm so với PostgreSQL (nơi '' là một giá trị chuỗi khác NULL).
3. Nối chuỗi ||: Oracle dùng toán tử || (cũng có hàm CONCAT nhưng chỉ nhận 2 tham số). Điểm thú vị: do '' là NULL, 'A' || NULL trong Oracle vẫn ra 'A' (nối với NULL bị bỏ qua) — khác hành vi NULL-lan-truyền thông thường.
4. Outer join cũ (+): code Oracle di sản dùng cú pháp WHERE a.id = b.id(+) cho left join thay vì LEFT JOIN chuẩn. Nên dùng cú pháp ANSI JOIN cho code mới vì (+) có nhiều giới hạn và khó đọc.

Hint tối ưu — giới thiệu

Oracle cho phép "gợi ý" (hint) trình tối ưu qua comment đặc biệt /*+ ... */ đặt ngay sau SELECT. Hint ép hoặc khuyến nghị optimizer chọn một chiến lược cụ thể:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT /*+ INDEX(e emp_dept_idx) */ * FROM employees e WHERE department_id = 10;
SELECT /*+ FULL(e) */                * FROM employees e WHERE salary > 100000;
SELECT /*+ LEADING(d e) USE_NL(e) */ *
FROM departments d JOIN employees e ON e.department_id = d.department_id;

• /*+ INDEX(bang index) */: ép dùng index cụ thể.
• /*+ FULL(bang) */: ép full table scan (đôi khi nhanh hơn khi cần đọc phần lớn bảng).
• /*+ LEADING(...) */: chỉ định thứ tự bảng dẫn đầu trong join.
• /*+ USE_NL / USE_HASH */: ép kiểu join (nested loop / hash join).

Hint là con dao hai lưỡi: dùng đúng cứu được truy vấn lệch kế hoạch, dùng sai làm cứng nhắc và lỗi thời khi dữ liệu thay đổi. Chi tiết cách đọc execution plan, thu thập statistics và khi nào nên/không nên hint — xem bài Oracle tối ưu. Về việc gọi các tính năng này từ code thủ tục, xem PL/SQL.

Use case thực tế

1. Duyệt cây phòng ban bằng CONNECT BY. Hệ thống nhân sự cần in sơ đồ tổ chức có thụt lề, kèm đường dẫn đầy đủ từ CEO tới từng nhân viên và đánh dấu ai là node lá (nhân viên không quản lý ai):

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
SELECT
    LPAD(' ', (LEVEL - 1) * 3) || name AS so_do,
    LEVEL AS cap,
    SYS_CONNECT_BY_PATH(name, ' > ')   AS duong_dan_bao_cao,
    CONNECT_BY_ISLEAF                  AS la_nhan_vien_cuoi
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;

Chỉ một câu lệnh thay cho một vòng đệ quy ở tầng ứng dụng — và Oracle đọc cây trong một lần quét.

2. ETL upsert nạp số dư tài khoản bằng MERGE. Mỗi đêm, job ETL đổ dữ liệu từ hệ thống nguồn vào bảng staging stg_accounts, rồi đồng bộ vào bảng accounts: tài khoản đã có thì cập nhật số dư (chỉ khi thực sự đổi), tài khoản mới thì chèn:

-- (minh hoạ — Oracle, không chạy trong sandbox Postgres)
MERGE INTO accounts t
USING (
    SELECT account_id, customer_id, balance
    FROM stg_accounts
    WHERE load_date = TRUNC(SYSDATE)
) s
   ON (t.account_id = s.account_id)
WHEN MATCHED THEN
    UPDATE SET t.balance = s.balance, t.customer_id = s.customer_id
    WHERE t.balance <> s.balance
WHEN NOT MATCHED THEN
    INSERT (account_id, customer_id, balance)
    VALUES (s.account_id, s.customer_id, s.balance);
COMMIT;

Mẫu này là xương sống của hầu hết pipeline data warehouse trên Oracle: idempotent (chạy lại nhiều lần cho cùng kết quả), gọn, và tối ưu I/O nhờ điều kiện WHERE trong WHEN MATCHED.

Ghi nhớ

• Analytic function tính trên cửa sổ mà không co dòng; OVER(PARTITION BY ... ORDER BY ...) là trái tim. Nhớ default frame là RANGE ... CURRENT ROW — dùng ROWS tường minh cho running total chính xác, và luôn mở UNBOUNDED FOLLOWING cho LAST_VALUE.
• ROW_NUMBER / RANK / DENSE_RANK khác nhau ở cách xử lý ties: không lặp / lặp-có-nhảy / lặp-không-nhảy.
• CONNECT BY (START WITH + CONNECT BY PRIOR) là cách duyệt cây gọn của Oracle với LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT, NOCYCLE. Cần khả chuyển thì dùng recursive CTE (Oracle hỗ trợ từ 11gR2).
• MERGE = upsert một-câu-lệnh, chủ lực cho ETL; dùng WHERE trong WHEN MATCHED để bỏ qua dòng không đổi.
• ROWNUM áp dụng trước ORDER BY — muốn top-N phải bọc subquery. ROW_NUMBER() và OFFSET/FETCH (12c+) an toàn hơn cho phân trang. ROWID là địa chỉ vật lý, nhanh nhất nhưng không bền.
• Sequence (NEXTVAL/CURRVAL) cấp khóa tự tăng, có thể có lỗ hổng số; IDENTITY (12c+) là bọc chuẩn của sequence.
• Phương ngữ: cần FROM dual cho biểu thức đơn; '' = NULL trong Oracle; nối chuỗi bằng ||; tránh outer join (+) cũ trong code mới.
• Hint /*+ ... */ gợi ý optimizer — mạnh nhưng dễ lỗi thời; chi tiết ở bài tối ưu.

Xem thêm: PL/SQL · Oracle tối ưu · SQL nâng cao PostgreSQL.