Run!

이 포스트는 ;나 ,등의 구분자로 여러 개의 값을 가진 컬럼으로 다른 테이블 정보를 조회하여 다시 한 컬럼의 값으로 합치는 방법을 설명하고 있다. 바쁘신 분은 최종 쿼리만 보시면 됨.

tbl_space라는 테이블이 있다고 하자.

이 테이블은 건물의 사무실공간 정보를 갖고 있다.

대충 스키마를 그려보자면 

<tbl_space : 공간정보 테이블>

insert into tbl_space values(1, '302호', '1001,1052');

<tbl_dept : 부서정보 테이블>

insert into tbl_dept values('1001', '경영지원');

insert into tbl_dept values('1052', '인사');

insert into tbl_dept values('1042', '마케팅');

tbl_space테이블을 다시 살펴보자.

302호 사무실을 쓰는 부서는 경영지원팀과 인사팀이다.

내가 리스트에 뿌려주고 싶은 값이  '1001,1052'가 아니라 '경영지원, 인사'라면 어떻게 가져오는게 좋을까?

                                         (원하는 결과의 모양)

우선 tbl_dept에서 '1001'과 '1052'값을 가진 부서명을 가져오면 아래처럼 나올것이다.

select dept_name from tbl_dept where dept_cd in ('1001', '1052');

얘네를 먼저 한 컬럼으로 만들어야겠다. 그렇다면 LISTAGG를 써보자

(오라클11g부터, 9g는 WM_CONCAT를 쓰라고한다.)

형식은  LISTAGG(컬럼명 , 구분자) 이다.

select LISTAGG(dept_name, ',') within group (order by dept_name) dept_name

   from tbl_dept

   where dept_cd in ('1001', '1052');

이제 원하던 대로 나올 것이다.

그런데! 조건절 where dept_cd in ('1001', '1052') 을 어떻게 구현하느냐 하는 난관에 봉착했다.

space_use_dept의 값은 '1001, 1052'이니깐 대충 생각하면 1001, 1052를 ','로 split해서 배열로 만들어주면 될 것 같은데...이걸 어떻게 구현?

이제 여기서는 REGEXP_SUBSTR라는 정규식을 써보도록 한다. (10g이상)

REGEXP_SUBSTR(대상문자열, '[^구분자]+'(정규표현식), 시작위치, 찾는위치)

'1052'를 뽑고싶다면 구분자는 콤마니까 구분자에 ,를 넣어주고 두번째 데이터니 찾는위치에 2를 넣어서

REGEXP_SUBSTR('1001, 1052', '[^,]+', 1,2) 이렇게 써주면 된다.

그렇다면...우리는 space_use_dept값에 있는 데이터를 다 찾아야 하니 하나씩 갖고오도록 해보자

WITH TT AS ( SELECT '1' FROM DUAL)
           SELECT TRIM(REGEXP_SUBSTR('1001, 1052', '[^,]+', 1, LEVEL)) AS space_use_dept
                FROM TT
           CONNECT BY INSTR('1001, 1052', ',', 1, LEVEL - 1) > 0;

with connect를 이용하여 1개씩 갖고오도록 하는 쿼리문이다. (with connect는 따로 설명하려면 길어질듯하니인터넷에서 잘 찾아보도록하자) 이렇게 질의를 하면 아래와 같이 예쁘게 split된 데이터로 가져온다.

자 대망의 최종 Query문임.

select space_cd, 
         space_name,
         (select LISTAGG(dept_name, ',') within group (order by dept_name) dept_name
                from tbl_dept d
                where dept_cd in (WITH TT AS ( SELECT '1' FROM DUAL)  
                                    SELECT TRIM(REGEXP_SUBSTR(s.space_use_dept, '[^,]+', 1, LEVEL)) AS space_use_dept
                                    FROM TT 
                                    CONNECT BY INSTR(s.space_use_dept, ',', 1, LEVEL - 1) > 0)) space_use_dept_str
      from tbl_space s;

그리고 최종 결과

참...쉽쥬?

어느날 디비접속하는데 저런 에러가 난다면

다음을 수행해준다.

윈도우시작버튼->실행->services.msc->OracleOraHome92CliendCache 시작

기본 환경은 Weblogic, Apache, Oracle, Spring, Mybatis이다.

오라클은 원래 쓰고 있었는데 MS-SQL을 연결할 일이 생겼다.

A. 우선 웹로직에 데이터 소스를 만든다.

1. 서비스>데이터소스>새로만들기

2.  드라이버클래스 이름은 weblogic.jdbc.sqlserver.SQLServerDriver

3. 나머지는 적당히...

B. 라이브러리에 sqljdbc4.jar를 추가한다. (자세한 것은 나중에 추가한다. 오래전에해서 기억이 잘...)

C. DB연결정보 셋팅

DB.Mssql.DriverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

mysql에서 사용자 정의 함수를 만들 때 나는 에러

1. Not allowed to return a result set from a function SQL Statement

select할 때 값을 =가 아닌 into로 넣어준다.

예를 들면

select v_id = m_id from tblMember; => select m_id into v_id from tblMember;

2. ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

create 문 실행하기전에

SET GLOBAL log_bin_trust_function_creators = 1; 

를 실행해준다.