[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, 開発メモ
-,


関連記事

[VB.net]TextBoxで、マウスクリック(1回目)だけ、内容を全選択させる

結構前に書いた記事 日本の中心付近で仕様変更と闘うSE日記[C#]TextBoxで、マウスクリック(1回目)だけ、内容を全選択させるhttp://ht-jp.net/blog/pc/c/c-textb …

no image

同じ失敗と、関連する失敗を繰り返さない方法

実際、仕事をしていると軽微なミスって言うのは少なからずあります。 日本の中心付近で仕様変更と闘うSE日記  1 Userデグレをぶちかましたので、開発作業を1週間以上止められた話。http: …

Visual Studio 2008 が重い件について(暫定対応)

現象 とにかく重い。 重いと一言で言うけど、重いという内容は色々含んではいる。 起動が遅い スクロールすらカク付く 右クリックのコンテキストメニューが表示されるまで数秒待たされる 定義の移動に時間がか …

「別のプログラムがこのフォルダーまたはファイルを開いているので、操作を完了できません。」と表示された場合の対処法:Windows7

今日は2017年秋期の情報処理試験でしたが、受験されたみなさまいかがでしたでしょうか? ろくすっぽどころか、参考書すら買ってない状態だけど、情報処理試験の午前だけは受けに行く(起きれたら)。 午前は択 …

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

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


カテゴリー