Oracle 層次查詢利器：SYS_CONNECT_BY_PATH 函數完整攻略

函數定位與應用場景

SYS_CONNECT_BY_PATH 是 Oracle 專為層次查詢設計的分析函數，能將從根節點到當前節點的路徑，以指定分隔符串連成單一字串回傳。常見用途包含：組織架構路徑顯示、目錄樹完整路徑產出、BOM 物料層級追蹤等。

語法結構

SYS_CONNECT_BY_PATH(column, delimiter)

• column：欲串接的欄位（如節點 ID、名稱）。
• delimiter：分隔符號（如 '/'、'=>'）。

需搭配 START WITH ... CONNECT BY PRIOR 子句使用。

實戰範例：建立測試資料與對比差異

1. 建立樹狀結構測試表

CREATE TABLE test_tree (
  id  NUMBER PRIMARY KEY,
  pid NUMBER,
  name VARCHAR2(50)
);

INSERT INTO test_tree VALUES (1, 0, 'Root');
INSERT INTO test_tree VALUES (2, 1, 'Child A');
INSERT INTO test_tree VALUES (3, 1, 'Child B');
INSERT INTO test_tree VALUES (4, 2, 'Grandchild A1');
INSERT INTO test_tree VALUES (5, 2, 'Grandchild A2');
COMMIT;

2. 正向樹狀查詢

SELECT 
  LEVEL,
  SYS_CONNECT_BY_PATH(id, '/') AS id_path,
  SYS_CONNECT_BY_PATH(name, ' > ') AS name_path
FROM test_tree
START WITH pid = 0
CONNECT BY PRIOR id = pid;

結果示意：

LEVEL  ID_PATH        NAME_PATH
-----  -------------  ----------------------
1      /1             Root
2      /1/2           Root > Child A
3      /1/2/4         Root > Child A > Grandchild A1
3      /1/2/5         Root > Child A > Grandchild A2
2      /1/3           Root > Child B

3. 反向樹狀查詢

SELECT 
  LEVEL,
  SYS_CONNECT_BY_PATH(pid, '/') AS pid_path
FROM test_tree
START WITH id = 4
CONNECT BY PRIOR pid = id;

此查詢從葉節點向上追溯至根節點，適合「查詢某節點的所有祖先」場景。

進階技巧與注意事項

• 去除首字分隔符：可用 LTRIM(path, '/') 或 SUBSTR(path, 2) 處理開頭多餘的分隔符。
• 搭配 CONNECT_BY_ROOT：可同時取得根節點屬性，如 CONNECT_BY_ROOT name AS root_name。
• 效能考量：大量資料時建議在 PRIOR 欄位建立索引；避免無限循環需加入 NOCYCLE 參數（11g+）。
• 長度限制：回傳字串最大 4000 位元組（VARCHAR2 上限），深層次樹狀結構需留意截斷風險。

總結

SYS_CONNECT_BY_PATH 讓層次查詢結果從「扁平化列表」升級為「可視化路徑」，極大提升報表與應用的可讀性。掌握正向/反向查詢語法差異，並善用 LEVEL、CONNECT_BY_ROOT 等輔助欄位，即能靈活應對各類樹狀資料呈現需求。