教你在oracle中編寫樹級代碼

這是在2002年給一個制衣廠做系統的時候寫的，請大家多批評指正！

 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,' ')
 

 from dept
start with topno='0'
connect by prior departno=topno;

create or replace function GetLevel(TempNo varchar2)
return varchar2
IS
 Result  varchar2(20);
 cursor CurLevel(curTempNo varchar2)
 is
 select departNo
 from dept
 connect by prior departNo = TopNo

• 首頁
• 上一頁
• 1
• 2
• 下一頁
• 尾頁
• 共2頁