[Oracle|오라클] IN 파라미터(parameter)에 값(value)을 여러개(muti) 동적 할당하기

오라클 11g 이상이면 REGEXP(정규식) 함수를 이용하여 손쉽게 가능하나 그 이하 버전에서는 INSTR 함수를 이용하여 비슷한 효과를 얻을 수 있다.

□ 방법 1. (REGEXP 이용) - Oracle 11g

--가상 테이블
WITH TEST_TABLE AS (
    SELECT '고구려' COUNTRY, '1대' ST, '동명성왕'   KING_NM FROM DUAL UNION ALL
    SELECT '고구려' COUNTRY, '3대' ST, '대무신왕'   KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '1대' ST, '온조왕'     KING_NM FROM DUAL UNION ALL
    SELECT '고구려' COUNTRY, '2대' ST, '유리왕'     KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '3대' ST, '기루왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '2대' ST, '남해왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '1대' ST, '박혁거세'   KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '2대' ST, '다루왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '3대' ST, '유리이사금' KING_NM FROM DUAL
)

--조회 쿼리
SELECT *
  FROM TEST_TABLE
 WHERE COUNTRY IN (
                    SELECT REGEXP_SUBSTR ( '백제/신라', '[^/]+', 1, LEVEL )
                      FROM DUAL
                    CONNECT BY LEVEL <= REGEXP_COUNT ( '백제/신라', '/' ) + 1
                   )
 ORDER BY COUNTRY, ST

- REGEXP_SUBSTR 함수를 이용하여 파라미터(백제/신라)의 구분자(/)를 이용하여 값을 자른다.

  

  REG_COUNT 함수를 이용하여 파라미터(백제/신라))의 구분자(/) 개수를 가져온다.

 

□ 방법 2. (INSTR 이용)

--가상 테이블
WITH TEST_TABLE AS (
    SELECT '고구려' COUNTRY, '1대' ST, '동명성왕'   KING_NM FROM DUAL UNION ALL
    SELECT '고구려' COUNTRY, '3대' ST, '대무신왕'   KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '1대' ST, '온조왕'     KING_NM FROM DUAL UNION ALL
    SELECT '고구려' COUNTRY, '2대' ST, '유리왕'     KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '3대' ST, '기루왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '2대' ST, '남해왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '1대' ST, '박혁거세'   KING_NM FROM DUAL UNION ALL
    SELECT '백제'   COUNTRY, '2대' ST, '다루왕'     KING_NM FROM DUAL UNION ALL
    SELECT '신라'   COUNTRY, '3대' ST, '유리이사금' KING_NM FROM DUAL
)

--조회 쿼리
SELECT *
  FROM TEST_TABLE
 WHERE INSTR('/백제/신라/', '/' || COUNTRY || '/') > 0
 ORDER BY COUNTRY, ST

- INSTR은 문자 위치의 시작점을 리턴하는 함수 이다.

  파라미터(/백제/신라/)의 값에 해당 테이블의 컬럼 값을 비교하는 방식이다. 

  값을 앞,뒤에 꼭 구분자를 붙여서 값의 유일성을 보장해야 한다.

 

□ 결과

○ 조회 전 (TEST_TABLE)

○ 조회 후