create table dept(
departno varchar2(10),
departname varchar2(20),
topno varchar2(10));
insert into dept values('001','董事會','0');
insert into dept values('002','總裁辦','001');
insert into dept values('003','財務部','001');
insert into dept values('004','市場部','002');
insert into dept values('005','公關部','002');
insert into dept values('006','銷售部','002');
insert into dept values('007','分銷處','006');
insert into dept values('008','業務拓展處','004');
insert into dept values('009','銷售科','007');
create table tempdept
(
tempdeptno char(4), /*部門編號*/
tempdeptname varchar2(20), /*部門名稱*/
uppertempdeptno char(4), /*上級部門編號*/
uppertempdeptname varchar2(20), /*上級部門名稱*/
tempdeptlevel number(4) /*部門所處的級別*/
);
select departname
from dept
connect by prior departno=topno
start with topno='0';
select a.departname top ,b.departname next
from dept a,dept b
where a.departno=b.topno;
select lpad(departno,level*2+length(departno),'/')
from dept
start with topno='0'
connect by prior departno=topno;
select departno,departname,level ||'/'|| topno
from dept
start with topno='0'
connect by prior departno=topno;
select lpad(departno,level*2+length(departno),' ')
from dept
start with topno='0'
connect by prior departno=topno;
select lpad(departno,level*3,' ')
