Oracle의 숨겨진 함수 wm_concat () - 한 컬럼에 데이터 콤마로 엮기(Clob)

1. Oracle 에는 비공개된 숨겨진 functions 및 parameter가 다수 존재합니다.

그 중에서 최근에 알게 된 것이 "wmsys.wm_concat"라는 함수로 굉장히 강력합니다.

기능적으로 보면 집약하는 함수라고 볼 수 있으며, 정규화된 table을 비정규화해서 출력하는

경우에 딱맞습니다. Oracle11g manual에는 기재되어 있지 않기 때문에

undocumented function(support대상외)이라는 것을 알고 사용하기를 바랍니다.

2. 이제 어떤 기능이 되는지 살펴보겠습니다. 우선 다음과 같은 간단한 table을 만들어 보겠습니다.z

name	description
userid
point	취득한
getdate	point취득일

3. point가 발생할 때마다 table에 new record가 등록이 되므로 userid별로 복수의 records가

존재하게 됩니다. 이렇게 정규화된 table에서 비정규화된 형태(userid별로 point취득일과

취득한 point를 다음의 결과와 같이 한 행에 표시)로 출력하고자 하는 경우에

wm_concat를 사용할 수 있습니다.

SQL> create table points (userid varchar2(20), point number(10), getdate date); SQL> insert into points values ('hurjin',100,to_date('2009/04/19','yyyy/mm/dd')); SQL> insert into points values ('hurjin',50,to_date('2009/05/20','yyyy/mm/dd')); SQL> insert into points values ('hurjin',300,to_date('2009/05/10','yyyy/mm/dd')); SQL> insert into points values ('myoyoung',1100,to_date('2009/03/29','yyyy/mm/dd')); SQL> insert into points values ('myoyoung',40,to_date('2009/04/19','yyyy/mm/dd')); SQL> insert into points values ('myoyoung',150,to_date('2009/05/01','yyyy/mm/dd')); SQL> select userid, wmsys.wm_concat(point || '(' || getdate || ')') point_info 2 from points group by userid order by userid; userid point_info hurjin 100(2009-04-19),300(2009-05-10),50(2009-05-20) myoyoung 1100(2009-03-29),40(2009-04-19),150(2009-05-01)

4. 보통 위와 같은 결과를 얻기 위해서는 application쪽에서 coding을 하는 경우가 많습니다만,

wm_concat을 사용함으로서 한 번의 SQL을 수행을 통해 결과를 얻을 수 있습니다.

5. 이 wmsys는 Workspace Manager용 metadata를 저장하기 위한 schema인데,

wm_concat함수의 source는 암호화되어 있는 관계로 볼 수는 없어서 어떤 식으로 처리를 하는지

알 수는 없습니다. 하지만 실행속도는 native function과 비슷한 정도로,

대량의 data를 test한 경우에도 처리속도가 훌륭하더군요.

아래에서는 또 하나의 예입니다.

SQL> create table idtable (id number, val varchar2(20)); SQL> insert into idtable (id, val) values (10, 'abc'); SQL> insert into idtable (id, val) values (10, 'abc'); SQL> insert into idtable (id, val) values (10, 'def'); SQL> insert into idtable (id, val) values (10, 'def'); SQL> insert into idtable (id, val) values (20, 'ghi'); SQL> insert into idtable (id, val) values (20, 'jkl'); SQL> insert into idtable (id, val) values (20, 'mno'); SQL> insert into idtable (id, val) values (20, 'mno'); SQL> commit; SQL> column enames format a50; SQL> select id, wmsys.wm_concat(val) as enames from idtable group by id; id enames 10 abc,def,def,abc 20 ghi,mno,mno,jkl SQL> select id, wmsys.wm_concat(distinct val) as enames 2 from idtable group by id order by id; id enames 10 abc,def 20 ghi,jkl,mno SQL> select id, val, wmsys.wm_concat(val) over(partition by id) as enames 2 from idtable order by id; id val enames 10 abc abc,abc,def,def 10 abc abc,abc,def,def 10 def abc,abc,def,def 10 def abc,abc,def,def 20 ghi ghi,jkl,mno,mno 20 jkl ghi,jkl,mno,mno 20 mno ghi,jkl,mno,mno 20 mno ghi,jkl,mno,mno SQL> select id, val, wmsys.wm_concat(val) over(order by id, val) as enames 2 from idtable order by id; id val enames 10 abc abc,abc 10 abc abc,abc 10 def abc,abc,def,def 10 def abc,abc,def,def 20 ghi abc,abc,def,def,ghi 20 jkl abc,abc,def,def,ghi,jkl 20 mno abc,abc,def,def,ghi,jkl,mno 20 mno abc,abc,def,def,ghi,jkl,mno

6. database를 DBCA를 이용하지 않고 수동으로 생성하여 "wmsys" schema가 존재하지 않는 경우,

"$ORACLE_HOME/rdbms/admin/owminst.plb"를 실행하여 생성할 수 있습니다.

[출처] Oracle의 숨겨진 함수 wmsys.wm_concat 에 관해...|작성자 진우준세 아버지

 

예제)

WITH tSite
     AS (SELECT 'sg' AS site_cd FROM DUAL
         UNION ALL
         SELECT 'uk' AS site_cd FROM DUAL
         UNION ALL
         SELECT 'fr' AS site_cd FROM DUAL)
SELECT wm_concat(site_cd) AS siteCd
  FROM tSite  

결과 :