[Oracle] IN 절 파라미터 여러개 전달하는 방법 (바인드 변수)

오라클의 일반적인 쿼리문에서는 바인드 변수에 쉼표(,)를 추가하여 여러 개의 값을 IN 절에 전달해도 하나의 문자열로 인식하기 때문에 정상적으로 작동하지 않는다. IN 절에 여러 개의 파라미터(바인드 변수)를 전달하기 위해서는 동적 쿼리문을 사용해야 하지만, 동적 쿼리문을 사용하지 않아도 여러 개의 파라미터를 쿼리문에서 인식할 수 있도록 하는 다양한 방법이 존재한다. 대표적인 방법으로는 INSTR 함수는 사용하는 방법이고, 두 번째는 정규식 함수를 사용하여 문자열을 구분자로 쪼개서 IN 절에 입력할 수 있는 방법이다. 

 

목차 INSTR 함수를 사용하는 방법 정규식(REGEXP) 함수를 사용하는 방법

 

INSTR 함수를 사용하는 방법

WITH dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname FROM dual 
)

SELECT *
  FROM dept
 WHERE INSTR(',' || '20,30,40' || ',', ',' || deptno || ',') > 0

 

 

위의 예제는 dept 칼럼에서 20, 30, 40의 값을 조회하는 방법이다.

IN (20, 30, 40)과 동일한 결과라고 생각하면 된다.

 

조회할 코드의 길이가 일정하다면 INSTR( '20,30,40', deptno) > 0 이렇게 변경해도 된다.

문자열의 양쪽에 쉼표(,)를 붙인 이유는 입력한 값이 길이가 일정한 코드 값이 아닐 경우를 대비해서 이다.

 

정규식(REGEXP) 함수를 사용하는 방법

WITH dept AS (
  SELECT 10 deptno, 'ACCOUNTING' dname FROM dual UNION ALL
  SELECT 20 deptno, 'RESEARCH'   dname FROM dual UNION ALL
  SELECT 30 deptno, 'SALES'      dname FROM dual UNION ALL
  SELECT 40 deptno, 'OPERATIONS' dname FROM dual 
)

SELECT *
  FROM dept
 WHERE deptno IN ( SELECT REGEXP_SUBSTR('20,30,40', '[^,]+', 1, LEVEL)
                     FROM dual
                  CONNECT BY LEVEL <= REGEXP_COUNT('20,30,40', ',') + 1 )

 

 

위의 예제는 dept 칼럼에서 20, 30, 40의 값을 조회하는 방법이다.

첫 번째 예제에 비해서 조금 복잡해 보이지만 조금 더 IN 절에 가까운 방법이다.

 

정규식 함수(REGEXP_SUBSTR, REGEXP_COUNT)를 사용해서 오라클 11g 이상에서 작동한다.

 

[Oracle] 오라클 INSTR 함수 사용법 정리

[Oracle] 정규식 사용법 쉽게 설명 (REGEXP)