create type res_rec_t as object (x1 number, x2 number, res varchar2(50));
/
create type res_tab_t as table of res_rec_t;
/
create type src_rec_t as object (x1 number, x2 number, c varchar2(50), flag varchar2(50));
/

create or replace function f_superimpose(p_refcur sys_refcursor) return res_tab_t pipelined is
    l_res        res_rec_t;
    l_src        src_rec_t;
    l_curr_flag  varchar2(50);
begin
    fetch p_refcur into l_src;
    if p_refcur%NOTFOUND then 
        return;
    end if;
    
    l_res := res_rec_t(l_src.x1, l_src.x2, l_src.c);
    l_curr_flag := l_src.flag;

    loop
        fetch p_refcur into l_src;
        exit when p_refcur%NOTFOUND;
        
        -- разрыв
        if l_src.x1 - l_res.x2 > 1 then
            pipe row (l_res);
            l_res.x1 := l_res.x2 + 1;
            l_res.x2 := l_src.x1 - 1;
            l_res.res := '';
            pipe row (l_res);
            l_res.x1  := l_src.x1;
            l_res.x2  := l_src.x2;
            l_res.res := l_src.c;
            l_curr_flag := l_src.flag;
            continue;
        end if;
        
        -- касание
        if l_src.x1 - l_res.x2 = 1 then
            if l_src.c = l_res.res then
                l_res.x2  := l_src.x2;
                l_curr_flag := l_src.flag;
            else
                pipe row (l_res);
                l_res.x1  := l_src.x1;
                l_res.x2  := l_src.x2;
                l_res.res := l_src.c;
                l_curr_flag := l_src.flag;
            end if;
            continue;
        end if;

        -- пересечение
        if l_src.x1 - l_res.x2 < 1 then
            if l_curr_flag = 'src' then
                if l_src.x2 > l_res.x2 then
                    if l_src.c = l_res.res then
                        l_res.x2  := l_src.x2;
                        l_curr_flag := l_src.flag;
                    else
                        pipe row (l_res);
                        l_res.x1  := l_res.x2 + 1;
                        l_res.x2  := l_src.x2;
                        l_res.res := l_src.c;
                        l_curr_flag := l_src.flag;
                    end if;
                end if;
            elsif l_curr_flag = 'tgt' then
                if l_src.x2 >= l_res.x2 then
                    if l_src.c = l_res.res then
                        l_res.x2  := l_src.x2;
                        l_curr_flag := l_src.flag;
                    else
                        if l_src.x1 > l_res.x1 then
                            l_res.x2 := l_src.x1 - 1;
                            pipe row (l_res);
                        end if;
                        l_res.x1  := l_src.x1;
                        l_res.x2  := l_src.x2;
                        l_res.res := l_src.c;
                        l_curr_flag := l_src.flag;
                    end if;
                else
                    if l_src.c != l_res.res then
                        if l_src.x1 > l_res.x1 then
                            pipe row (res_rec_t(l_res.x1, l_src.x1 - 1, l_res.res));
                        end if;
                        pipe row (res_rec_t(l_src.x1, l_src.x2, l_src.c));
                        l_res.x1 := l_src.x2 + 1;
                    end if;
                end if;
            end if;
        end if;
    end loop;

    pipe row (l_res);
    close p_refcur;
end;
/

select * from table(f_superimpose(cursor(
with t (x1, x2, c, flag) as
(
select 1, 4, 'red', 'src' from dual
union all select 7, 10, 'yellow', 'src' from dual
union all select 13, 16, 'red', 'src' from dual
union all select 3, 7, 'black', 'tgt' from dual
union all select 9, 11, 'black', 'tgt' from dual
union all select 13, 14, 'black', 'tgt' from dual
union all select 16, 19, 'blue', 'tgt' from dual
union all select 18, 22, 'green', 'src' from dual
union all select 22, 25, 'black', 'tgt' from dual
union all select 26, 28, 'red', 'src' from dual
union all select 29, 30, 'red', 'tgt' from dual
union all select 32, 33, 'black', 'tgt' from dual
)
select src_rec_t(x1, x2, c, flag) from t order by x1, flag
)));