10-288 木のIDと節のIDのセットで識別

SQLパズル

EmuNocyT
C1  C2
--  --
 1   2
 1   3
 2   5
 3   7
 7   1

下記のSQLをOracle9iで模倣する。

select C1,C2,sys_connect_by_path(C1,'/') as Path
  from EmuNocyT
connect by nocycle C1 = prior C2
order by C1,C2,Path;

出力結果
C1  C2  Path
--  --  ----------
 1   2  /1
 1   2  /1/3/7/1
 1   2  /3/7/1
 1   2  /7/1
 1   3  /1
 1   3  /3/7/1
 1   3  /7/1
 2   5  /1/2
 2   5  /1/3/7/1/2
 2   5  /2
 2   5  /3/7/1/2
 2   5  /7/1/2
 3   7  /1/3
 3   7  /3
 3   7  /7/1/3
 7   1  /1/3/7
 7   1  /3/7
 7   1  /7

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table EmuNocyT(
C1 char,
C2 char);

insert into EmuNocyT values(1,2);
insert into EmuNocyT values(1,3);
insert into EmuNocyT values(2,5);
insert into EmuNocyT values(3,7);
insert into EmuNocyT values(7,1);
commit;

SQL

--■■■表関数を使わない方法■■■
col Path for a20

select C1,C2,min(Path) as Path
from (select sys_connect_by_path(C1,'/') AS Path,C1,C2,
      connect_by_root RowID as treeID,RowID as nodeID
        from EmuNocyT
      connect by C1 = prior C2
             and Level <=50
             and prior dbms_random.value != 1)
group by treeID,nodeID,C1,C2
order by C1,C2,Path;

--■■■表関数を使う方法■■■
create or replace Package Pack10_288 Is
    type PrintType is record(
    C1 EmuNocyT.C1%type,
    C2 EmuNocyT.C1%type,
    Path varchar2(30));

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintR return Pack10_288.PrintTypeSet PipeLined IS
    outR Pack10_288.PrintType;
    cursor cur is select C1,C2,to_char(RowNum,'fm0009') as Row_ID from EmuNocyT;
    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;

    type InfoDef is record(
        RowData  cur%rowType,
        RootData cur%rowType, --Is_root用
        LV pls_Integer,
        RowIDList varchar2(1000),
        sysConn   varchar2(1000), -- sys_connect_by_path用
        IsLeaf boolean, -- Isleaf用
        IsCycle boolean -- IsCycle用
    );

    stackP binary_integer := 0;
    type StackClass is table of InfoDef index by binary_integer;
    st StackClass;
    priInfo  InfoDef;
    willPush InfoDef;
    LoopChk pls_Integer :=0;

    procedure push(pInfoDef InfoDef) is
    begin
        st(stackP) := pInfoDef;
        stackP := stackP+1;
    end;

    function pop return InfoDef is
    begin
        stackP := stackP-1;
        return st(stackP);
    end;

    function IsEmpty return boolean is
    begin
        return stackP = 0;
    end;

begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;
--  for rec in cur Loop
--      saveData(cur%rowCount) := rec;
--  end Loop;

    --Start With句
    for i in 1..saveData.Count Loop
        willPush.RowData.C1 := saveData(i).C1;
        willPush.RowData.C2 := saveData(i).C2;
        willPush.RowIDList  := saveData(i).Row_ID;
        willPush.sysConn    := saveData(i).C1;
        willPush.LV := 1;

        push(willPush);
    end Loop;

    while (IsEmpty = false) Loop
        priInfo := pop();

        -- connect by句
        for i in 1..saveData.Count Loop
            if  (priInfo.RowData.C2 = saveData(i).C1) then

                -- 未訪問ノードならpush (nocycle)
                if instr(priInfo.RowIDList,saveData(i).Row_ID) = 0 then
                    willPush.RowData.C1 := saveData(i).C1;
                    willPush.RowData.C2 := saveData(i).C2;
                    willPush.RowIDList  := priInfo.RowIDList || ',' || saveData(i).Row_ID;
                    willPush.LV         := priInfo.LV+1;
                    willPush.sysConn    := priInfo.sysConn || '/' || saveData(i).C1;

                    push(willPush);
                end if;
            end if;
        end Loop;

        outR.C1   := priInfo.RowData.C1;
        outR.C2   := priInfo.RowData.C2;
        outR.Path := priInfo.sysConn;
        pipe row(outR);
    end Loop;
end;
/

sho err

select C1,C2,Path from table(PrintR)
order by C1,C2,Path;

解説

木のIDと節のIDのセットで識別してます。
connect_by_rootをOracle9iで代用するのであれば、
sys_connect_by_pathの先頭を取得して下さい :-)

8-43 Oracle9iでnocycleを模倣
PL/SQL5 階層問い合わせを模倣(nocycle対応)