ORACLE-树状数据结构获取各层级节点信息-CSDN博客

平时工作中出报表时，要求分别列出员工的一级部门，二级部门....，在数据库中，部门表（unit）的设计一般为在表中维护每个部门的上级部门（pid字段），或者通过一个关联表(unit_link)维护层级关系（uid:部门表中的主键,pid：上级部门的id,这种可以解决一对多的关系）。

之前取各级部门时用到了很多子查询很麻烦，现在考虑了一个做法，用到了connect by，sys_connect_by_path(9i以后)。

sql语句如下，可以创建视图使用，只需改变临时表，可获得各节点的信息：

--开始--

/*with tmp as
(select b.objno,
b.objname,
sys_connect_by_path(b.objno, '>') objnos,
sys_connect_by_path(b.objname, '>') objnames
from budgetdept b
start with b.id in ('30461F609D0AB57BE050007F01004203',
'30461F609D09B57BE050007F01004203')
connect by prior b.id = b.pid)*/

with tmp as
(select u.id,u.objname,
sys_connect_by_path(u.id, '>') objnos,
sys_connect_by_path(u.objname, '>') objnames
from orgunit u, orgunitlink k
where u.isdelete = 0
and u.id = k.oid
start with u.id = '402881e70ad1d990010ad1e5ec930008'
connect by prior k.oid = k.pid)
select substr(t.objnos,
instr(t.objnos, '>', 1, 1) + 1,
case
when instr(t.objnos, '>', 1, 2) > 0 then
instr(t.objnos, '>', 1, 2) - instr(t.objnos, '>', 1, 1) - 1
else
length(t.objnos) - instr(t.objnos, '>', 1, 1)
end) objno1,

substr(t.objnames,
instr(t.objnames, '>', 1, 1) + 1,
case
when instr(t.objnames, '>', 1, 2) > 0 then
instr(t.objnames, '>', 1, 2) - instr(t.objnames, '>', 1, 1) - 1
else
length(t.objnames) - instr(t.objnames, '>', 1, 1)
end) objname1,
substr(t.objnos,
case
when instr(t.objnos, '>', 1, 2) > 0 then
instr(t.objnos, '>', 1, 2) + 1
else
999
end,
case
when instr(t.objnos, '>', 1, 3) > 0 then
instr(t.objnos, '>', 1, 3) - instr(t.objnos, '>', 1, 2) - 1
else
length(t.objnos) - instr(t.objnos, '>', 1, 2)
end) objno2,
substr(t.objnames,
case
when instr(t.objnames, '>', 1, 2) > 0 then
instr(t.objnames, '>', 1, 2) + 1
else
999
end,
case
when instr(t.objnames, '>', 1, 3) > 0 then
instr(t.objnames, '>', 1, 3) - instr(t.objnames, '>', 1, 2) - 1
else
length(t.objnames) - instr(t.objnames, '>', 1, 2)
end) objname2,
substr(t.objnos,
case
when instr(t.objnos, '>', 1, 4) > 0 then
instr(t.objnos, '>', 1, 4) + 1
else
999
end,
case
when instr(t.objnos, '>', 1, 4) > 0 then
instr(t.objnos, '>', 1, 4) - instr(t.objnos, '>', 1, 3) - 1
else
length(t.objnos) - instr(t.objnos, '>', 1, 3)
end) objno3,
substr(t.objnames,
case
when instr(t.objnames, '>', 1, 4) > 0 then
instr(t.objnames, '>', 1, 4) + 1
else
999
end,
case
when instr(t.objnames, '>', 1, 4) > 0 then
instr(t.objnames, '>', 1, 4) - instr(t.objnames, '>', 1, 3) - 1
else
length(t.objnames) - instr(t.objnames, '>', 1, 3)
end) objname3,
substr(t.objnos,
case
when instr(t.objnos, '>', 1, 4) > 0 then
instr(t.objnos, '>', 1, 4) + 1
else
999
end,
case
when instr(t.objnos, '>', 1, 5) > 0 then
instr(t.objnos, '>', 1, 5) - instr(t.objnos, '>', 1, 4) - 1
else
length(t.objnos) - instr(t.objnos, '>', 1, 4)
end) objno4,
substr(t.objnames,
case
when instr(t.objnames, '>', 1, 4) > 0 then
instr(t.objnames, '>', 1, 4) + 1
else
999
end,
case
when instr(t.objnames, '>', 1, 5) > 0 then
instr(t.objnames, '>', 1, 5) - instr(t.objnames, '>', 1, 4) - 1
else
length(t.objnames) - instr(t.objnames, '>', 1, 4)
end) objname4,
substr(t.objnos,
case
when instr(t.objnos, '>', 1, 5) > 0 then
instr(t.objnos, '>', 1, 5) + 1
else
999
end,
case
when instr(t.objnos, '>', 1, 6) > 0 then
instr(t.objnos, '>', 1, 6) - instr(t.objnos, '>', 1, 5) - 1
else
length(t.objnos) - instr(t.objnos, '>', 1, 5)
end) objno5,
substr(t.objnames,
case
when instr(t.objnames, '>', 1, 5) > 0 then
instr(t.objnames, '>', 1, 5) + 1
else
999
end,
case
when instr(t.objnames, '>', 1, 6) > 0 then
instr(t.objnames, '>', 1, 6) - instr(t.objnames, '>', 1, 5) - 1
else
length(t.objnames) - instr(t.objnames, '>', 1, 5)
end) objname5,
t.*
from tmp t;