여행을 개발하다
[Oracle] XMLELEMENT, XMLAGG 함수 - 다수 레코드 1열로 가져오기 본문
이번 포스팅에서는 오라클의 XMLELEMENT, XMLAGG 함수에 대해 이야기하고자 한다.
하지만 각 함수의 고유한 기능보다는, 두 개의 함수를 조합한 '활용'에 더 초점을 맞추려고 한다.
* Oracle 10g 버전 이상부터XMLAGG, WM_CONCAT 함수를 조합하면 보다 간단하게 사용 가능하다.
먼저 두 함수의 정의에 대해 알아본다.
1. XMLELEMENT
- 정의 : 반환되는 레코드를 XML 요소로 바꾸어준다.
- 함수 인수 :XMLELEMENT([태그명], 레코드명)
함수의 이름 자체가 멀리서 보면 가독성이 떨어진다. 하지만 자세히 보면'XML + ELEMENT = XMLELEMENT'이다.
첫 번째 인수에는 XML의 요소로 사용할 태그를 지정하고, 그 뒤에는 SELECT할 필드명을 적어준다.태그명은 옵션 항목으로, 별도로 지정하지 않으면 레코드명 자체가 XML 요소로 반환된다. 따라서, FROM 절 뒤에 나오는 테이블의 레코드는 철저하게 무시된다.
예1) APPROVER 테이블의 APPR_USER 컬럼을 XML <A> 요소로 리턴한다.
SELECT XMLELEMENT(A, APPR_USER)
FROM APPROVER; 예1 결과)
XMLELEMENT(A,APPR_USER)
-------------------------
<A>2012010111</A>
<A>2012121202</A>
<A>2012050308</A>
<A>2013010101</A>
<A>2012050310</A>
<A>2011030402</A>
<A>2013032102</A>예2) XML 요소를 지정하지 않고, APPROVER 테이블의 APPR_USER 컬럼을 가져온다.
SELECT XMLELEMENT(APPR_USER)
FROM APPROVER; 예2 결과) APPROVER 테이블의 레코드는 무시되며, 해당 테이블의 레코드 수만큼의 XML 요소가 나타난다.
XMLELEMENT(APPR_USER)
----------------------
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>
<APPR_USER></APPR_USER>그래서 특정 테이블의 컬럼이 아닌DUAL 테이블을 활용하여, XMLELEMENT 함수 인수 그 자체를 XML 요소로 반환받을 수도 있다.
예3) DUAL 테이블의 활용
SELECT XMLELEMENT(APPR_USER)
FROM DUAL;예3 결과)
XMLAGG(XMLELEMENT(APPR_USER))
------------------------------
<APPR_USER></APPR_USER>
2. XMLAGG
- 정의 : XML 요소들로 이루어진 다수의 레코드들을 한 줄로 이어서 표현한다.
- 함수 인수 : XMLAGG(XML 요소 레코드)
'XML + AGG(aggregate) = XMLAGG'로, XML 요소들을 집합화한다는 뜻이다.XML 형식의 레코드를 하나의 ROW로 SELECT 해오기 때문에, XMLELEMENT와 적절히 결합하여 사용할 수 있다.
예1) APPROVER 테이블의 APPR_USER 컬럼을 XML <A> 요소로 감싸, 1개의 ROW로 리턴한다.
SELECT XMLAGG(XMLELEMENT(A, APPR_USER))
FROM APPROVER; 예1 결과)
XMLAGG(XMLELEMENT(A,APPR_USER))
--------------------------------
<A>2012010111</A><A>2012121202</A><A>2012050308</A><A>2013010101</A>...<A>2013032102</A>
3. 활용 예제
(결재 건에 포함된 다수의 결재권자들을 ','로 이어 1개의 ROW로 가져오기)
한 회사 프로젝트에서 다음과 같은 요구 사항을 받았는데, XMLELEMENT와 XMLAGG 함수를 활용하여 쉽게 문제를 해결할 수 있었다.
어떤 부서에서 품의를 올리려고 한다.
기안자는 품의 내용을 작성하여, 결재권자에게 상신한다.
기안자의 품의 내용은 1건이지만, 결재권자는 여러 명이 될 수도 있다.
이때, 사용자에게 품의 내용 1건당, 결재권자들을 한 row로 표현하여 보여줘야 한다.
간단히 말해, 기안자가 기안서 작성 후 결재권자에게 상신하는 워크플로 인터페이스를 개발하는 작업이었다. 보안상 관련된 테이블을 구조를 상세하게 설명할 수는 없지만, 대략적으로 다음과 같은 테이블들을 사용했다.
1. APPR_MST (결재 건들을 저장하는 마스터성 테이블)
2. APPROVER (APPR_MST의 결재 건들에 대한 결재권자들의 정보를 저장하는 테이블)
3. INSA (회사의 전 직원 정보를 담고 있는 테이블)
1) TABLE APPR_MST
TABLE_NAME COLUMN_NAME DATA_TYPE NULLABLE IS_PK COMMENTS
---------- ------------ ---------- ---------- ------ ----------------------------
APPR_MST APPR_SEQ NUMBER Y 결재건 시퀀스
APPR_MST START_DT DATE Y 결재 요청일
APPR_MST END_DT DATE Y 모든 결재권자의 승인 완료일
APPR_MST REG_DT DATE Y 등록일
APPR_MST REG_USER VARCHAR2(10) Y 등록자
APPR_MST UPT_USER VARCHAR2(10) Y 수정일
APPR_MST UPT_DT DATE Y 수정자 2) TABLE APPROVER
TABLE_NAME COLUMN_NAME DATA_TYPE NULLABLE IS_PK COMMENTS
---------- ----------- --------- ---------- ------- ---------------
APPROVER APPR_SEQ NUMBER Y 결재건 시퀀스
APPROVER APPROVER_SEQ NUMBER Y 결재권자 시퀀스
APPROVER UPT_DT DATE Y 수정일
APPROVER REG_USER VARCHAR2(10) Y 등록자
APPROVER REG_DT DATE Y 등록일
APPROVER APPR_USER VARCHAR2(10) Y 결재권자
APPROVER UPT_USER VARCHAR2(10) Y 수정자3) TABLE INSA
TABLE_NAME COLUMN_NAME DATA_TYPE NULLABLE IS_PK COMMENTS
------------ ------------- ----------- --------- ------- -----------------
INSA SABUN VARCHAR2(10) Y 사번
INSA NAME VARCHAR2(100) 이름INSA 테이블은 단순히 사번을 가지고 이름을 조회하는 용도로만 사용될 예정이기에 배제하고, 아래 그림과 같이 APPR_MST 테이블과 APPROVER 테이블의 관계를 살펴보면 1 : N 관계임을 알 수 있다. 결재 1건당 다수의 결재자가 있을 수 있기 때문이다.
이를 다음 형식으로 SELECT 해오고자 한다.
결재번호 기안자 결재자 결재요청일 결재승인일
-------- --------- ------------------------------------ ----------- -----------
1 정영훈 결재권자1, 결재권자2, ..., 결재권자N 20200301 20200307
* 결재 건 테이블 (APPR_MST) 데이터(날짜 형식은 TO_CHAR 함수를 통해 가독성 있게 가져옴)
SELECT *
FROM APPR_MST;APPR_SEQ START_DT END_DT REG_DT REG_USER UPT_DT UPT_USER
--------- ----------- -------- --------- ---------- -------- --------------
1 20200303 20200310 20200303 2020011901 20200303 2020011901
2 20200307 20200314 20200307 2012010108 20200307 2012010108
* 승인자 (APPROVER) 데이터(날짜 형식은 TO_CHAR 함수를 통해 가독성 있게 가져옴)
SELECT *
FROM APPROVER;APPR_SEQ APPROVER_SEQ APPR_USER REG_DT REG_USER UPT_DT UPT_USER
-------- -------------- ----------- --------- ---------- -------- --------------
1 1 2012010111 20200307 2020011901 20200307 2020011901
1 2 2012121202 20200307 2020011901 20200307 2020011901
1 3 2012050308 20200307 2020011901 20200307 2020011901
2 1 2013010101 20200307 2012010108 20200307 2012010108
2 2 2012050310 20200307 2012010108 20200307 2012010108
2 3 2011030402 20200307 2012010108 20200307 2012010108
2 4 2013032102 20200307 2012010108 20200307 2012010108이에 결재건 시퀀스를 변수로 받는 전체 SQL을 작성해본다.
SELECT AM.APPR_SEQ AS 결재번호,
(SELECT INSA.NAME
FROM INSA INSA
WHERE AM.REG_USER = INSA.SABUN) AS 기안자,
SUBSTR(( SELECT XMLAGG(XMLELEMENT(A, ',' || (SELECT INSA.NAME
FROM INSA INSA
WHERE APPR.APPR_USER = INSA.SABUN)
)
).EXTRACT('//text()').GETSTRINGVAL()
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
),2) AS 결재자,
TO_CHAR(AM.START_DT, 'YYYYYMMDD') AS 결재요청일,
TO_CHAR(AM.END_DT, 'YYYYMMDD') AS 결재승인일
FROM APPR_MST AM;
--WHERE AM.APPR_SEQ = 1;결재번호 기안자 결재자 결재요청일 결재승인일
--------- -------- ---------------------- ----------- -------------
1 정영훈 유지연,김주영,이태경 202000303 20200310
2 구인철 최민석,김동우,전지희,남지영 202000307 20200314
그중 XML 함수가 사용된 부분만 뜯어본다.
SUBSTR(( SELECT XMLAGG(XMLELEMENT(A, ',' || (SELECT INSA.NAME
FROM INSA INSA
WHERE APPR.APPR_USER = INSA.SABUN)
)
).EXTRACT('//text()').GETSTRINGVAL()
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
),2) AS 결재자
1단계)
· APPROVER 테이블의 APPR_USER 컬럼을 SELECT 해오되, <A> 태그를 붙여서 가져온다.
· 단, 하나의 ROW에는 APPR_MST 테이블의 APPR_SEQ와 APPORVER의 APPR_SEQ가 일치하는 레코드들만 가져온다.
· 또한, 쉼표(,)로 구분해서 가져오기로 했으므로, 레코드 앞에 ',' || 를 추가한다.
SELECT (SELECT XMLAGG(XMLELEMENT(A, ',' || APPR_USER ))
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
) AS 결재자
FROM APPR_MST AM; 결재자
-------------------------------------------------------------------------
<A>,2012010111</A><A>,2012121202</A><A>,2012050308</A>
<A>,2013010101</A><A>,2012050310</A><A>,2011030402</A><A>,2013032102</A>
2단계)
· 하지만 사번만으로는 누가 누군지 구분할 수 없다.
· 따라서, INSA 테이블에서 해당 사번에 해당하는 이름을 서브 쿼리로 다시 가져온다.
SELECT (SELECT XMLAGG(XMLELEMENT(A, ',' || ( SELECT INSA.NAME
FROM INSA INSA
WHERE APPR.APPR_USER = INSA.SABUN
)
)
)
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
) AS 결재자
FROM APPR_MST AM; 결재자
-----------------------------------------------------
<A>,유지연</A><A>,김주영</A><A>,이태경</A>
<A>,최민석</A><A>,김동우</A><A>,전지희</A><A>,남지영</A>
3단계)
· 사실상 필요 없는 XML 태그를'EXTRACT('//text()')' 함수로 제거한다.
· 운영 환경의 상황에 따라 한글이 깨질 수 있다는 것을 염두,'GETSTRINGVAL()'함수로 완벽하게 추출해 준다.
SELECT (SELECT XMLAGG(XMLELEMENT(A, ',' || ( SELECT INSA.NAME
FROM INSA INSA
WHERE APPR.APPR_USER = INSA.SABUN
)
)
).EXTRACT('//text()').GETSTRINGVAL()
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
) AS 결재자
FROM APPR_MST AM; 결재자
-------------------------
,유지연,김주영,이태경
,최민석,김동우,전지희,남지영
4단계)
가장 앞에 있는 콤마(,)를 'SUBSTR' 함수로 제거해 준다.
SELECT SUBSTR((SELECT XMLAGG(XMLELEMENT(A, ',' || ( SELECT INSA.NAME
FROM INSA INSA
WHERE APPR.APPR_USER = INSA.SABUN
)
)
).EXTRACT('//text()').GETSTRINGVAL()
FROM APPROVER APPR
WHERE AM.APPR_SEQ = APPR_SEQ
),2) AS 결재자
FROM APPR_MST AM; 결재자
-------------------------
유지연,김주영,이태경
최민석,김동우,전지희,남지영
지금까지 오라클의 XMLELEMENT, XMLAGG 함수의 정의 및 활용에 대해 알아보았다.
긴 글 읽어주셔서 감사하며, 수정사항이나 개선이 필요한 부분이 있으면 언제든지 댓글 환영합니다 : )
'데이터베이스 > Oracle' 카테고리의 다른 글
| [Oracle] 계층형 쿼리 - START WITH ~ CONNECT BY 구문 (1) | 2020.03.08 |
|---|---|
| [Oracle] Data Dictionary (0) | 2020.02.09 |
