[ORACLE]SQLのIN句にパラメータを適用する方法 |

カンマ区切りの文字列を取得した理由

こないだの続きです。

http://ht-jp.net/blog/pc/c/c-string-join

リストの項目をカンマ区切りで出力したい卵が先か鶏が先か～って話じゃないんですが、リストで出力した内容をカンマ区切りのデータにする必要が出てきたので、調べました。List<string> items = new List<string>(){"a","b","c...

リスト形式のものをわざわざカンマ区切りの文字列にしたのは、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関数を使う

これも、以前似たような関数を使ってました。

日本の中心付近で仕様変更と闘うSE日記

[Oracle]regexp_replaceで、改行コード・タブとかのCSV出力時に邪魔になるものを消す

http://ht-jp.net/blog/pc/dev-memo/ora-recexp-replace

通常、スペースを消したい時とかなら、replaceとかtrimとかで一括で処理してたんだけど、CSVに出力したい時に、複数の処理を纏めるのがスゲェ面倒くさかった。それがregexp_replaceで一括処理出来るって分かった。Oracle10g以降で使えるらしいです。それ以前のバージ...

とりあえず、調べてみると、こんな感じで書くとのこと。

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の動的な作成は避けたかった。

定数で定義できればこんなに悩まなかったんだけどなｗ