with base_table as

(

    select

        --'38,27,65,102,43,10,82,3,25,67,33,88' target

        '38,27,43,9,3,82,10' target

    from    dual

)

 

, recur (target,cnt,head,kind,maxlevel,ord) as

(

select

    target,0 cnt,'X',1 , length(target) - length(replace(target,',','')),0

from    base_table

union all

select

    case when a.kind = 2 or target = replace(target,',') then

        a.target

    else

        (

            select

                case

                    when dvd.lvl = 1 then

                        listagg(

                            case when to_number(substrb(a.target,1,instr(a.target,',')-1)) >= to_number(substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)) then

                                substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)

                            end

                            ,',') within group (order by column_value)

                    when dvd.lvl = 2 then

                        substrb(a.target,1,instr(a.target,',')-1)

                    else

                        listagg(

                            case when to_number(substrb(a.target,1,instr(a.target,',')-1)) < to_number(substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)) then

                                substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)

                            end

                            ,',') within group (order by column_value)

                end

            from    table(select collect(level) from dual connect by level <= length(a.target) - length(replace(a.target,',','')) )

        )

    end target

    , a.cnt + 1

    , substrb(a.target,1,instr(a.target,',')-1) head

    , case when a.kind = 2 or target = replace(target,',') then 2 else dvd.lvl end

    , maxlevel

    , ord * 3 + case when a.kind = 2 or target = replace(target,',') then 2 else dvd.lvl end

from    recur a

        , (

                    select

                        level lvl

                    from    dual

                    connect by level < 4

        ) dvd

where   a.cnt < a.maxlevel

and     lvl < (case when a.kind = 2 or target = replace(target,',') then 2 else 4 end )

and

    case when a.kind = 2 or target = replace(target,',') then

        a.target

    else

        (

            select

                case

                    when dvd.lvl = 1 then

                        listagg(

                            case when to_number(substrb(a.target,1,instr(a.target,',')-1)) >= to_number(substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)) then

                                substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)

                            end

                            ,',') within group (order by column_value)

                    when dvd.lvl = 2 then

                        substrb(a.target,1,instr(a.target,',')-1)

                    else

                        listagg(

                            case when to_number(substrb(a.target,1,instr(a.target,',')-1)) < to_number(substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)) then

                                substr(a.target, instr(a.target||',',',',1,column_value) +1 , instr(a.target||',',',',1,column_value + 1) - instr(a.target||',',',',1,column_value) - 1)

                            end

                            ,',') within group (order by column_value)

                end

            from    table(select collect(level) from dual connect by level <= length(a.target) - length(replace(a.target,',','')) )

        )

    end is not null

 

)

select

    a.cnt

    , a.target

from

    (

        select

            a.cnt

            , listagg(a.target,',') within group ( order by a.cnt,a.ord) target

            , lag(listagg(a.target,',') within group ( order by a.cnt,a.ord)) over (order by a.cnt) pre_target

        from

            (

                select

                    a.target

                    ,a.cnt

                    ,a.head

                    ,a.kind

                    ,a.ord

                    , max(a.head) over (partition by a.cnt) maxhead

                from    recur a

            ) a

        where   a.maxhead is not null

        group by

            a.cnt

    ) a

 

 

recursive-with를 사용하여 재귀호출을 흉내냈습니다.

 

 recursive-with는 계층구조 뿐 아니라 일반적인 점화식에 쓰일 수 있고

이와 같이 재귀호출 구조에도 사용할 수 있습니다.(트리 탐색도 재귀적이고...)