가로를 세로로 바꾸기

세로를 가로로 만드는 방법에 대해서는 Recursive SQL 에서 충분히 논하였다.
그렇다면 가로를 세로로 바꾸는 방법중의 최적은 무엇인가?

그문제와 관련하여 필자는 얼마전 어떤 개발자가 유져로 부터 다음과 같은 질문을 받았는것을 보았다.

유져 : "DBMS 와 웹 Application 간에 배열 연동이 안되므로 변수를 배열로 던져주지 못하는 대신에 '7654,7698,7780,7788' 처럼 변수를 만들어서 Oracle 에 던지고 싶습니다.
그런데 이렇게 던지면 Oracle 에서 List 로 만들어서 처리할수 있습니까?"

개발자 : "예" --> 어느정도 내공이 있는 개발자임을 알수 있다.

유져 : "구분자(delimiter)가 항상 ',' 가 아닐수도 있고 1 byte 도 아닐수 있습니다.
예를들면 구분자가 ':;;:' 처럼 될수도 있고 그것은 프로그램 마다 다를수 있습니다.
그래도 가능 하겠습니까?"

개발자: (주저주저 하며 대답을 못함)

이런경우 일단 대답은 "예" 이다.

왜냐하면 프로그램마다 문자열과 구분자를 받을수 있기 때문이다.

SELECT :v_str , :v_deli FROM dual;

--여기서 :v_deli 는 구분자(',') 이며 :v_str 는 문자열 '7654,7698,7780,7788' 이다.

결과 :

Oracle 10g 라면 아래처럼 간단한 SQL 로 IN-LIST 를 만들수 있다.

SELECT
ltrim(regexp_substr(:v_deli:v_str:v_deli, :v_deli'[^':v_deli']+', 1, level ), :v_deli) AS empno
FROM dual
connect by level<= ( length(:v_deli:v_str) - length(replace(:v_deli:v_str, :v_deli)) ) / length(:v_deli) ;

결과 :

처리의 핵심은 첫번째로는 문자열 길이 에서 구분자(comma) 를 제거한 길이를 빼서 구분자의 길이로 나누면 row 갯수가 나온다는 점이다.
두번째로는 10g 부터 사용할수 있는 정규식을 사용하여 시작문자와 끝문자 를 정의해서 그사이에 있는 문자들을 가져올수 있다는 점이다.
예를들면 regexp_substr( 문자열, ',[^,]+') 이렇게 하면 문자열에서 ,(comma) 부터 다음 ,(comma) 까지의 문자들을 가지고 올수 있다.
물론 10g 미만의 버젼이라면 level 과 connect by 를 대신해서 copy_t 류의 테이블을 사용하고 substr 과 instr 함수를 적절히 조합하면 된다.

이해를 돕기위해 아래 SQL 을 실행해 보자.
아래 SQL 은 첫번째,두번째,세번째, 네번째 의 사번(EMPNO)을 위의방법을 이용하여 각각 가져오고 있다.

SELECT
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 1 ) empno1,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 2 ) empno2,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 3 ) empno3,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 4 ) empno4
FROM dual;

주의사항은 오라클 정규식의 예약어인 파이프라인 이나 괄호 )( ][ {} 등을 구분자로 사용하지 말라는것이다.
파이프라인 이나 괄호 대신에 ':' 처럼 적당한 다른문자를 사용하면 됨.