簡単な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 神奈川     横浜市