递归案例

create table tmp_dept(   DepartNO varchar2(10),   DepartName varchar2(20),   TopNo varchar2(10)); 

insert into tmp_dept values ('001', ' 董事会', '0');
insert into tmp_dept values ('002', '总裁办 ', '001');
insert into tmp_dept values ('003', '财务部 ', '001');
insert into tmp_dept values ('004', '市场部 ', '002');
insert into tmp_dept values ('005', '公关部 ', '002');
insert into tmp_dept values ('006', '销售部 ', '002');
insert into tmp_dept values ('007', '分销处 ', '006');
insert into tmp_dept values ('008', '业务拓展处', '004');
insert into tmp_dept values ('009', '销售科', '007');

 

SQL> select distinct departno, departname, level
  2    from tmp_dept
  3  connect by prior topno = departno
  4   start with departno = '005';
 
DEPARTNO   DEPARTNAME                LEVEL
---------- -------------------- ----------
005        公关部                        1
002        总裁办                        2
001         董事会                       3