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

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

こないだの続きです。

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

[C#]リストをカンマ区切りの文字列で出力する

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

リストの項目をカンマ区切りで出力したい卵が先か鶏が先か～って話じゃないんですが、リストで出力した内容をカンマ区切りのデータにする必要が出てきたので、調べました。List<string> items = new List<string>(){"a","b","c",...};string str = String.Join(",",items);これで「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以降で使えるらしいです。それ以前のバージョンでは使えなさそうなので、replaceとかで頑張るしかなさそう・・・改行コードがあると何が問題なのか例えばこんなSQLselect 1 || chr(13) || chr(10) || 2 || chr(13) || chr(10) || 3 from dual;出力結果はこんな感じになる。実行結果は改行されていないように見え...

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

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

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