[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-01008:バインドされていない変数があります。

今日も今日とて、ORA-01008のエラーに悩まされる。 無慈悲に「バインドされていない変数があります。」ってメッセージは出してくれるんだけど、ビンゴの位置は教えてくれないわけで・・・ 何度目だろう・ …

OracleDataaccessでどハマリして、勉強になったとかじゃなくお客さんに迷惑かけたし、もう穴を掘って埋まりたい

みんなは、どうやってOracleDataaccessを筆頭に、複数バージョンが入り乱れるDLLを管理してるのか教えてください。 マジで。

devenvを使って、プロジェクトをコマンドラインからビルドする方法[VisualStudio]

今日も今日とて休日出勤\(^o^)/   現在、C#をメイン言語として使用しています。 で、当然というか、開発の区切りだったり、テストするためにビルドというものをやります。 このビルド、プロ …

ORACLEでORA-01861: リテラルが書式文字列と一致しません

ORA-01861: リテラルが書式文字列と一致しません このエラー、ぶっちゃけ、意味が分からないところでもある。 日付の指定時に発生してて、原因が判明するまでに結構回り道をしました。 ネットで検索し …

[ORACLE]IMPコマンドで、複数テーブルをインポートする

OracleでIMPコマンドでdumpファイルをインポートする時に、特定のテーブルだけインポートしたい時ってある。 後は、複数のテーブルを纏めてインポートする方法を説明します。 Oracleを使ってる …


カテゴリー