orale 树形

https://blog.csdn.net/WuLex/article/details/82773890 prior 用法

直接调用FUNCTION TABLE(hrorg.prior_org_level(o.org_id)) 传入参数org_id

CREATE OR REPLACE FUNCTION “HRORG”.“PRIOR_ORG_LEVEL”(v_org_id in varchar2)
return hrorg.org_level_table is
t_org_level hrorg.org_level_table := hrorg.org_level_table();
begin
for v_org in (SELECT v_org_id as ORG_ID,
wm_concat(ORG_ID1) as ORG_ID1,
wm_concat(ORG_NAME1) as ORG_NAME1,
–wm_concat(ORG_LEVEL_2) as ORG_LEVEL_2,
wm_concat(ORG_ID2) as ORG_ID2,
wm_concat(ORG_NAME2) as ORG_NAME2,
–wm_concat(ORG_LEVEL_3) as ORG_LEVEL_3,
wm_concat(ORG_ID3) as ORG_ID3,
wm_concat(ORG_NAME3) as ORG_NAME3,
– wm_concat(ORG_LEVEL_4) as ORG_LEVEL_4,
wm_concat(ORG_ID4) as ORG_ID4,
wm_concat(ORG_NAME4) as ORG_NAME4,
– wm_concat(ORG_LEVEL_5) as ORG_LEVEL_5,
wm_concat(ORG_ID5) as ORG_ID5,
wm_concat(ORG_NAME5) as ORG_NAME5,
– wm_concat(ORG_LEVEL_6) as ORG_LEVEL_6,
wm_concat(ORG_ID6) as ORG_ID6,
wm_concat(ORG_NAME6) as ORG_NAME6,
– wm_concat(ORG_LEVEL_7) as ORG_LEVEL_7,
wm_concat(ORG_ID7) as ORG_ID7,
wm_concat(ORG_NAME7) as ORG_NAME7,
– wm_concat(ORG_LEVEL_8) as ORG_LEVEL_8,
wm_concat(ORG_ID8) as ORG_ID8,
wm_concat(ORG_NAME8) as ORG_NAME8,
– wm_concat(ORG_LEVEL_9) as ORG_LEVEL_9,
wm_concat(ORG_ID9) as ORG_ID9,
wm_concat(ORG_NAME9) as ORG_NAME9,
replace(wm_concat(ORG_NAME), ‘,’, ‘/’) ORG_NAME_ALL
from (SELECT ORG_ID,
ORG_NAME,
ORG_LEVEL,
decode(ORG_LEVEL, 1, 1) ORG_LEVEL1,
decode(ORG_LEVEL, 1, ORG_ID) ORG_ID1,
decode(ORG_LEVEL, 1, ORG_NAME) ORG_NAME1,
decode(ORG_LEVEL, 2, 2) ORG_LEVEL2,
decode(ORG_LEVEL, 2, ORG_ID) ORG_ID2,
decode(ORG_LEVEL, 2, ORG_NAME) ORG_NAME2,
decode(ORG_LEVEL, 3, 3) ORG_LEVEL3,
decode(ORG_LEVEL, 3, ORG_ID) ORG_ID3,
decode(ORG_LEVEL, 3, ORG_NAME) ORG_NAME3,
decode(ORG_LEVEL, 4, 4) ORG_LEVEL4,
decode(ORG_LEVEL, 4, ORG_ID) ORG_ID4,
decode(ORG_LEVEL, 4, ORG_NAME) ORG_NAME4,
decode(ORG_LEVEL, 5, 5) ORG_LEVEL5,
decode(ORG_LEVEL, 5, ORG_ID) ORG_ID5,
decode(ORG_LEVEL, 5, ORG_NAME) ORG_NAME5,
decode(ORG_LEVEL, 6, 6) ORG_LEVEL6,
decode(ORG_LEVEL, 6, ORG_ID) ORG_ID6,
decode(ORG_LEVEL, 6, ORG_NAME) ORG_NAME6,
decode(ORG_LEVEL, 7, 7) ORG_LEVEL7,
decode(ORG_LEVEL, 7, ORG_ID) ORG_ID7,
decode(ORG_LEVEL, 7, ORG_NAME) ORG_NAME7,
decode(ORG_LEVEL, 8, 8) ORG_LEVEL8,
decode(ORG_LEVEL, 8, ORG_ID) ORG_ID8,
decode(ORG_LEVEL, 8, ORG_NAME) ORG_NAME8,
decode(ORG_LEVEL, 9, 9) ORG_LEVEL9,
decode(ORG_LEVEL, 9, ORG_ID) ORG_ID9,
decode(ORG_LEVEL, 9, ORG_NAME) ORG_NAME9
FROM ORGANIZATION oo
START WITH oo.ORG_ID = v_org_id
CONNECT BY prior oo.ORG_PARENT_DEPT = oo.ORG_ID
ORDER BY oo.ORG_LEVEL)) loop
t_org_level.extend;
t_org_level(t_org_level.count) := hrorg.type_org_level(v_org.org_id,
v_org.org_id1,
v_org.ORG_NAME1,
v_org.org_id2,
v_org.org_name2,
v_org.org_id3,
v_org.org_name3,
v_org.org_id4,
v_org.org_name4,
v_org.org_id5,
v_org.org_name5,
v_org.org_id6,
v_org.org_name6,
v_org.org_id7,
v_org.org_name7,
v_org.org_id8,
v_org.org_name8,
v_org.org_id9,
v_org.org_name9,
v_org.org_name_all);
end loop;
return t_org_level;
end;