深入掌握 Oracle 層次查詢：從語法到實戰應用

在關聯式資料庫中，處理樹狀或階層式資料結構時，Oracle 的層次查詢功能提供了強大且原生的支援。透過 START WITH ... CONNECT BY PRIOR 子句，我們能輕鬆實現遞歸查詢，無論是組織架構、產品分類還是物料清單，都能高效地提取完整的層級關係。本文將系統介紹其核心語法、關鍵條件解析，並配合實際資料範例演示完整樹、子樹查詢以及遞歸深度控制等常見場景。

核心語法結構

Oracle 層次查詢的標準語法如下：

SELECT ...
FROM <TableName>
WHERE <Conditional-1>
START WITH <Conditional-2>
CONNECT BY <Conditional-3>;

三大關鍵條件的功能定義：

• <Conditional-1>（過濾條件）：作用於最終結果集，篩選符合條件的記錄。
• <Conditional-2>（根節點條件）：定義遞歸的起始點，即樹的根節點或森林的多棵樹根。
• <Conditional-3>（連接條件）：指定父子節點間的關聯邏輯，核心在於 PRIOR 關鍵字的位置。

範例資料準備

建立測試表 t2 並插入模擬雙樹結構資料：

CREATE TABLE t2(
    root_id NUMBER,
    id NUMBER,
    name VARCHAR(5),
    description VARCHAR(10)
);

INSERT INTO t2 VALUES(0, 1, 'a', 'aaa');
INSERT INTO t2 VALUES(1, 2, 'a1', 'aaa1');
INSERT INTO t2 VALUES(1, 3, 'a2', 'aaa2');
INSERT INTO t2 VALUES(0, 4, 'b', 'bbb');
INSERT INTO t2 VALUES(4, 5, 'b1', 'bbb1');
INSERT INTO t2 VALUES(4, 6, 'b2', 'bbb2');

資料呈現兩棵獨立的樹：以 root_id=0 為根的節點 1、4 為兩棵樹的根節點，其下分別掛載子節點。

實戰查詢場景

1. 獲取完整樹結構

從所有根節點（root_id = 0）出發，遞歸向下遍歷：

SELECT * FROM t2
START WITH root_id = 0
CONNECT BY PRIOR id = root_id;

結果將包含節點 1、2、3、4、5、6 共六筆記錄，完整呈現兩棵樹的所有層級。

2. 獲取特定子樹

若僅需某節點為根的子樹，調整 START WITH 條件即可：

-- 以節點 1 為根的子樹
SELECT * FROM t2
START WITH id = 1
CONNECT BY PRIOR id = root_id;

-- 以節點 4 為根的子樹
SELECT * FROM t2
START WITH id = 4
CONNECT BY PRIOR id = root_id;

3. PRIOR 關鍵字的關鍵作用

PRIOR 決定遞歸方向與深度。若省略 PRIOR，查詢將退化為單層連接，不再進行深層遞歸：

-- 錯誤示範：僅返回根節點及其直接子節點
SELECT * FROM t2
START WITH root_id = 0
CONNECT BY id = root_id;

此時 Oracle 視為一般連接條件，不再遞歸展開後續層級，結果集大幅減少。

進階提示

• 可搭配 LEVEL 偽欄位顯示節點層級，或使用 SYS_CONNECT_BY_PATH 構建完整路徑。
• 若需自底向上查詢（子節點追溯至根），只需交換 CONNECT BY 兩邊的欄位位置：CONNECT BY PRIOR root_id = id。
• 注意避免循環引用導致無限遞歸，可加入 NOCYCLE 關鍵字（Oracle 10g+ 支援）。

掌握層次查詢的核心在於理解 PRIOR 的方向性與 START WITH 的根節點定義。透過靈活調整這兩個條件，即可應對各類樹狀資料的查詢需求。