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

--第一种方式：Walking the Tree: From the Bottom Up
SQL> select employee_id,last_name,job_id,manager_id
2 from employees
3 start with employee_id=101
4 connect by prior manager_id=employee_id;

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ---------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES

--第二种方式：Walking the Tree: From the Top Down
SQL> ;
1 select employee_id,last_name,job_id,manager_id
2 from employees
3 start with employee_id=101
4* connect by prior employee_id=manager_id
SQL> /

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ---------- ---------- ----------
101 Kochhar AD_VP 100
200 Whalen AD_ASST 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205

-- 理解prior修饰符
SQL> select last_name||' report to '|| prior last_name "Walk Top Down"
2 from employees
3 start with last_name='King'
4 connect by prior employee_id=manager_id;

Walk Top Down
--------------------------------------------------------------
King report to
Kochhar report to King
Whalen report to Kochhar
Higgins report to Kochhar
Gietz report to Higgins
De Haan report to King
Hunold report to De Haan
Ernst report to Hunold

--如何格式化树状分级报告？level是个伪列(pseudocolumn)
--Formatting Hierarchical Reports Using LEVEL and LPAD
SQL> column org_chart format a20
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'.')
2 as org_chart
3 from employees
4 start with last_name='King'
5 connect by prior employee_id=manager_id

ORG_CHART
-------------------
King
..Kochhar
....Whalen
....Higgins
......Gietz

-- 如何修剪枝杈？Pruning Branches
SELECT department_id, employee_id,last_name, job_id, salar y
FROM employees
WHERE last_name != 'Higgins'
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- or
SELECT department_id, employee_id,last_name, job_id, salary
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Higgins';

-- 测试题：Create a report that shows the hierarchy of the managers for the employee Lorentz. Display his immediate manager first.
SQL> select last_name
2 from employees
3 start with employee_id=(select b.manager_id from employees b where b.last_name='Lorentz')
4 connect by prior manager_id=employee_id
5 and last_name != 'Lorentz'
LAST_NAME
----------
Hunold
De Haan
King

-- test 2: Create an indented report showing the management hierarchy starting from the employee whose LAST_NAME is Kochhar. Print the employee’s last name, manager ID, and department ID. Give alias names to the columns as shown in the sample output.
SQL> col name for a10
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') name,
2 manager_id mgr,department_id deptno
3 from employees
4 start with last_name='Kochhar'
5 connect by prior employee_id=manager_id;