カンマ区切りの文字列を取得した理由
こないだの続きです。
リスト形式のものをわざわざカンマ区切りの文字列にしたのは、SQLのIN句に適用したかったからでした。
ただ、今回手を加えるSQLの性質上、for文などでリストの個数だけIN句の部分を拡張するのは避けたかったっていう理由がありまして・・・
まぁ、ただでさえ500行を超えるSQLが動的に変わるとか、面倒くさすぎるし、後でメンテナンスも出来たもんじゃないってことですよ。
作ったその時は良いけど、絶対に後で後悔するのでｗ
必要な環境
自分は、Oracle 11g で確認しました。
ただ、Oracle 10g 以降なら使えるみたいです。
テーブルは、こんな感じで簡単に用意してみました。
主キーとかの定義も適当ｗ
IN句に引数を渡す
ちなみに、引き渡すパラメータは「b,c,d」という文字列。
通常であれば、COLUMN1が「2,3,4」のデータが取得できるはずです。
普通にIN句を記述してみる
ということで、まずは、普通にIN句を使った書き方。
SELECT
*
FROM
m_person
WHERE
m_person.column2 IN ('b','c','d')
;
まぁ、結果も普通に取得できます。
パラメータで引き渡してみる
次は、パラメータで引き渡してみます。
SELECT
*
FROM
m_person
WHERE
m_person.column2 IN (:test_parameter)
;
取得できません。
変数をそのままぶち込んでみる
変数をそのままぶち込むというのは、「’b,c,d’」という文字列がパラメータとして引き渡されている場合です。
SELECT
*
FROM
m_person
WHERE
m_person.column2 IN ('b,c,d')
;
取得できません。
まぁ、当然っちゃ当然ですね。
だって、「’b,c,d’」なんて文字列はどのレコードにも登録されてないですし・・・
REGEXP_SUBSTR関数を使う
これも、以前似たような関数を使ってました。
とりあえず、調べてみると、こんな感じで書くとのこと。
SELECT
regexp_substr(:test_parameter, '[^,]+', 1, LEVEL)
FROM
dual
CONNECT BY
regexp_substr(:test_parameter, '[^,]+', 1, LEVEL) IS NOT NULL
;
実行すると、引数が分割されて取得できます。
でもって、この結果をSELECT文のIN句に渡してやると、結果が取得できる！
SELECT
*
FROM
m_person
WHERE
m_person.column2 IN (
SELECT
regexp_substr(:test_parameter, '[^,]+', 1, LEVEL)
FROM
dual
CONNECT BY
regexp_substr(:test_parameter, '[^,]+', 1, LEVEL) IS NOT NULL
)
;
目的の結果が取得できた！
マジで、バインド変数での引き渡しを辞めて、SQL部分をループで動的に追加するのか・・・って絶望しかけてたけど、これでバインド変数でIN句に引き渡せる。
1箇所ぐらいならいいんだけど、同様に変数がリストで取得されていて、動的に項目数が変わってしまう処理が複数あったので、どうしてもSQLの動的な作成は避けたかった。
定数で定義できればこんなに悩まなかったんだけどなｗ
[…] […]