Oracle 递归查询 自定义函数

--查找顶级
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';