前言

遞歸查詢是 SQL 中非常強(qiáng)大的一種功能，它用于處理具有層次結(jié)構(gòu)或樹形結(jié)構(gòu)的數(shù)據(jù)。在 Oracle 中，遞歸查詢主要通過 START WITH 和 CONNECT BY 子句實(shí)現(xiàn)，這是 Oracle 的傳統(tǒng)方法。從 Oracle 11g Release 2 開始，它也支持 ANSI SQL 標(biāo)準(zhǔn)的 WITH 子句（即公共表表達(dá)式 CTE）形式的遞歸查詢。

我將重點(diǎn)介紹最常用、也是 Oracle 特色的 START WITH ... CONNECT BY 語法，并簡(jiǎn)要對(duì)比標(biāo)準(zhǔn)的遞歸 CTE 方法。

一、核心概念與語法 (START WITH … CONNECT BY)

這種語法專門為處理層次查詢而設(shè)計(jì)，非常直觀和高效。

基本語法結(jié)構(gòu)：

SELECT [LEVEL], column1, column2, ...
FROM table_name
[WHERE ...]
START WITH condition -- 指定層次結(jié)構(gòu)的根節(jié)點(diǎn)（起點(diǎn)）
CONNECT BY [PRIOR] condition -- 定義父節(jié)點(diǎn)和子節(jié)點(diǎn)之間的關(guān)系
[ORDER SIBLINGS BY column_name]; -- 按兄弟節(jié)點(diǎn)排序

關(guān)鍵元素解釋：

1. LEVEL 偽列：

   • 這是一個(gè)系統(tǒng)自動(dòng)生成的偽列，它表示當(dāng)前行在樹形結(jié)構(gòu)中的層級(jí)。
   • 根節(jié)點(diǎn)的 LEVEL 為 1，根節(jié)點(diǎn)的直接子節(jié)點(diǎn)為 2，以此類推。

2. START WITH 子句：

   • 用于指定遞歸開始的根節(jié)點(diǎn)（一行或幾行）。
   • 例如：START WITH employee_id = 100 表示從員工 ID 為 100 的 CEO 開始構(gòu)建樹。

3. CONNECT BY 子句：

   • 這是遞歸查詢的核心，它定義了父行和子行之間的關(guān)系。
   • PRIOR 運(yùn)算符：至關(guān)重要。它引用的是父行的列。
     • CONNECT BY PRIOR child_id = parent_id：表示上一行的 child_id 等于當(dāng)前行的 parent_id。這通常用于從父節(jié)點(diǎn)向下遍歷到子節(jié)點(diǎn)（自上而下）。
     • CONNECT BY child_id = PRIOR parent_id：表示當(dāng)前行的 child_id 等于上一行的 parent_id。這可以用于從子節(jié)點(diǎn)向上遍歷到根節(jié)點(diǎn)（自下而上）。

4. ORDER SIBLINGS BY 子句：

   • 在保持層次結(jié)構(gòu)完整性的前提下，對(duì)同一父節(jié)點(diǎn)下的兄弟節(jié)點(diǎn)進(jìn)行排序。
   • 比直接在最后用 ORDER BY 更合理，因?yàn)樗粫?huì)打亂樹的顯示順序。

二、經(jīng)典示例：?jiǎn)T工組織架構(gòu)圖

假設(shè)我們有一個(gè) employees 表，結(jié)構(gòu)如下：

需求： 查詢所有員工，并顯示他們的匯報(bào)層級(jí)關(guān)系。

查詢語句（自上而下）：

SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*4, ' ') || NAME AS Indented_Name, -- 用縮進(jìn)直觀顯示層級(jí)
    EMPLOYEE_ID,
    NAME,
    MANAGER_ID,
    JOB_TITLE
FROM employees
START WITH MANAGER_ID IS NULL -- 從最大的老板開始（沒有經(jīng)理的人）
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 上一行的員工ID = 當(dāng)前行的經(jīng)理ID
ORDER SIBLINGS BY NAME; -- 同一經(jīng)理下的員工按名字排序

查詢結(jié)果可能如下：

從這個(gè)結(jié)果可以清晰地看出 King 是根節(jié)點(diǎn)，De Haan 和 Kochhar 向他匯報(bào)，Hunold 向 De Haan 匯報(bào)，Ernst 向 Hunold 匯報(bào)。

三、其他有用的運(yùn)算符和函數(shù)

1. CONNECT_BY_ROOT：

   • 用于獲取當(dāng)前行所在樹的根節(jié)點(diǎn)的某列值。
   • SELECT CONNECT_BY_ROOT NAME AS Top_Manager, NAME ... 會(huì)為 Ernst 顯示 Top_Manager 是 King。

2. SYS_CONNECT_BY_PATH：

   • 顯示從根節(jié)點(diǎn)到當(dāng)前節(jié)點(diǎn)的完整路徑。
   • SELECT SYS_CONNECT_BY_PATH(NAME, ' -> ') AS Path ... 對(duì)于 Ernst，會(huì)顯示 -> King -> De Haan -> Hunold -> Ernst。

3. CONNECT_BY_ISLEAF：

   • 判斷當(dāng)前行是否是葉子節(jié)點(diǎn)（即沒有子節(jié)點(diǎn)）。是葉子節(jié)點(diǎn)則返回 1，否則返回 0。

四、ANSI SQL 標(biāo)準(zhǔn)方法：遞歸公用表表達(dá)式 (CTE)

Oracle 也支持使用 WITH 子句進(jìn)行遞歸查詢，語法更符合其他數(shù)據(jù)庫（如 PostgreSQL, SQL Server）的標(biāo)準(zhǔn)。

語法結(jié)構(gòu)：

WITH cte_name (column_list) AS (
    -- 錨定成員 (Anchor Member)：定義根節(jié)點(diǎn)
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition -- 類似于 START WITH

    UNION ALL

    -- 遞歸成員 (Recursive Member)：引用CTE自身，進(jìn)行遞歸join
    SELECT t.column1, t.column2, ...
    FROM table_name t
    JOIN cte_name c ON t.parent_id = c.child_id -- 類似于 CONNECT BY
)
-- 主查詢
SELECT * FROM cte_name;

用遞歸 CTE 實(shí)現(xiàn)上面的例子：

WITH Employee_Tree (LEVEL, EMPLOYEE_ID, NAME, MANAGER_ID, JOB_TITLE) AS (
    -- 錨定成員：找到根節(jié)點(diǎn)
    SELECT
        1 AS LEVEL,
        EMPLOYEE_ID,
        NAME,
        MANAGER_ID,
        JOB_TITLE
    FROM employees
    WHERE MANAGER_ID IS NULL

    UNION ALL

    -- 遞歸成員：連接員工表和CTE自身
    SELECT
        p.LEVEL + 1, -- 層級(jí)增加
        e.EMPLOYEE_ID,
        e.NAME,
        e.MANAGER_ID,
        e.JOB_TITLE
    FROM employees e
    INNER JOIN Employee_Tree p ON e.MANAGER_ID = p.EMPLOYEE_ID
)
SELECT * FROM Employee_Tree
ORDER BY LEVEL, NAME;

五、兩種方法的對(duì)比

總結(jié)

• 對(duì)于 Oracle 環(huán)境下的開發(fā)，START WITH ... CONNECT BY 是處理遞歸查詢的首選，因?yàn)樗Z法簡(jiǎn)潔、功能專一且性能優(yōu)異。
• 如果你需要編寫跨數(shù)據(jù)庫兼容的 SQL，或者希望遞歸邏輯更符合通用的編程思維（先錨定再遞歸），那么應(yīng)該使用遞歸 CTE (WITH 子句)。

無論是哪種方法，遞歸查詢都是操作樹形結(jié)構(gòu)數(shù)據(jù)（如組織架構(gòu)、菜單、分類目錄、BOM物料清單）的利器。

到此這篇關(guān)于Oracle遞歸查詢的文章就介紹到這了,更多相關(guān)Oracle遞歸查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論