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;