박성훈의 웹개발

오라클에서 &등과 같은 특수문자가 포함된 텍스트(주로 URL값 입력시)를 SQL Developer에서 입력할 때 "대체변수 입력" 창이 아래와 같이 뜨게 된다.

이경우 해결할 수 있는 방법은 몇가지가 존재한다.

1. ASCII 코드값 입력하기 (★)

- 특수문자를 아스키코드 값으로 대체한다.

1) 아스키코드 값 추출

select ascii('&') from dual;

2. 아스키코드 값 입력

update url_tbl 
   set url = 'http://www.naver.com?aaa=bbb' || ch(38) || 'ccc=ddd'
  from url_id = '111111111';

2. DEFINE OFF 시키기

- & 특수기호의 경우는 대체 변수로 치환하게 되어 있다.

- SET DEFINE OFF를 하게되면 [대체 변수 입력] 프롬프트가 뜨지 않게 된다.

set define off

update url_tbl 
   set url = 'http://www.naver.com?aaa=bbb&ccc=ddd'
  from url_id = '111111111';
  
set define on

3. ESCAPE ON 처리하기

- ESCAPE ON을 하고 역슬래쉬(\)를 특수문자 앞에 붙히면 입력할 수 있게 된다.

set escape on

update url_tbl 
   set url = 'http://www.naver.com?aaa=bbb\&ccc=ddd'
  from url_id = '111111111';
  
set escape off

오라클에서는 세로로 나열된 정보를 가로로 연결해줄 수 있는 wm_concat()이라는 함수가 존재한다.

가뭄에 단비같은... 기능이라고나 할까? 역시 DB는 오라클인듯.

자 그럼 wm_concat()에 대해 간단히 설명해본다.

아래 쿼리는 하나의 요청에 1:N으로 엮여진 정보를 ,(콤마)를 붙혀 나열하기 위한 쿼리이다.

ex) 박성훈 요청번호 20150904001에 포함된 카테고리 정보 : 리눅스,자바,스프링

SELECT SUBSTR (SYS_CONNECT_BY_PATH (cd_nm , ','), 2)
FROM ( SELECT cd_nm
		, ROW_NUMBER() OVER (ORDER BY cd_nm) rnum
		, COUNT(*) OVER () cnt
		FROM ( SELECT cd_nm
			FROM t_cd
			WHERE cd IN ('AAA', 'BBB', 'CCC')
		)
	)
WHERE rnum = cnt
START WITH rnum = 1
CONNECT BY rnum = PRIOR rnum + 1;

쿼리에 대해 간단히 설명하면 먼저  rnum과 cnt를 붙혀준다.

그리고 재귀함수를 통해서 rnum이 1인 것부터 시작하여 SYS_CONNECT_BY_PATH로 텍스트에 ,로 붙혀주게 된다.

그러면 다음과 같은 형태가 된다.

리눅스

리눅스,자바

리눅스,자바,스프링

그 중에서 rnum과 cnt값이 같은 마지막값인 리눅스,자바,스프링 값을 최종적으로 선택하게 된다.

그런데 이 복잡한 쿼리를 wm_concat()을 이용하게 되면 아래와 같이 한방에 해결할 수 있게 된다.

굉장함! (혼자 감탄중..)

SELECT wm_concat(y.cd_nm)
  FROM t_catg x, t_cd y
 WHERE x.doc_no = aaa.doc_no AND x.catg_cd = y.cd

만약 t_catg에 중복값이 있다면 wm_concat에서 distinct 키워드를 사용하여 중복처리도 가능하다.

SELECT wm_concat(DISTINCT y.cd_nmo)
  FROM t_catg x, t_cd y
 WHERE x.doc_no = aaa.doc_no AND x.catg_cd = y.cd

AS-IS 쿼리의 경우 메인쿼리를 실행한 후, mybatis 등에서 다시 쿼리 호출을 통해 데이터를 가져왔었다.

이 경우 메인쿼리의 select 카운트에 따라 N번의 IO가 발생하여 성능이 느렸으나, wm_concat()로 변경하게 되면서 훨씬 더 좋은 성능을 얻을 수 있었다.

또한 over()를 통해 order by나 partition by도 복합적으로 사용할 수도 있다.