表 TRANS_INST(TRANS_ID, UP_TRANS_ID) 存儲樹狀結構,TRANS_ID 為子節點,UP_TRANS_ID 為父節點。已知任一節點 (如 480242),找出其所在樹的所有葉子節點。
SELECT TRANS_ID
FROM TRANS_INST
WHERE CONNECT_BY_ISLEAF = 1
START WITH TRANS_ID = 480242
CONNECT BY PRIOR UP_TRANS_ID = TRANS_ID;
START WITH:指定遍歷起點 (此處為已知節點)CONNECT BY PRIOR UP_TRANS_ID = TRANS_ID:遞歸條件,**PRIOR 修飾父欄位**表示「從子找父」反向遍歷CONNECT_BY_ISLEAF:虛擬欄位,1=葉子節點,0=非葉子-- 從根節點向下找所有子孫
SELECT TRANS_ID
FROM TRANS_INST
START WITH TRANS_ID = :root_id
CONNECT BY PRIOR TRANS_ID = UP_TRANS_ID;
SYS_CONNECT_BY_PATH(col, '/') 顯示完整路徑LEVEL 偽欄位表示層級 (根=1)ORDER SIBLINGS BY 同層排序NOCYCLE 關鍵字 (10g+)