Oracle 노하우 및 팁
이제 점차 업계에서는 Oracle 10g에서 Oracle 11g로 업글을 하고 있는 추세인 것 같다.
이에, Oracle 11g에서 새롭게 소개된 기능들을 살펴보고자 하며, 그 첫 번째 내용으로 'LISTAGG' Function에 대해 알아보고자 한다.
우선 테스트 DB의 Oracle Version 확인 및 샘플 데이터를 생성해 보자
|
-- 1. Check Oracle Version SELECT * FROM v$version ; BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production ; -- 2. Create Table DROP TABLE EMP; CREATE TABLE EMP(DEPTNO NUMBER, ENAME VARCHAR2(1000), HIREDATE DATE); -- 3. Data Insert INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, 'CLARK ', TO_DATE('19810609', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, 'KING', TO_DATE('19811117', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, 'MILLER', TO_DATE('19820123', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, 'ADAMS', TO_DATE('19830112', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, 'FORD', TO_DATE('19811203', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, 'JONES', TO_DATE('19810402', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, 'SCOTT', TO_DATE('19821209', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, 'SMITH', TO_DATE('19801217', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'ALLEN', TO_DATE('19810220', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'BLAKE', TO_DATE('19810501', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'JAMES', TO_DATE('19811203', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'MARTIN', TO_DATE('19810928', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'TURNER', TO_DATE('19810908', 'YYYYMMDD')); INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, 'WARD', TO_DATE('19810222', 'YYYYMMDD')); COMMIT; |
이렇게 생성된 데이터를 확인해보자.
|
-- 4. Query EMP col deptno format 999,999,999 col ename format a10 col hiredate format a20 SELECT DEPTNO, ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') HIREDATE FROM EMP ; DEPTNO ENAME HIREDATE ------------ ---------- ------------ 10 CLARK 1981-06-09 10 KING 1981-11-17 10 MILLER 1982-01-23 20 ADAMS 1983-01-12 20 FORD 1981-12-03 20 JONES 1981-04-02 20 SCOTT 1982-12-09 20 SMITH 1980-12-17 30 ALLEN 1981-02-20 30 BLAKE 1981-05-01 30 JAMES 1981-12-03 30 MARTIN 1981-09-28 30 TURNER 1981-09-08 30 WARD 1981-02-22 |
이제 이 데이터를 바탕으로, DEPTNO를 GROUP으로 묶은 후 ENAME 값을 가로로 나열하고자 한다.
10g에서는 이를 구현하기 위해, CONNECT BY 구문을 사용해야 했다.
아래의 예제를 살펴보자.
|
-- 5. Query aggregated ename in 10g version col deptno format 999,999,999 col aggregated_enames format a50 SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, ', ')), 2) AGGREGATED_ENAMES FROM (SELECT DEPTNO, ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') HIREDATE, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) CNT FROM EMP) T CONNECT BY PRIOR CNT = CNT - 1 AND PRIOR DEPTNO = DEPTNO START WITH CNT = 1 GROUP BY DEPTNO ORDER BY 1 ; DEPTNO AGGREGATED_ENAMES ------------ ---------------------------------------------- 10 CLARK, KING, MILLER 20 SMITH, JONES, FORD, SCOTT, ADAMS 30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
즉 DEPTNO 값을 GROUPING 하기 위해 ROWNUM() 함수를 사용한 후, CONNECT BY PRIOR를 이용하여 연결한 뒤, SYS_CONNECT_BY_PATH 함수를 사용하여 값을 가로로 나열하였다.
하지만, 11g에서는 이를 구현하기 위해서 복잡하게 SQL을 구현할 필요가 없이 LISTAGG 함수를 사용하면 된다.
☞ LISTAGG Function Syntax
|
Syntax : LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )] The following elements are mandatory:
1) the column or expression to be aggregated;
2) the WITHIN GROUP keywords;
3) the ORDER BY clause within the grouping |
이제 LISTAGG Function이 사용된 여러 Sample을 통해 어떻게 활용이 가능한지 살펴보자.
▶ LISTAGG Function Sample_1
|
SELECT DEPTNO, LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO ; DEPTNO AGGREGATED_ENAMES ------------ ---------------------------------------------- 10 CLARK, KING, MILLER 20 SMITH, JONES, FORD, SCOTT, ADAMS 30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
사용방법은 간단하다. GROUPING 하고자 하는 컬럼을 GROUP BY로 묶은 후, 가로로 나열하고자 하는 컬럼을 LASTAGG()에 명시하면 된다. 이후, WITHIN GROUP() 함수에서 가로로 나열하고자 하는 순서를 ORDER BY로 지정하면 끝이다.
그럼 항상 GROUP BY 함수를 사용해야 하나? 10g의 분석함수처럼 여러 컬럼 데이터와 동시에 볼 수는 없을까?
아래의 예제에서 확인해보자.
▶ LISTAGG Function Sample_2
|
SELECT DEPTNO, ENAME, HIREDATE, LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGGREGATED_ENAMES FROM EMP ; DEPTNO ENAME HIREDATE AGGREGATED_ENAMES ------------ ---------- -------------------- ------------------------------------------- 10 CLARK 09-JUN-81 CLARK, KING, MILLER 10 KING 17-NOV-81 CLARK, KING, MILLER 10 MILLER 23-JAN-82 CLARK, KING, MILLER 20 SMITH 17-DEC-80 SMITH, JONES, FORD, SCOTT, ADAMS 20 JONES 02-APR-81 SMITH, JONES, FORD, SCOTT, ADAMS 20 FORD 03-DEC-81 SMITH, JONES, FORD, SCOTT, ADAMS 20 SCOTT 09-DEC-82 SMITH, JONES, FORD, SCOTT, ADAMS 20 ADAMS 12-JAN-83 SMITH, JONES, FORD, SCOTT, ADAMS 30 ALLEN 20-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES 30 WARD 22-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES 30 BLAKE 01-MAY-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES 30 TURNER 08-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES 30 MARTIN 28-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES 30 JAMES 03-DEC-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
Sample_1에서 사용한 LISTAGG 구문에 OVER(PARTITION BY DEPTNO) 구문을 추가할 경우, 분석함수처럼 기존 컬럼들과 같이 볼 수 있다.
▶ LISTAGG Function Sample_3
|
SELECT DEPTNO, LISTAGG(ENAME) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO ; DEPTNO AGGREGATED_ENAMES ------------ -------------------------------- 10 CLARKKINGMILLER 20 SMITHJONESFORDSCOTTADAMS 30 ALLENWARDBLAKETURNERMARTINJAMES |
이번 예제는 LISTAGG 함수의 2번째 파라마터 값을(구분자) 아무것도 안 줄 경우, 모든 값이 연속으로 연결되는 것을 확인할 수 있다.
이번에는 이 함수 사용 시 제약사항에 대해 살펴보자.
▶ LISTAGG Function Sample_4
|
SELECT DEPTNO, LISTAGG(ENAME, ', ') WITHIN GROUP () AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO ; LISTAGG(ENAME, ', ') WITHIN GROUP () AS AGGREGATED_ENAMES * ERROR at line 2: ORA-30491: missing ORDER BY clause |
당연한 이야기지만, WITHIN GROUP 함수 파라미터에 값을 주지 않을 경우 에러가 난다.
▶ LISTAGG Function Sample_5
|
SELECT DEPTNO, LISTAGG(ENAME, '(' || ROWNUM || ')') WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO ; LISTAGG(ENAME, '(' || ROWNUM || ')') WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES * ERROR at line 2: ORA-30497: Argument should be a constant or a function of expressions in GROUP BY. |
이번에는 LISTAGG 함수의 2번째 파라미터에 ROWNUM 같은 예약어는 사용 불가함을 알 수 있다.
▶ LISTAGG Function Sample_6
|
SELECT DEPTNO, LISTAGG(ENAME, '(' || CHR(DEPTNO+55) || ')') WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO ; DEPTNO AGGREGATED_ENAMES ------------ ---------------------------------------------- 10 CLARK(A)KING(A)MILLER 20 SMITH(K)JONES(K)FORD(K)SCOTT(K)ADAMS 30 ALLEN(U)WARD(U)BLAKE(U)TURNER(U)MARTIN(U)JAMES |
이번에는 LISTAGG 함수의 2번째 파라미터에 예약어가 아닌 CHR() 함수를 사용할 경우, 에러 없이 출력이 가능한 것을 볼 수 있다.
▶ LISTAGG Function Sample_7
|
SELECT LISTAGG(OBJECT_NAME) WITHIN GROUP (ORDER BY NULL) FROM ALL_OBJECTS ; FROM ALL_OBJECTS * ERROR at line 2: ORA-01489: result of string concatenation is too long |
이번 예제는 LISTAGG 함수를 사용하여 String 값을 가져올 때 너무 길이가 긴 값은 가져올 수 없음을 확인할 수 있는 예제이다. 여기서는 ALL_OBJECTS에서 모든 레코드의 OBJECT_NAME 값을 연결시키고자 했으며, 그 길이가 너무 길어 에러가 났음을 알 수 있다.
이상으로 11g에서 새롭게 소개된 LISTAGG 함수에 대해 알아보았다.
다음 포스팅 글에서도 11g의 유용한 기능에 대해 소개하고자 하며, 이번 시간에는 여기서 글을 마치고자 한다.
Reference site : http://www.oracle-developer.net/display.php?id=515
Blog : http://blog.naver.com/xsoft
정확하게는 11G R2버전 부터 사용 가능합니다.
11G R1은 사용 불가 합니다.
유용한 정보 감사합니다.^^
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi에서 LISTAGG를 사용할 수 없어서 설명하신 부분
(DEPTNO 값을 GROUPING 하기 위해 ROWNUM() 함수를 사용한 후, CONNECT BY PRIOR를 이용하여 연결한 뒤, SYS_CONNECT_BY_PATH 함수를 사용하여 값을 가로로 나열)에 있는 쿼리를 이용하여
SQL Developer에서 실행을 해보면 "소켓에서 읽을 데이터가 없습니다"라고 하면서
먹통이 되버리는데 혹시 무슨 문제인지 알수 있을까요??? 11G를 설치한 DB에서는 정상적으로 조회가 됩니다.
오래걸려서 세션 끊어지신거 아닌가요?
11G쪽에는 1초도 안걸리는데 10G쪽에는 2초 정도 돌다가 먹통이 됩니다.ㅠㅠ
이상하네요.. 일단 게시물 참조하여 해결해보세요.
http://www.gurubee.net/article/55512
) 버튼을 클릭하여 작성 하시면 됩니다.