10-335 model句で欠番の順次使用

SQLパズル

fillSeqテーブル
ID  seq
--  ---
 1    2
 2    3
 3    4
 4    9

ID列を10まで補完する。Seq列は、未使用のseqを昇順に使用する。

出力結果
ID  seq
--  ---
 1    2
 2    3
 3    4
 4    9
 5    1
 6    5
 7    6
 8    7
 9    8
10   10

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

データ作成スクリプト

create table fillSeq(ID,seq) as
select 1,2 from dual union
select 2,3 from dual union
select 3,4 from dual union
select 4,9 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select *
  from fillSeq
 model
dimension by(ID)
measures(seq,0 as rn,0 as maySet)
rules iterate (10) (
rn[any]= Row_Number() over(order by seq),
maySet[iteration_number+1] = nvl(max(case when rn = seq
                                          then rn+1 end)[any],1),
seq[iteration_number+1] = case when seq[cv()] is present
                               then seq[cv()]
                               else maySet[cv()] end);

--■■■表関数を使う方法■■■
create or replace Package Pack10_335 Is
    type PrintRType is record(
    ID  fillSeq.ID%type,
    seq number(2));

    type PrintTypeSet is table of PrintRType;
end;
/

create or replace function PrintR10335Set return Pack10_335.PrintTypeSet PipeLined IS
    outR Pack10_335.PrintRType;
    cursor cur is select ID,seq from fillSeq;
    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;

    IsExist1 boolean;
    IsExist2 boolean;
begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;

    for I in 1..10 Loop
        IsExist1 := false;
        for J in 1..saveData.Count Loop
            if I = saveData(J).ID then
                IsExist1 := true;
            end if;
        end Loop;

        if IsExist1 = false then
            for willSetSeq in 1..10 Loop
                IsExist2 := false;
                for J in 1..saveData.Count Loop
                    if willSetSeq = saveData(J).seq then
                        IsExist2:= true;
                    end if;
                end Loop;

                if IsExist2 = false then
                    saveData(saveData.Count+1).ID:=I;
                    saveData(saveData.Count).seq:=willSetSeq;
                    exit;
                end if;
            end Loop;
        end if;
    end Loop;

    for P in 1..saveData.Count Loop
        outR.ID  := saveData(P).ID;
        outR.seq := saveData(P).seq;
        pipe row(outR);
    end Loop;
end;
/

sho err

select * from table(PrintR10335Set);

解説

変数を用意してループの中で使用可能である、model句が有効なケースですね。