1、格式化层次化查询的结果：（level伪列，显示显示树中的层次数）

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=1
connect by prior employee_id=manager_id;

查询结果：

   level      employee

-----------------------------------------

1    1     James Smith
2    2          Ron Johnson
3    3               Fred Hobbs
4    3               Rob Green
5    2          Susan Jones
6    3               Jane Brown
7    4                    Henry Heyson
8    3               John Grey
9    4                    Jean Blue
10  2          Kevin Black
11  3               Keith Long
12  3               Frank Howard
13  3               Doreen Penn

通过查看格式化之后的结果、员工关系一目了然。

2、start with句子中使用子查询

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=(
select employee_id from more_employees
where first_name='Susan' and last_name='Jones'
)
connect by prior employee_id=manager_id;

查询结果：

level    employee

-----------------------------

   1     Susan Jones
   2          Jane Brown
   3               Henry Heyson
   2          John Grey
   3               Jean Blue

3、从下向上遍历树

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=(
select employee_id from more_employees
where first_name='Susan' and last_name='Jones'
)
connect by prior manager_id=employee_id;

查询结果：

1 1     Susan Jones
2 2          James Smith

4、子查询中删除节点和分支

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
where last_name !='Johnson'
start with employee_id=1
connect by prior employee_id=manager_id;

1 1     James Smith
2 3               Fred Hobbs
3 3               Rob Green
4 2          Susan Jones
5 3               Jane Brown
6 4                    Henry Heyson
7 3               John Grey
8 4                    Jean Blue
9 2          Kevin Black
10 3               Keith Long
11 3               Frank Howard
12 3               Doreen Penn

查询结果：Ron Johnson已清除，但它的下属Rob Green 、Fred Hobbs仍存在，

可以再connect by prior使用and子句将 将其Ron Johnson下属从结果中除去：

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=1
connect by prior employee_id=manager_id and last_name !='Johnson';

查询结果：

1 1     James Smith
2 2          Susan Jones
3 3               Jane Brown
4 4                    Henry Heyson
5 3               John Grey
6 4                    Jean Blue
7 2          Kevin Black
8 3               Keith Long
9 3               Frank Howard
10 3               Doreen Penn