ストアドプロシージャ(PL/SQL)で区切り文字

PL/SQL や Transact-SQL は、データベースサーバ上でプログラムを実行できる機能として理解していましたが、今まで使用したことはありませんでした。

これまでは、SQL Server, Oracle, MySQL, PostgreSQL といろいろなデータベース(DB)は使ってきましたが、負荷の高いクエリでそんなに悩むことがなかったからでしょうか。

大型の業務系システムや、履歴データなどを集計するバッチ処理などでは使われる機会が多そうですね。

今回は初めて Oracle の PL/SQL を使ってプログラムを書く機会があったのですが、一般的なプログラム言語と違って便利な関数が少ないのは仕方ないところです。
(メインはデータベースの操作ですからね)

その中でも、見つけれなかった関数は、とある区切り文字でセパレートされた文字列を区切り文字単位で抜き出すもの。例えばこんな文字列です。

[ハイフン区切り]
AAA-BBB-CCC
[アンダーバー区切り]
AAA_BBB_CCC
[カンマ区切り]
AAA,BBB,CCC

[ハイフン区切り]

AAA-BBB-CCC

[アンダーバー区切り]

AAA_BBB_CCC

[カンマ区切り]

AAA,BBB,CCC

PL/SQL には、ある指定した文字が何文字目に含まれているかを調べる INSTR という関数と、文字列を指定した部分だけ抜き出す SUBSTR という関数があるので、これらを利用してみます。

変数は declare のブロックで宣言してあるとします。また、ここでのプログラムの都合上、文字列の区切り文字(デリミタ delimiter)を文字列の最後にも付けます。もっといい方法があればトラックバックなどで教えて下さい。

vWord := 'AAA-BBB-CCC';
vWord := vWord || '-';
iPosStart := 1;
iPosEnd := INSTR(vWord, '1', iPosStart);
WHILE iPosEnd <> 0 LOOP
vTmpWord := SUBSTR(vWord, iPosStart, iPosEnd - iPosStart);
-- (処理例)
INSERT INTO table (word) VALUES(vTmpWord);
iPosStart := iPosEnd + 1;
iPosEnd := INSTR(vWord, '1', iPosStart);
END LOOP;