【Oracle】樹狀結構查詢

--oracle中的樹狀結構查詢實際上就是一個中序周遊
--顯示以KING為根節點的樹，start with定義起始節點
SELECT *
FROM   emp a
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--置于等号前面，由父節點向子節點方向檢索；置于等号後面，則從子節點向父節點方向檢索
SELECT *
FROM   emp a
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--使用僞列level來顯示層級關系
SELECT *
FROM   emp a
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;

SELECT level, a.*
FROM   emp a
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--使用函數 sys_connect_by_path 顯示全路徑
SELECT LEVEL, sys_connect_by_path(a.ename, '/'), a.*
FROM   emp a
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--where子句隻限定單個節點，不影響子其子節點或者父節點
SELECT LEVEL, sys_connect_by_path(a.ename, '/'), a.*
FROM   emp a
WHERE a.empno <> 7369
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--connect by 子句限定整個以目前節點為起始節點的整個分支
--connect_by_root 可以在列之前顯示最高節點的内容
SELECT LEVEL, connect_by_root(a.job),sys_connect_by_path(a.ename, '/'), a.*
FROM   emp a
WHERE a.empno <> 7369
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--connect_by_isleaf 顯示目前行是否葉子節點，1：是；0：否
SELECT LEVEL, connect_by_isleaf,sys_connect_by_path(a.ename, '/'), a.*
FROM   emp a
WHERE a.empno <> 7369
START  WITH a.empno = 7839
CONNECT BY PRIOR a.empno = a.mgr;
--connect_by_iscycle 10g中開始有的新特性，用來判斷目前節點是否産生了循環，0：否；1：是
SELECT LEVEL, connect_by_iscycle, connect_by_isleaf,sys_connect_by_path(a.ename, '/'), a.*
FROM   emp a
WHERE a.empno <> 7369
START  WITH a.empno = 7839
CONNECT BY NOCYCLE PRIOR a.empno = a.mgr;