peoplesoft function PSTREENODE 通过 deptid 获得部门树 全路径 code

create or replace function getUnitFullcode(deptid in varchar)
return varchar2 is
r varchar2(1024);
c int;
n varchar2(1024);
m varchar2(1024);
r_name varchar2(1024);
m_deptid varchar2(64);
begin
r := '';
select a.tree_level_num
into c
from pstreenode a
where a.tree_name = 'DEPT_SECURITY'
and a.tree_node = deptid
and A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM pstreenode A_ED
WHERE A.tree_node = A_ED.tree_node);
r:=deptid; if c = 1 then
return r;
end if; select a.tree_level_num, a.parent_node_name, a.tree_node
into c, n, m
from pstreenode a
where a.tree_name = 'DEPT_SECURITY'
and a.tree_node = deptid
and A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM pstreenode A_ED
WHERE A.tree_node = A_ED.tree_node);
while c <> 1 loop r := n || '/' || r; select a.tree_level_num, a.parent_node_name, a.tree_node
into c, n, m
from pstreenode a
where a.tree_name = 'DEPT_SECURITY'
and a.tree_node = n
and A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM pstreenode A_ED
WHERE A.tree_node = A_ED.tree_node);
end loop;
return r;
end getUnitFullcode;