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

2018.01.26 2024.08.20

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

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

PL/SQL の動的 SQL を使ってプログラミングすれば可能ですが、いちいちプログラムを書かかずに SELECT 文で確認したいときもあるものです。

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

ここでは OracleのSQLでIN句に引数(パラメータ)を渡す方法 をサンプル付きで解説します。

1 環境
2 SELECT文のIN句に引数を渡す方法
3 参考サイト
4 まとめ

環境

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',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('002','piyo','piyo@example.com','2','50',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('003','fuga','fuga@example.com',null,null,sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('004','foo','foo@example.com','2','65',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('005','bar','bar@example.com','1','25',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('006','baz','baz@example.com','2','70',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('007','saka','saka@example.com',null,'1000',sysdate,sysdate);

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
)

参考サイト

・How to split comma separated string and pass to IN clause of select statement Oracle ADF Illusions Blog

まとめ

OracleのSQLでIN句に引数(パラメータ)を渡す方法を紹介しました。

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

ちょっと SQL 文が長くなるのが玉に瑕ですが、まぁ仕方ないかな。使い方を忘れたらこのサイトを思い出して見に来てください。

おつかれさまでした。