Oracle HR 模式递归函数练习-树形展示（基于 employees 表）

参考上一篇关于递归函数的文章。

树形递归查询（最经典版）

SELECT LPAD(' ', (LEVEL - 1) * 4) || last_name AS 树形员工列表, employee_id, manager_id, LEVEL AS 层级 FROM employees START WITH manager_id IS NULL -- 根节点：总裁 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY employee_id;

效果（像树一样缩进）

King Kochhar Whalen Mavris Baer De Haan Hunold Ernst Austin ...

带完整路径的树形递归

SELECT LPAD(' ', (LEVEL - 1) * 4) || last_name AS 树形展示, SYS_CONNECT_BY_PATH(last_name, ' -> ') AS 完整路径, LEVEL AS 层级 FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY employee_id;