10-196 表関数で期間内の日付を出力

SQLパズル

dayTable
StartDay    EndDay
----------  ----------
2007/10/01  null
2007/10/02  2007/10/05
2007/10/03  null
2007/10/03  null
2007/10/20  2007/10/21
2007/12/31  2007/12/31

各行の、
StartDayからEndDayの期間の日付を全て出力する。

出力結果
day1
----------
2007/10/01
2007/10/02
2007/10/03
2007/10/04
2007/10/05
2007/10/20
2007/10/21
2007/12/31

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

データ作成スクリプト

create table dayTable(StartDay,EndDay) as
select to_date('2007/10/01','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/02','yyyy/mm/dd'),to_date('2007/10/05','yyyy/mm/dd') from dual union all
select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/20','yyyy/mm/dd'),to_date('2007/10/21','yyyy/mm/dd') from dual union all
select to_date('2007/12/31','yyyy/mm/dd'),to_date('2007/12/31','yyyy/mm/dd') from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select distinct StartDay
  from dayTable
 model
 partition by (RowNum as PID)
 dimension by (0 as Cnt)
 measures(StartDay,nvl(EndDay,StartDay) as EndDay)
 rules ITERATE (1000) UNTIL (StartDay[0]+ITERATION_NUMBER = EndDay[0])
 (StartDay[ITERATION_NUMBER]=StartDay[0]+ITERATION_NUMBER)
order by StartDay;

--■■■表関数を使う方法■■■
create or replace Package Pack10_196 Is
    type OutputDayType is table of date;
end;
/

create or replace function PrintOutputDayType return Pack10_196.OutputDayType PipeLined IS
begin
    for rec in (select StartDay,EndDay
                  from dayTable
                 order by StartDay) Loop

        if rec.EndDay is null then
            pipe row(rec.StartDay);
        else
            for I in 0..rec.EndDay-rec.StartDay Loop
                pipe row(rec.StartDay+I);
            end Loop;
        end if;
    end loop;
end;
/

sho err

create or replace view PrintOutputView(day1) as
select distinct *
  from table(PrintOutputDayType)
with read only;

select day1
  from PrintOutputView
order by day1;

解説

既定の型を戻す表関数の場合は、
type定義のDDLを1個減らせるようです。
といっても、代わりに列名定義用のviewが増えるみたいですが・・・

---