Oracle CONNECT BY 階層查詢完整指南：樹狀結構、遞迴生成序列、SYS_CONNECT_BY_PATH 實戰

CONNECT BY 核心語法

關鍵字功能
START WITH指定根節點條件 (通常為父鍵 IS NULL 或特定值)
CONNECT BY PRIOR定義父子關係：PRIOR 修飾父欄位，等號右側為子欄位
NOCYCLE10g+ 防止無限遞迴 (資料有循環引用時)
LEVEL偽欄位：根節點=1，每層遞增 1
CONNECT_BY_ROOT取得根節點欄位值
CONNECT_BY_ISLEAF偽欄位：1=葉節點，0=非葉節點
SYS_CONNECT_BY_PATH串接從根到當前節點的路徑字串
ORDER SIBLINGS BY同層節點排序，不影響階層結構


實戰範例：部門組織架構樹

建立測試資料
SELECT 
  RPAD(' ', 2*(LEVEL-1), '-') || depname AS depname_tree,
  CONNECT_BY_ROOT depname AS root_dep,
  CONNECT_BY_ISLEAF AS is_leaf,
  LEVEL,
  SYS_CONNECT_BY_PATH(depname, '/') AS path
FROM dep
START WITH upperdepid IS NULL
CONNECT BY PRIOR depid = upperdepid
ORDER SIBLINGS BY depname;

結果解析
-- 生成 1~10 序列
SELECT ROWNUM AS num FROM dual CONNECT BY ROWNUM <= 10;

-- 生成 2024 年 9 月每一天
SELECT TO_DATE('2024-09-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS dt
FROM dual
CONNECT BY ROWNUM <= 
  TO_DATE('2024-09-30', 'YYYY-MM-DD') - TO_DATE('2024-09-01', 'YYYY-MM-DD') + 1;

2. 查詢完整上下游關係
-- 刪除節點及其所有子孫
DELETE FROM dep 
WHERE depid IN (
  SELECT depid FROM dep
  START WITH depid = 1
  CONNECT BY PRIOR depid = upperdepid
);

4. 避免循環引用 (NOCYCLE)
特性CONNECT BY遞迴 CTE (11gR2+)
標準Oracle 專有ANSI SQL:1999 標準
可讀性簡潔、直觀較冗長、但邏輯清晰
功能豐富度內建偽欄位 (LEVEL, PATH...)需自行構建
效能優化器深度優化相近，視寫法而定
跨平台不可移植可移植 (PG, SQL Server, MySQL 8+)


總結
CONNECT BY 是 Oracle 處理階層資料的利器：語法簡潔、內建豐富偽欄位、效能優異。掌握 START WITH、PRIOR、LEVEL、SYS_CONNECT_BY_PATH 核心用法，配合 NOCYCLE 防禦髒資料，能輕鬆解決組織架構、BOM 物料清單、帳務科目表等樹狀查詢需求。現代應用亦可考慮 ANSI 遞迴 CTE 以提升可移植性。