しかし、PL/SQLでは”CSVを分解する”という機能は提供されていません。
DBMS_UTILITYパッケージにCOMMA_TO_TABLEというカンマ区切りテキストを分解して結合配列（PL/SQL表）に格納するプロシージャがありますが、これはOracleのオブジェクト名のリストを分解するためのもののようで、CSVを扱うには明らかに機能不足です。
ということで、CSVを分解する場合は、いつもPL/SQLでゴリゴリとロジックを書いているのですが、どうせなら再利用可能な形にしておこうと思い、汎用的なプロシージャを作成してみました。
CREATE OR REPLACE PROCEDURE CSV_TO_TABLE(
csv IN VARCHAR2, -- CSV文字列
csv_count OUT PLS_INTEGER, -- CSVカウント
csv_table OUT DBMS_UTILITY.MAXNAME_ARRAY -- CSVテーブル
)
IS
SEPARATOR CHAR(1) := ','; -- セパレータ
QUOTE CHAR(1) := '"'; -- 引用符
curpos PLS_INTEGER := 1; -- 現在位置
endpos PLS_INTEGER := LENGTH(csv); -- 終端位置
seppos PLS_INTEGER; -- セパレータ位置
quot1pos PLS_INTEGER; -- 引用符位置1
quot2pos PLS_INTEGER; -- 引用符位置2
tmp_csv_table DBMS_UTILITY.MAXNAME_ARRAY; -- CSVテーブル(temp)
CSV_IS_NULL EXCEPTION; -- CSVがNULL
INVALID_CSV EXCEPTION; -- CSVが不正
BEGIN
-- CSVが空の場合、エラー
IF csv IS NULL THEN
RAISE CSV_IS_NULL;
END IF;
csv_count := 0;
-- 終端位置に達するまで繰り返す
WHILE curpos <= endpos LOOP
-- セパレータ、引用符を探す
seppos := INSTR( csv, SEPARATOR, curpos, 1 );
quot1pos := INSTR( csv, QUOTE, curpos, 1 );
quot2pos := INSTR( csv, QUOTE, curpos, 2 );
-- 引用符がある場合
IF quot1pos > 0 AND ( seppos = 0 OR quot1pos < seppos ) THEN
-- 引用符で始まっていない場合、エラー
IF quot1pos > curpos THEN
RAISE INVALID_CSV;
END IF;
-- 終端引用符が見つかるまで繰り返す
WHILE quot2pos < endpos LOOP
-- 終端引用符がない場合、エラー
IF quot2pos = 0 THEN
RAISE INVALID_CSV;
END IF;
-- 引用符が連続している場合
IF SUBSTR( csv, quot2pos + 1, 1 ) = QUOTE THEN
-- 終端位置に達した場合、エラー
IF quot2pos + 1 = endpos THEN
RAISE INVALID_CSV;
END IF;
-- 次の引用符を探す
quot2pos := INSTR( csv, QUOTE, quot2pos + 2, 1 );
-- 引用符が連続していない場合
ELSE
-- 引用符の後ろがセパレータでない場合、エラー
IF SUBSTR( csv, quot2pos + 1, 1 ) != SEPARATOR THEN
RAISE INVALID_CSV;
END IF;
-- ループを抜ける
EXIT;
END IF;
END LOOP;
-- テーブルに値をセットする
csv_count := csv_count + 1;
IF quot1pos = quot2pos - 1 THEN
tmp_csv_table( csv_count ) := NULL;
ELSE
tmp_csv_table( csv_count ) :=
REPLACE(
SUBSTR( csv, quot1pos + 1, quot2pos - quot1pos - 1 ),
QUOTE || QUOTE,
QUOTE
);
END IF;
curpos := quot2pos + 2;
-- 引用符がない場合
ELSE
-- テーブルに値をセットする
csv_count := csv_count + 1;
IF seppos = 0 THEN
tmp_csv_table( csv_count ) := SUBSTR( csv, curpos, endpos - curpos + 1 );
curpos := endpos + 1;
ELSE
tmp_csv_table( csv_count ) := SUBSTR( csv, curpos, seppos - curpos );
curpos := seppos + 1;
END IF;
END IF;
END LOOP;
-- 終端文字がセパレータの場合、テーブルに空値をセットする
IF SUBSTR( csv, endpos, 1 ) = SEPARATOR THEN
csv_count := csv_count + 1;
tmp_csv_table( csv_count ) := NULL;
END IF;
csv_table := tmp_csv_table;
EXCEPTION
WHEN CSV_IS_NULL THEN
csv_count := -1;
WHEN INVALID_CSV THEN
csv_count := -2;
END;
/
DBMS_UTILITYパッケージにCOMMA_TO_TABLEと同じようなインターフェースにしてみました。
CSVを渡すと、それを分解格納した結合配列（PL/SQL表）と要素数を返します。
一応、
・空値
・引用符で括られた値
・文字としてのカンマ
・引用符を含んだ値
にも対応しています。
CSVが空だったり、引用符の対応が不正だったりすると要素数にマイナスの値を返すようにしています。
また、宣言部のSEPARATOR、QUOTEの値を変えれば、タブ区切りテキストなどにも対応することができます。
このプロシージャをテストするために以下のような簡単なプロシージャを作成し、
CREATE OR REPLACE PROCEDURE TEST_CSV_TO_TABLE (
csv IN VARCHAR2 -- CSV文字列
)
IS
csv_table DBMS_UTILITY.MAXNAME_ARRAY; -- CSV文字列を分解格納したテーブル
csv_count PLS_INTEGER; -- CSV文字列を分解格納したテーブルの要素数
BEGIN
CSV_TO_TABLE( csv, csv_count, csv_table );
FOR i IN 1 .. csv_count loop
DBMS_OUTPUT.PUT_LINE( i || '番目の要素： ' || csv_table( i ) );
end loop;
END;
/
'abc,,"abc","a,b,c","""a"",""b"",""c""","a b c"'というCSVを入力して実行してみると、以下のようにCSVを分解した結果が得られます。
SQL> call TEST_CSV_TO_TABLE('abc,,"abc","a,b,c","""a"",""b"",""c""","a b c"');
1番目の要素： abc
2番目の要素：
3番目の要素： abc
4番目の要素： a,b,c
5番目の要素： "a","b","c"
6番目の要素： a b c
コールが完了しました。
空値、引用符で括られた値、文字としてのカンマ、引用符を含んだ値もうまく処理されています。
このようなプロシージャを作っておくと、あとは何も考えなくともCSVを分解してくれますので結構便利かと思います。