7-30 空き番号を取得その3

SQLパズル

以下のテーブルから空き番号を取得する。

No
--
10
15
30

出力結果
空き
----
  11
  12
  13
  14
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29

データ作成スクリプト

create table NoTable3(No number(2));

insert into NoTable3 values(10);
insert into NoTable3 values(15);
insert into NoTable3 values(30);
commit;

SQL

--■■■1からの連番を取得する方法■■■
select 空き候補 as 空き
from(select RowNum as 空き候補
     from dual
     connect by Level < (select max(No) from NoTable3)
     minus select No from NoTable3)
where 空き候補 > (select min(No) from NoTable3)
order by 空き;

--■■■植木算の考え方を使う方法■■■
select (select min(No) from NoTable3)+RowNum as 空き
from dual
connect by Level <= (select max(No)-min(No) from NoTable3)
minus select No from NoTable3
order by 空き;

--■■■表関数を使う方法■■■
create or replace Package Pack07_30 Is
    type PrintType is record(
    No NoTable3.No%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function Print07_30 return Pack07_30.PrintTypeSet PipeLined IS
    outR Pack07_30.PrintType;
begin
    for rec in (select No+1 as staNo,
                Lead(No-1,1,No) over(order by No) as EndNo
                  from NoTable3) Loop
        for I in rec.staNo..rec.EndNo Loop
            outR.No  := I;
            pipe row(outR);
        end Loop;
    end Loop;
end;
/

sho err

select * from table(Print07_30);

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(No,LeadNo) as(
select No+1,LeadNo
from (select No,Lead(No) over(order by No) as LeadNo
      from NoTable3)
 where No+1 < LeadNo
union all
select No+1,LeadNo from rec
 where No+1 < LeadNo)
select * from rec order by No;

解説

1からの連番を取得する方法では、
1から、Noの最大値-1、までの中間テーブルを作って、空きを取得してます。

7-28 空き番号を取得その1
7-29 空き番号を取得その2

---