这是在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 start with TopNo = curTempNo; begin open CurLevel; for v_sor in CurLevel(TempNo) loop Result:=Result || ''''/'''' || v_sor.curTempNo; end loop; return(Result); close CurLevel; dbms_output.put_line(''''success''''); end GetLevel;
REM************************************************************ REM********** 生成部门之间形成的树结构 ****************** REM********** 功能:输入部门后自动产生该部门 ****************** REM********** 及其子部门之间的树关系() ****************** REM********** 输入参数:部门编号ParaDeptId ****************** REM************************************************************
CREATE OR REPLACE procedure getAllSubDeptId(ParaDeptId varchar2) IS DeptNo1 varchar2(4); /*存放部门编号*/ UpperDeptNo1 varchar2(4); /*存放该部门的上级部门编号*/ UpperDeptNo2 varchar2(4); /*存放查询同级上级部门编号*/ UpperDeptNo3 varchar2(4); /*存放查询的上级部门编号*/ level number; /*部门所处级别*/ DSql varchar2(100); /*动态SQL字符串*/ CURSOR getDept IS select DepartNO,TopNo from dept where DepartNo != ParaDeptId; BEGIN --DSql :=''''create table tempdept(TempdeptNo,TempdeptName,UpperTempdeptNo, --UpperTempdeptName,TempdeptLevel)''''; --EXECUTE IMMEDIATE DSql; DSql :=''''TRUNCATE TABLE TempDept''''; dbms_output.put_line(0); EXECUTE IMMEDIATE DSql; dbms_output.put_line(1); insert into TempDept values(ParaDeptId,Null,Null,Null,0); dbms_output.put_line(2); open getDept; fetch getDept into DeptNo1,UpperDeptNo1; WHILE ( SQLCODE = 0 ) LOOP level := 1 ; UpperDeptNo3 := UpperDeptNo1; WHILE ( UpperDeptNo1 is NOT NULL ) loop if (UpperDeptNo1 = ParaDeptId) then insert into tempdept values(DeptNo1,NULL,UpperDeptNo3,NULL,level); exit; else UpperDeptNo2 := UpperDeptNo1; select topNo into UpperDeptNo1 from dept where Departno= UpperDeptNo2; if UpperDeptNo2 != UpperDeptNo1 then level := level+1; end if; end if; END LOOP; fetch getDept into DeptNo1,UpperDeptNo1; END LOOP; close getDept; END; /
没有相关教程
|