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,'''' '''')
没有相关教程
|