oracle 树形查询，自连接查询

例1

select employee_id, first_name,last_name,manager_id
from employees  
start with employee_id=100
connect by prior employee_id=manager_id

1    Steven King
2    -Neena Kochhar
3    --Nancy Greenberg
4    ---Daniel Faviet
4    ---John Chen
...

例2

create table my_system_menu
( menu_id number,
  title varchar2(100),
  parent_id number,
  constraint my_system_menu_pk primary key (menu_id ),
  constraint my_system_menu_fk1 FOREIGN key( parent_id) references my_system_menu(menu_id) 
  );
  
insert into my_system_menu  values (1,'Banner',null);
insert into my_system_menu  values (2,'Student',1);
insert into my_system_menu  values (3,'Course Catalog',2);
insert into my_system_menu  values (4,'Class Schedule',2);
insert into my_system_menu  values (5,'General Person',2);
insert into my_system_menu  values (6,'Faculty Load',2);
insert into my_system_menu  values (7,'Location Management ',2);
insert into my_system_menu  values (8,'Basic Course Information',3);
insert into my_system_menu  values (9,'Course Detail Information',3);
insert into my_system_menu  values (10,'Course Registration Restrictions',3);
commit;

 

select  level,
        case 
        when level=1 then title 
        when level>1 then lpad (title,length(title)+level-1, '-')
        end tree
from my_system_menu  
start with menu_id=1
connect by prior menu_id=parent_id 
order by menu_id