select lpad(' ',level*2,' ')||DEPT_CODE,prior DEPT_CODE DEPT_PARENT_CODE from table_name connect by prior DEPT_CODE = DEPT_PARENT_CODE start with fid = '000';
create or replace function get_dept_parent( indept_code varchar2) return varchar2 is pcode department.dept_parent%TYPE; begin select dept_parent_code into pcode from department where dept_code=indept_code; return get_dept_parent(pcode)||indept_code; exception when others then return indept_code; end; /
create or replace procedure p_recursion(p_code varchar2,p_level number default 0) as begin dbms_output.put_line(lpad(' ',p_level*2,' ')||p_code); for c in (select DEPT_CODE from table_name where DEPT_PARENT_CODE=p_code order by DEPT_CODE) loop p_recursion(c.DEPT_CODE,p_level+1); end loop; end;
create or replace procedure p_recursion(p_code varchar2,p_level number default 0) as begin dbms_output.put_line(lpad(' ',p_level*2,' ')||p_code); for c in (select DEPT_CODE from table_name where DEPT_PARENT_CODE=p_code order by DEPT_CODE) loop p_recursion(c.DEPT_CODE,p_level+1); end loop; end;
create function get(p_code in varchar2) return varchar2 as str varchar2(20); begin select path||DEPT_CODE into str from (select DEPT_CODE,sys_connect_by_path(DEPT_CODE,'') path from table_name connect by prior DEPT_CODE = DEPT_PARENT_CODE start with DEPT_CODE =p_code) where rownum=1; return str; exception when others then return '0'; end; /以上语句只支持oracle9i,若有问题,再针对版本决定解决方法
from table_name
connect by prior DEPT_CODE = DEPT_PARENT_CODE
start with fid = '000';
indept_code varchar2)
return varchar2
is
pcode department.dept_parent%TYPE;
begin
select dept_parent_code into pcode from department where dept_code=indept_code;
return get_dept_parent(pcode)||indept_code;
exception
when others then return indept_code;
end;
/
as
begin
dbms_output.put_line(lpad(' ',p_level*2,' ')||p_code);
for c in (select DEPT_CODE from table_name where DEPT_PARENT_CODE=p_code order by DEPT_CODE) loop
p_recursion(c.DEPT_CODE,p_level+1);
end loop;
end;
as
begin
dbms_output.put_line(lpad(' ',p_level*2,' ')||p_code);
for c in (select DEPT_CODE from table_name where DEPT_PARENT_CODE=p_code order by DEPT_CODE) loop
p_recursion(c.DEPT_CODE,p_level+1);
end loop;
end;
return varchar2
as
str varchar2(20);
begin
select path||DEPT_CODE into str from
(select DEPT_CODE,sys_connect_by_path(DEPT_CODE,'') path
from table_name
connect by prior DEPT_CODE = DEPT_PARENT_CODE
start with DEPT_CODE =p_code) where rownum=1;
return str;
exception
when others then
return '0';
end;
/以上语句只支持oracle9i,若有问题,再针对版本决定解决方法