5.12递归查询

Oracle 递归查询

递归查询

递归查询的语法

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

使用语句SQL语句即可把整个递归树全部查询出来。

查询从King开始，从上往下的各级员工

SELECT last_name||' reports to '||
PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

查询从101开始，从下往上的各级员工

SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;

使用LEVEL关键字和LPAD函数，在OUTPUT中显示树形层次

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
 AS org_chart
FROM employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id