ORACLE SQLでIN句に引数(パラメータ)を渡す方法 ホームページ制作 | 墨田区

ORACLE SQLでIN句に引数(パラメータ)を渡す方法

LINEで送る
Pocket

ORACLEのSQLでIN句に引数(パラメータ)を渡す方法です。

ORACLE SQLでIN句に引数(パラメータ)を渡す方法
PL/SQL の動的 SQL を使ってプログラミングすれば可能ですが、いちいちプログラムを書かかずに SELECT 文で確認したいときもあるものです。

やり方としては、カンマ区切り文字列を引数として用意し、それを分割して SELECT 文の IN 句に渡します。

例題を見ながら解説します。




【PR】マジか?!「アレ」してるLINEスタンプっていったい・・・


環境

・ORACLE 11g

ここでは ORACLE 11g ですが、10g 以上であれば何でも構いません。

例題で使うテーブルとデータです。
CREATE TABLE EMPLOYEES (
    ID                             NVARCHAR2(255) NOT NULL,
    NAME                           NVARCHAR2(255) NOT NULL,
    EMAIL                          NVARCHAR2(255),
    SEX                            NVARCHAR2(1),
    SALARY                         NUMBER,
    CREATED_AT                     DATE NOT NULL,
    UPDATED_AT                     DATE,
    CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);
INSERT INTO EMPLOYEES VALUES('001','hoge','hoge@example.com','1','30',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('002','piyo','piyo@example.com','2','50',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('003','fuga','fuga@example.com',null,null,to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('004','foo','foo@example.com','2','65',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('005','bar','bar@example.com','1','25',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('006','baz','baz@example.com','2','70',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));
INSERT INTO EMPLOYEES VALUES('007','saka','saka@example.com',null,'1000',to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'),to_date('2017/12/01 10:00:00', 'yyyy/MM/dd hh24:mi:ss'));

SELECT文のIN句に引数を渡す方法

まず NAME カラムが下記のものを出力することとしましょう。

'hoge,foo,baz'

当然、この SELECT 文では、結果は得られません。
SELECT * FROM EMPLOYEES
WHERE NAME IN ('hoge,foo,baz');

ただの文字列ですからね、期待する結果が得られないのは当然です。
hoge,foo,bazを検索
なので、カンマで区切った文字列を、個々の文字列に分割し、それを IN 句に渡すことにします。

ORACLE では REGEXP_SUBSTR (DB >=10g) という関数が用意されています。REGEXP_SUBSTR 関数は、正規表現パターンで文字列を検索できるように SUBSTR の機能を拡張したもので、これを使えば簡単に実現できます。

まず、このカンマ区切りの文字列を分割し、個々の文字列を行として与えるためのクエリを作成します。
SELECT REGEXP_SUBSTR('hoge,foo,baz', '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY REGEXP_SUBSTR('hoge,foo,baz', '[^,]+', 1, LEVEL) IS NOT NULL

REGEXP_SUBSTR を使う場合には CONNECT BY 句が必要になります。
REGEXP_SUBSTRにはCONNECT BY句が必要
このクエリは対象文字列からカンマを検索し、文字列として分割します。区切り文字が見つかった場合は、対象文字列を行として返してくれます。

これを SELECT 文に渡してやれば、目的の結果が得られます。
SELECT * FROM EMPLOYEES
WHERE NAME IN (
  SELECT REGEXP_SUBSTR('hoge,foo,baz', '[^,]+', 1, LEVEL) FROM DUAL
  CONNECT BY REGEXP_SUBSTR('hoge,foo,baz', '[^,]+', 1, LEVEL) IS NOT NULL
)

実行してみると・・・、
対象文字列からカンマを検索して文字列として分割する
おおお、やりましたーー^^

こんな感じでバインド変数でも対応できますよ。
SELECT * FROM EMPLOYEES
WHERE NAME IN (
  SELECT REGEXP_SUBSTR(:names, '[^,]+', 1, LEVEL) FROM DUAL
  CONNECT BY REGEXP_SUBSTR(:names, '[^,]+', 1, LEVEL) IS NOT NULL
)


参考サイト


まとめ

この方法は PL/SQL の CURSOR 文でも利用できます。動的 SQL を書くのが面倒だったらこの方法を利用してみてください。

ちょっと SQL 文が長くなるのが玉に瑕ですが、まぁ仕方ないかな。

使い方を忘れたらこのサイトを思い出して見に来てください。


おつかれさまでした。

LINEで送る
Pocket

この記事がお役に立ちましたら シェア をお願いいたします。

コメントを残す

コメント(必須)

お名前 (必須)
メールアドレス
(アドレスは公開されません)

Trackback URL