簡単なSQLで多行を生成する
SQLだけで多行を生成する方法はいくつかあります。
知っていると、ちょっとしたデータを作ったり、簡易的なマスターとして主表とJOINさせたりできるので便利です。
DUAL表とUNION ALLを組み合わせる方法や、CONNECT BY句とLEVEL疑似列を使う方法が一般的に知られています。
表関数とSYS.ODCIVARCHAR2LISTを使う
SYS.ODCIVCARCHAR2LISTのパラメータに複数の文字列を指定すると簡単に複数行のデータが作成できます。
SQL> L
1 select column_value
2 from table(sys.odcivarchar2list(
3 'test1',
4 'test2',
5 'test3'
6* ))
SQL> /
COLUMN_VALUE
------------------------------------
test1
test2
test3
複数カラムにする場合は、SUBSTRなどでCOLUMN_VALUEを分割する方法があります。
SQL> L
1 select
2 substr(column_value, 1, 4) name,
3 substr(column_value, 5, 1) no
4 from table(sys.odcivarchar2list(
5 'test1',
6 'test2',
7 'test3'
8* ))
SQL> /
NAME NO
---------------- ----
test 1
test 2
test 3
REGEXP_SUBSTRでカンマ区切りデータを分割
この方法を拡張し以下のようなカンマ区切り（CSV形式）のデータから複数カラムを出力する方法を考えてみます。
11,埼玉県,さいたま市
12,千葉県,千葉市
13,東京都,東京
14,神奈川県,横浜市
カンマの位置がデータによって異なるので単純なSUBSTRでは分割できません。
INSTR関数と組み合わせればできますがとても複雑になります。
そこで、REGEXP_SUBSTRを使って分割してみます。
構文は以下の通りです。
REGEXP_SUBSTR(文字列, 正規表現, 開始位置, 順序)
文字列 ：対象となる文字列
正規表現：切り出す文字列と合致させる正規表現
開始位置：検索対象の開始位置（１は先頭）
順序 ：正規表現が合致した順番
例を見てみます。
SQL> L
1 select
2 regexp_substr('abc123def456', '[0-9]+', 1, 1) N1,
3 regexp_substr('abc123def456', '[0-9]+', 1, 2) N2
4* from dual
SQL> /
N1 N2
--- ---
123 456
正規表現 [0-9]+ は0から9の数字からなる1文字以上の文字列なので、1番目に合致するデータは1232番目に合致するデータは456となります。
これを利用して、カンマ区切りのデータを取り出すにはどうしたらよいでしょう。
厳密には値の中にカンマ、改行などが含まれていたり、NULLデータを表すために、カンマが連続したりするデータに対応しなければいけませんが、簡易的に使用するだけなら、 [^,]+ で分割できそうです。
正規表現の意味は「カンマ以外文字の１文字以上の連続」となります。
切り出したデータをCASTを使ってデータ型も定義した最終形は以下のようになります。
SQL> L
1 select
2 cast(regexp_substr(v, '[^,]+', 1, 1) as NUMBER(2)) 都道府県番号,
3 cast(regexp_substr(v, '[^,]+', 1, 2) as VARCHAR2(10)) 都道府県名,
4 cast(regexp_substr(v, '[^,]+', 1, 3) as VARCHAR2(20)) 都道府県庁所在地
5 from (
6 select column_value v
7 from table(sys.odcivarchar2list(
8 '11,埼玉県,さいたま市',
9 '12,千葉県,千葉市',
10 '13,東京都,東京',
11 '14,神奈川県,横浜市'
12 ))
13* )
SQL> /
都道府県番号 都道府県名 都道府県庁所在地
------------ ---------- --------------------
11 埼玉県 さいたま市
12 千葉県 千葉市
13 東京都 東京
14 神奈川 横浜市