728x90

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

2from 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 에 관해...|작성자 진우준세 아버지

* 오라클에는 GROUP_CONCAT 함수가 없다

728x90

'DB' 카테고리의 다른 글

토드 쿼리 로그  (0) 2012.07.29
오라클에 접속하기  (0) 2012.07.29
GROUP_CONCAT  (0) 2012.07.29
ORDER BY item must be the number of a SELECT-list expression  (0) 2012.07.29
셀렉트하면서 인서트  (0) 2012.07.29

+ Recent posts