Oracle 遞歸查詢實戰：START WITH ... CONNECT BY 完整語法與樹狀結構應用

建立測試表與資料

CREATE TABLE TBL_TEST (
    ID   NUMBER,
    NAME VARCHAR2(100 BYTE),
    PID  NUMBER DEFAULT 0
);

INSERT INTO TBL_TEST (ID, NAME, PID) VALUES (1, '10', 0);
INSERT INTO TBL_TEST (ID, NAME, PID) VALUES (2, '11', 1);
INSERT INTO TBL_TEST (ID, NAME, PID) VALUES (3, '20', 0);
INSERT INTO TBL_TEST (ID, NAME, PID) VALUES (4, '12', 1);
INSERT INTO TBL_TEST (ID, NAME, PID) VALUES (5, '121', 2);
COMMIT;

資料結構呈現樹狀階層：

• Root 節點：ID=1 (PID=0)、ID=3 (PID=0)
• 子節點：ID=2,4 皆掛在 ID=1 下；ID=5 掛在 ID=2 下

自頂向下遞歸（Root → Leaf）

SELECT * 
FROM TBL_TEST
START WITH id = 1
CONNECT BY PRIOR id = pid;

結果：依序回傳 1 → 2 → 5 → 4（深度優先走訪）。

自底向上遞歸（Leaf → Root）

SELECT * 
FROM TBL_TEST
START WITH id = 5
CONNECT BY PRIOR pid = id;

結果：依序回傳 5 → 2 → 1。

關鍵子句解析

實用擴充功能

顯示層級與縮排

SELECT LPAD(' ', 2*(LEVEL-1)) || NAME AS TREE_VIEW, LEVEL
FROM TBL_TEST
START WITH id = 1
CONNECT BY PRIOR id = pid;

排除循環參考

SELECT * 
FROM TBL_TEST
START WITH id = 1
CONNECT BY NOCYCLE PRIOR id = pid;

加入 NOCYCLE 可防止資料異常導致無限迴圈，並可搭配 CONNECT_BY_ISCYCLE 偵測循環。

取得完整路徑

SELECT SYS_CONNECT_BY_PATH(NAME, ' > ') AS PATH
FROM TBL_TEST
START WITH id = 1
CONNECT BY PRIOR id = pid;

應用場景

• 組織架構/部門層級查詢
• 物料清單 (BOM) 展開
• 選單/目錄樹狀顯示
• 帳務科目層級彙總