--查找顶级
with info as
(select '001' org_id, '0' porg_id
from dual
union all
select '002' org_id, '001' porg_id
from dual
union all
select '003' org_id, '002' porg_id
from dual
union all
select '004' org_id, '002' porg_id
from dual
union all
select '011' org_id, '0' porg_id
from dual
union all
select '012' org_id, '011' porg_id
from dual
union all
select '013' org_id, '012' porg_id
from dual
union all
select '014' org_id, '012' porg_id
from dual
union all
select '015' org_id, '014' porg_id
from dual)
select org_id, porg_id, connect_by_root(org_id) root_name, level
from info a
start with porg_id = 0
connect by prior org_id = porg_id;
--自定义函数,查找非顶级
create or replace function get_up_org_id(str_org_id in varchar2)
return varchar2 is
str_up_org_id varchar2(20);
begin
select org_id
into str_up_org_id
from (select org_id, porg_id, connect_by_root(org_id) root_name, level
from t_org_info a
where type = '02'
start with org_id = str_org_id
connect by prior porg_id = org_id
order by level desc)
where rownum = 1;
return str_up_org_id;
end get_up_org_id;
--查询语句
select org_id, get_up_org_id(org_id) up_org_id
from t_org_info
where type = '02';
Oracle 递归查询 自定义函数
最新推荐文章于 2024-04-03 15:33:05 发布