はじめに
PostgreSQLにはGENERATE_SERIESという数値や日付の連番を生成する関数がありますが、Oracle Databaseには、該当する関数がありません。しかし、PL/SQLの表関数(テーブル・ファンクション)の機能を使用することで、類似の機能を実装することは可能です。本記事はOracle9i以降(SE/EE問わず)に対応しています。動作確認はXE18c Windows版で実施しています。
表関数とは
表関数とは、PL/SQLのネスト表型の戻り値を取るストアド・ファンクションです。
ネスト表型というのが聞きなれないかもしれませんが、PL/SQLのコレクション(配列(的な)オブジェクト)の一種で、テーブルのようなデータ型だと思ってください。要は戻り値として仮想的なテーブルを戻すファンクションだとご理解ください。
表関数は、SELECT文のFROM句に指定することで、実際にSELECTを実行することが可能になります。
表関数の作り方
1. 戻り値となるネスト表型を作成する
ファンクションを作成する前に、ファンクションの戻り値となるネスト表型を作成する必要があります。
create type number_table as table of number;
/
Oracle DatabaseはPostgreSQLと同じくオブジェクト・リレーショナルデータベースです。CREATE TYPE文で型(クラスのようなもの)を作成することにより、その型の列やテーブルを作成したり、PL/SQLの変数の型として利用することができるようになります。多くの場合は複数列の型を作成して、そのコレクション(例文中の「table」)を作成しますが、今回は1列だけでいいので、データ型(例文中の最後の「number」)を直接指定しています。型の作成に関するSQL文は、最後は「/」を入れないと実行されません。
2. 表関数を作成する
まずサンプルソースを掲示します。一部の引数名はPL/SQLでは予約語だったため、引数名はGENERATE_SERIES関数と合わせ切れていません。
create or replace function generate_series(
first in number default 1,
last in number default 100,
step in number default 1
) return number_table pipelined
is
output number;
i number := 0;
begin
loop
output := first + i;
exit when output > last;
pipe row(output);
i := i + step;
end loop;
return;
end;
/
5行目の戻り値の型として、1.で作成したNUMBER_TABLE型を指定しています。
その次の「pipelined」は、この表関数をパイプライン表関数として作成する宣言となります。このオプションのない表関数の場合、戻り値の変数に戻したいデータをため込んで、最後にその変数をRETURNする形になります。その場合、戻したいデータが大量にあると、その分メモリを大量に消費し、すべてのデータの作成を待つので処理時間にも影響します。PIPELINEDオプションを指定し、8行目のPIPE ROW文で1行ずつ戻したいデータを出力することで、ワークとして必要なメモリ量を減らし、パフォーマンスを向上させることが可能です。
PL/SQLのファンクション作成時はRETURN文が必須です。例のようなパイプライン表関数の場合は、PIPE ROW文で既にデータを出力しているので、ただ「RETURN」とだけ記述します。
実行例
SQL> select * from table(generate_series(5,10));
COLUMN_VALUE
------------
5
6
7
8
9
10
6行が選択されました。
古いバージョンでは上記のようにTABLE関数の引数として表関数を指定する必要がありますが、直近のバージョンであれば、以下の様に、TABLE関数を通さずとも機能します。
SQL> select * from generate_series(5,10);
COLUMN_VALUE
------------
5
6
7
8
9
10
6行が選択されました。
DATE型対応版
GENERATE_SERIESはTIMESTAMP型にも対応しているので、DATE型版も作成してみます。NUMBER型版と同じ名前なので、どちらも利用したい場合は後述の様な感じでパッケージ化してください。
create type date_table as table of date;
/
create or replace function generate_series(
first in date default current_date,
last in date default last_day(current_date),
step in number default 1
) return date_table pipelined
is
output date;
i number := 0;
begin
loop
output := first + i;
exit when output > last;
pipe row(output);
i := i + step;
end loop;
return;
end;
/
SQL> select to_char(column_value, 'yyyy/mm/dd hh24:mi:ss') as column_value
2 from generate_series(to_date('19990101121314', 'yyyymmddhh24miss'),
3 to_date('19990102010203', 'yyyymmddhh24miss'), 1/24);
COLUMN_VALUE
--------------------------------------
1999/01/01 12:13:14
1999/01/01 13:13:14
1999/01/01 14:13:14
1999/01/01 15:13:14
1999/01/01 16:13:14
1999/01/01 17:13:14
1999/01/01 18:13:14
1999/01/01 19:13:14
1999/01/01 20:13:14
1999/01/01 21:13:14
1999/01/01 22:13:14
COLUMN_VALUE
--------------------------------------
1999/01/01 23:13:14
1999/01/02 00:13:14
13行が選択されました。
パッケージ対応
NUMBER型版とDATE型版の両方を使えるようにしました。このサンプルの様に、パッケージを使用すると、オーバーロード(引数仕様が異なる、同じ名前のプロシージャやファンクションを定義すること)が可能になります。もちろん、ファンクション名を変えていただいても構いません。
create or replace package generate_series as
type number_table is table of number;
type date_table is table of date;
function generate_series(
first in number default 1,
last in number default 100,
step in number default 1
) return number_table pipelined;
function generate_series(
first in date default sysdate,
last in date default last_day(sysdate),
step in number default 1
) return date_table pipelined;
end generate_series;
/
create or replace package body generate_series as
function generate_series(
first in number default 1,
last in number default 100,
step in number default 1
) return number_table pipelined
is
output number;
i number := 0;
begin
loop
output := first + i;
exit when output > last;
pipe row(output);
i := i + step;
end loop;
return;
end generate_series;
function generate_series(
first in date default sysdate,
last in date default last_day(sysdate),
step in number default 1
) return date_table pipelined
is
output date;
i number := 0;
begin
loop
output := first + i;
exit when output > last;
pipe row(output);
i := i + step;
end loop;
return;
end generate_series;
end generate_series;
/
SQLのみでの実装
最後に今までの説明を覆すような説明を入れますが、実は、再帰SQLを使用すれば、PL/SQLに頼らずSQLだけでも実現可能です。
再帰WITH句を使用
Oracle Database 11g R2以降であれば、次のようなSQLで対応が可能です。
SQL> with generate_series(col) as (
2 select 1 from dual
3 union all
4 select col + 1 from generate_series where col + 1 <= 5)
5 select col from generate_series;
COL
----------
1
2
3
4
5
SQL> with generate_series(col) as (
2 select sysdate from dual
3 union all
4 select col + 1 from generate_series where col + 1 < sysdate + 5)
5 select col from generate_series;
COL
--------
20-06-16
20-06-17
20-06-18
20-06-19
20-06-20
CONNECT BY句を使用
Oracle Database独自の再帰SQL構文になりますが、CONNECT BY句を使用しても実現可能です。こちらの方法であれば、再帰WITH句が使用できないバージョンでも利用可能です。
SQL> select level as col from dual connect by level <= 5;
COL
----------
1
2
3
4
5
SQL> select sysdate + level - 1 as col from dual connect by level <= 5;
COL
--------
20-06-16
20-06-17
20-06-18
20-06-19
20-06-20