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

平时工作中出报表时，要求分别列出员工的一级部门，二级部门....，在数据库中，部门表（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;

--结束--

思路是这样的：通过connect by与sys_connect_by_path获取到部门路径的字符串，通过分割符（>）去截取。

例如： objno objname objnos objnames

D001 凤凰网 >D001 >凤凰网

D002 信息部 >D001>D002 >凤凰网>信息部

D003 研发组　　　　　　　>D001>D002>D003　　　　>凤凰网>信息部>研发组　

根据instr(objnos,'>',1,X)获取分割符的位置（instr(objnos,'>',1,X)登录0时说明后面不包含此字符了，返回0，会出现截取不正确的字符串，赋值999，从第999个字符开始截取，一般就会截取空字符串了），使用substr去截取，从而获得各层级的数据。

结果：

　　　一级编号一级名称二级编号二级名称三级编号三级名称四级编号四级部门五级编号五级名称当前编号当前名称

　　　D001 凤凰网　　 D001 凤凰网

D001 凤凰网 D002 信息部 D002 信息部

D001 凤凰网 D002 信息部 D003 研发组 D003 研发组

目前只想到了这种方法

巴特西

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

最新文章

热门文章