데이터베이스 질문과 답변
안녕하세요.
input 하나의 변수에 구분자를 넣어 여러개의 코드값을 전달 했을때
where절의 in 조건에 대입을 하려고 합니다.
예시)
-- 전달값
-- codes : A|B|C|D
PROCEDURE pc_test(codes in varchar2, out_cursor in out SYS_REFCURSOR)
is
ret_cursor SYS_REFCURSOR; --변수선언
testcdList varcHar2(300);
begin
testcdList := '''' || replace(codes, '|', '''' || ',' || '''') || '''';
open ret_cursor for
select ....
from test...
where code in ('A', 'B', 'C', 'D') -- <-- 변환하여 대입(codes의 값을 대입)
--where code in (testcdList) -- <-- 변환하여 대입(실패)
;
out_cursor := ret_cursor;
end;
전달받은 [codes]값을 testcdList에 변경하여 where절에 대입해 봤지만 적용이 안되는것 같습니다.
어떤식으로 변환을 해야할지 조언 부탁드립니다.
by 우리집아찌
[2022.07.19 16:59:33]
WITH T AS ( SELECT 'A' V FROM DUAL UNION ALL SELECT 'B' FROM DUAL UNION ALL SELECT 'C' FROM DUAL UNION ALL SELECT 'D' FROM DUAL UNION ALL SELECT 'E' FROM DUAL ) SELECT * FROM T A WHERE REGEXP_LIKE ( V ,'A|B|C|D' )
by 마농
[2022.07.19 17:23:17]
-- 1. 문자 비교 조건으로 바꾸는 방안
SELECT ....
FROM ....
WHERE ....
AND INSTR(codes, code) > 0 -- 고정 자리수인 경우
AND INSTR('|'||codes||'|', '|'||code||'|') > 0 -- 가변 자리수인 경우
AND REGEXP_LIKE(code, codes) -- 고정 자리수인 경우
AND REGEXP_LIKE(code, '^('|| codes ||')$') -- 가변 자리수인 경우
AND REGEXP_INSTR(code, codes) > 0 -- 고정 자리수인 경우
AND REGEXP_INSTR(code, '^('|| codes ||')$') > 0 -- 가변 자리수인 경우
;
-- 2. Split 하는 서브쿼리 이용하는 방안
SELECT ....
FROM ....
WHERE ....
AND code IN (SELECT REGEXP_SUBSTR(codes, '[^|]+', 1, LEVEL) code
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(codes, '[^|]+')
)
;
-- 3. 동적쿼리를 이용하는 방안 --
PROCEDURE pc_test
( codes IN VARCHAR2
, out_cursor IN OUT SYS_REFCURSOR
)
IS
ret_cursor SYS_REFCURSOR; --변수선언
testcdList VARCHAR2(300);
v_sql VARCHAR2(300);
BEGIN
testcdList := '''' || REPLACE(codes, '|', ''',''') || '''';
v_sql := 'SELECT ....'
|| ' FROM ....'
|| ' WHERE ....'
|| ' AND code IN (' || testcdList || ')'
;
OPEN ret_cursor FOR v_sql;
out_cursor := ret_cursor;
END;
/
) 버튼을 클릭하여 작성 하시면 됩니다.