-- получить все возможные зависимости
with tmp1 as (select l.id_ref1, l.id_ref2, l.type
                from links l
               where l.id_ref2 is not null
              connect by nocycle l.id_ref1 = prior l.id_ref2
                              or l.id_ref1 = prior l.id_ref1
                              or l.id_ref2 = prior l.id_ref1
                              or l.id_ref2 = prior l.id_ref2
               start with l.id_ref1 = 3 -- например
               group by l.id_ref1, l.id_ref2, l.type),

-- перевернуть ссылки в одну сторону
     tmp2 as (select l.id_ref1, l.id_ref2
                from tmp1 t
               where l.type = 0
               union
              select l.id_ref2, l.id_ref1
                from tmp1 t
               where l.type = 1)

-- тут должно быть вычисление guid-а 1-уровня в соответствии с каждым связанным элементом
select t.*
  from tmp2 t

  procedure testFunc(pId in number) as
    lMainGuid varchar2(200);
  begin
    -- получаю весь список ссылок и переворачиваю их в одну сторону
    for rec in (with tmp as (select decode(l.type, 1, l.id_ref2, l.id_ref1) id_ref1,
                                    decode(l.type, 1, l.id_ref1, l.id_ref2) id_ref2
                               from links l
                              where l.id_ref2 is not null
                            connect by nocycle l.id_ref1 = prior l.id_ref2
                                            or l.id_ref1 = prior l.id_ref1
                                            or l.id_ref2 = prior l.id_ref1
                                            or l.id_ref2 = prior l.id_ref2
                              start with l.id_ref1 = pId or l.id_ref2 = pId
                              group by decode(l.type, 1, l.id_ref2, l.id_ref1),
                                       decode(l.type, 1, l.id_ref1, l.id_ref2))

                select l.id_ref1,
                       l.id_ref2,
                       dense_rank() over(partition by l.id_ref1 order by l.lvl desc) isLast -- беру только корневые записи
                  from (-- нахожу родителей для каждого элемента
                        select connect_by_root l.id_ref1 id_ref1,
                               l.id_ref2,
                               level lvl
                          from tmp l
                       connect by nocycle l.id_ref1 = prior l.id_ref2) l
                 order by l.id_ref2 -- если для одной записи несколько корневых, беру первое
               )
    loop
      if rec.isLast = 1 then
        -- делаем первый родительский guid как main
        if lMainGuid is null then
          update recs r
             set r.main_guid = r.guid
           where r.id = rec.id_ref2
          returning r.guid into lMainGuid;
        -- все остальные родительские guid'ы отмечаем первым выбранным
        else
          update recs r
             set r.main_guid = lMainGuid
           where r.id = rec.id_ref2
             and (r.main_guid is null or r.main_guid != lMainGuid);
        end if;

        -- остальные записи отмечаем родительским guid'ом
        update recs r
           set r.main_guid = lMainGuid
         where r.id = rec.id_ref1
           and (r.main_guid is null or r.main_guid != lMainGuid);
      end if;
    end loop;

    -- если записей в таблице links нет
    if lMainGuid is null then
      update recs r
         set r.main_guid = r.guid
       where r.id = pId
         and (r.main_guid is null or r.main_guid != r.guid);  
    end if;
  end;