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

投稿日:

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

こないだの続きです。

リスト形式のものをわざわざカンマ区切りの文字列にしたのは、SQLのIN句に適用したかったからでした。

ただ、今回手を加えるSQLの性質上、for文などでリストの個数だけIN句の部分を拡張するのは避けたかったっていう理由がありまして・・・

まぁ、ただでさえ500行を超えるSQLが動的に変わるとか、面倒くさすぎるし、後でメンテナンスも出来たもんじゃないってことですよ。

作ったその時は良いけど、絶対に後で後悔するのでw

必要な環境

自分は、Oracle 11g で確認しました。

ただ、Oracle 10g 以降なら使えるみたいです。

テーブルは、こんな感じで簡単に用意してみました。

主キーとかの定義も適当w

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

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

 







-ORACLE, 開発メモ
-,


関連記事

[ORACLE]ORA-01950: 表領域’USERS’に対する権限がありません。のエラー対処法

  Oracle 12c R2(R1でも同様のエラーが発生)で ORA-01950: 表領域’USERS’に対する権限がありません のエラーが発生しました。 状況とし …

[ORACLE]ORA-00918:列の定義が未確定です・・・のエラーにならなかった

なんでエラーになるものをマージしてるんだ? と、怒られました。 一瞬、理解が出来ず・・・ で、エラーを再現してもらう・・・と。 あっさり発生。 早く修正しろって突っ返されました。 頭には「?」しか浮か …

コマンドプロンプトのFor文で、ファイルの一覧を表示する。

コマンドプロンプトの出力結果を引数として、別のコマンドを実行させる。 なんてことが出来ることは知ってるんだけど、どうやってやるのかは良く分からない。 おもむろにコマンドプロンプトを立ち上げて、以下のコ …

Oracle11gでパスワードの有効期限を変更する

先日のOra-28001ですが、他のユーザーも同様になってましたw 当然ですね・・・ 日本の中心付近で仕様変更と闘うSE日記Oracleで「Ora-28001:the password has exp …

WindowsのコマンドプロンプトでUnicodeを扱おうとして挫折した。

WindowsのコマンドプロンプトでUnicodeのファイル名を扱うためのバッチファイルをどうたらこうたら。 http://daily-misc.blogspot.jp/2014/12/windows …


カテゴリー