[Oracle] Oracle 데이터를 Json 형태 문자열로 변환 [오라클 11g 이하]

• target:  Oracle 데이터를 Json 형태 문자열로 변환

 

---

• method: 

▶ 같이 보면 좋을 내용

https://pu3vig.tistory.com/119

[Oracle] Oracle 데이터를 Json 형태로 변환 [오라클 12c 이상]

https://pu3vig.tistory.com/121

[Java] CLOB 데이터 String 변환

https://pu3vig.tistory.com/126

[Mybatis] Oracle CLOB 데이터 조회

---

1. LISTAGG [Oracle 11g 이상]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT ID
     , LISTAGG(NAME, ',') WITHIN GROUP(ORDER BY SEQ) AS NAME_LIST
  FROM TB_COL
GROUP BY ID;

/*
>>
  ID    |     NAME_LIST
 COL1   |   컬럼1,컬럼4
 COL2   |   컬럼2
 COL3   |   컬럼3
*/

※ VARCHAR2형이기 때문에 4000byte 이상인 경우, 잘림

---

2. XML(XMLELEMENT()).EXTRACT() [Oracle 11g 이하]

/*
- 테이블 명 : TB_COL
- 데이터 형식
> SELECT SEQ, ID, NAME, TYPE
FROM TB_COL;

>> Result
   SEQ     |      ID     |     NAME      |     TYPE
    1      |     COL1    |     컬럼1     |     TEXT
    2      |     COL2    |     컬럼2     |    NUMBER
    3      |     COL3    |     컬럼3     |     DATE
    4      |     COL1    |     컬럼4     |    DATETIME
*/

-- 위와 같은 ROW를 보유한 테이블이 존재하는 경우
SELECT ID
     , SUBSTR(XMLAGG(XMLELEMENT(A, ',' || NAME) ORDER BY SEQ).EXTRACT('//text()').getCLOBval(), 2) NAME_LIST
  FROM TB_COL
GROUP BY ID;

/*
>>
  ID    |     NAME_LIST
 COL1   |   컬럼1,컬럼4
 COL2   |   컬럼2
 COL3   |   컬럼3
*/

※ XMLELEMENT함수는 첫번째 파라미터로 해당 컬럼을 감싸는 태그 생성

※ 첫번째 파라미터는 소문자로 작성하여도 대문자 태그 생성※ XMLAGG는 CONCAT과 동일하다고 보면 되고, ORDER BY를 통해 다른 컬럼의 값으로 정렬 가능※ XMLAGG나 XMLELEMENT의 RETURN 타입은 XML형이기에, 이를 SUBSTR을 사용하면 VARCHAR2로 형변환※ SUBSTR 없이 VARCHAR2로 변환하고자 하는 경우, .getStringVal() 사용※ EXTRACT('//text()')는 XML형의 태그내의 문자열만 추출 (문자열이 4000byte를 넘어갈 수 있기에 .getCLOBval() 적용)※ SUBSTR에서 2번째 문자열부터 추출하는 이유는 처음 XMLELEMENT생성 시, 콤마를 추가하였기에, 첫번째 콤마 제거용

 

요약)

1. XMLELEMENT(A, ',' || NAME)을 통해서 컬럼의 값을 <A>,값</A> 형태로 변환

2. XMLAGG()를 통해서 컬럼을 SEQ 순으로 이어붙여서 <A>,1번값</A><A>,2번값</A><A>,3번값</A> ... 형태로 변환

3. .EXTRACT('//text()')를 통해서 태그를 제거한 문자열만 추출하여 ,1번값,2번값,3번값 ... 형태로 추출

4. SUBSTR()를 통해서 1번값 앞의 콤마를 제외한 위치부터 잘라내어 데이터 완성

---

• desc: 

12c이하 버전에서 데이터를 JSON 형태 문자열로 변환하여 추출하기 위한 용도로 쓰기 편함

추출하여 Java나 Javascript에서 문자열을 JSON형태로 파싱해서 사용 가능

---

• warning: 

오라클 버전에 따라서, 그리고 데이터 길이에 따라서 사용함수 선택 필요

---

• source:

https://goddaehee.tistory.com/57

[Oracle] 오라클 피벗(1) - LISTAGG, WM_CONCAT