행복한 세상

DB를 오라클을 사용시 DB 툴은 보통

Oracle SQL Developer 나 DBeaver 를 쓰는데

쿼리 실행 하다가 먹통 되서 강종을 할경우에

쿼리가 날라가버릴때 사용하면 좋음

-----------------------------------------------------

SELECT
     MODULE AS PROGRAM_NM /* 실행 프로그램 */
    ,PARSING_SCHEMA_NAME AS USER_ID /* 접속 계정 */
    ,FIRST_LOAD_TIME AS LOAD_TIME /* 실행 시간 */
    ,SQL_FULLTEXT AS SQLTEXT /* 쿼리 */
FROM V$SQL 
WHERE MODULE like '%%' --'SQL Developer'
ORDER BY FIRST_LOAD_TIME DESC 

-----------------------------------------------------

아래는 PROGRAM_NM  프로그램 실행명으로 찾을때 나온 항목들

각 프로젝트마다 사용하는 툴이 다르니 프로그램 명이나 버전등을 체크해서 

like 검색을 해주면 좋음~

DBeaver 6.3.0 - Main
DBeaver 6.3.0 - Metadata
DBeaver 7.1.0 - Main
DBeaver 7.1.0 - SQLEditor
DBeaver 7.1.2 - Metadata
DBeaver 7.1.2 - SQLEditor
DBeaver 7.1.3 - Main
DBeaver 7.1.3 - SQLEditor
DBeaver 7.1.3 - View sessions
QueryOne Client 2.1.8.24

SQL Developer
SQLGate 9.9.5.0

자주 쓰는거

SELECT   a.c1,
         LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

LISTAGG 함수가 도입되기 전에 동일 기능을 구현하기 위해 다양한 기법들이 사용되었다. 정리해보자.

아래와 같이 데이터를 생성하자.

CREATE TABLE t1 (c1 NUMBER(1), c2 VARCHAR2(2));

INSERT INTO t1 VALUES (1, '01');
INSERT INTO t1 VALUES (2, '02');
INSERT INTO t1 VALUES (2, '03');
INSERT INTO t1 VALUES (3, '04');
INSERT INTO t1 VALUES (3, '04');
INSERT INTO t1 VALUES (3, '05');
INSERT INTO t1 VALUES (3, '06');

① 11g를 사용한다면 LISTAGG 함수를 사용하면 된다. 집계함수(1번)와 분석함수(2번) 형태로 사용이 가능하다.

-- 1
SELECT   a.c1,
         LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

-- 2
SELECT a.c1,
       LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) OVER (PARTITION BY A.c1) AS c2
  FROM t1 a;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  2 02,03       
  3 04,04,05,06 
  3 04,04,05,06 
  3 04,04,05,06 
  3 04,04,05,06 

7 rows selected.

② WM_CONCAT 함수는 WMSYS 유저가 내부적으로 사용한다. (SQL Reference에 없다...--;) LISTAGG보다 성능은 떨어지지만 추가 기능(DISTINCT 구문, 분석함수 누적, KEEP 절)을 지원한다. 4번 방식을 이용하면 정렬도 가능하다. 

-- 1
SELECT   a.c1,
         wmsys.wm_concat (a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,06,05,04 

3 rows selected.

-- 2
SELECT   a.c1,
         wmsys.wm_concat (DISTINCT a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,05,06    

3 rows selected.

-- 3
SELECT a.c1,
       wmsys.wm_concat (a.c2) OVER (ORDER BY a.c2) AS c2
  FROM t1 a;

 C1 C2                    
--- ----------------------
  1 01                    
  2 01,02                 
  2 01,02,03              
  3 01,02,03,04,04        
  3 01,02,03,04,04        
  3 01,02,03,04,04,05     
  3 01,02,03,04,04,05,06  

7 rows selected.

-- 4
SELECT   a.c1,
         MAX (CAST (a.c2 AS VARCHAR2 (4000))) as c2
    FROM (SELECT a.c1,
                 wmsys.wm_concat (a.c2) OVER (PARTITION BY a.c1 ORDER BY a.c2) AS c2
            FROM t1 a) a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

-- 5
SELECT   a.c1,
         wmsys.wm_concat (a.c2) KEEP (DENSE_RANK FIRST ORDER BY a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02          
  3 04,04       

3 rows selected.

③ 10g에서는 XMLAGG 함수를 사용해도 된다. 

SELECT   a.c1,
         SUBSTR (XMLAGG (XMLELEMENT (a, ',', a.c2) ORDER BY a.c2).EXTRACT ('//text()'), 2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

④ 후임자를 괴롭히고 싶다면 MODEL 절을 사용해도 된다...--;

SELECT   a.c1,
         RTRIM (a.c2, ',') as c2
    FROM (SELECT c1,
                 c2,
                 rn
            FROM t1 a
           MODEL PARTITION BY (a.c1)
                 DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn)
                 MEASURES (CAST (a.c2 AS VARCHAR2(4000)) AS c2)
                 RULES (c2[ANY] ORDER BY rn DESC = c2[CV()] || ',' || c2[CV()+1])) a
   WHERE a.rn = 1
ORDER BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

⑤ 9i에서는 전통적 방식인 ROW_NUMBER와 SYS_CONNECT_BY_PATH 조합을 사용하면 된다. 

SELECT     a.c1,
           SUBSTR (MAX (SYS_CONNECT_BY_PATH (a.c2, ',')), 2) AS c2
      FROM (SELECT a.c1,
                   a.c2,
                   ROW_NUMBER () OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn
              FROM t1 a) a
START WITH a.rn = 1
CONNECT BY a.c1 = PRIOR a.c1
       AND a.rn - 1 = PRIOR a.rn
  GROUP BY a.c1
  ORDER BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

출처 : http://tyboss.tistory.com/entry/Oracle-XMLAGG-LISTAGG-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0

MSSQL 쿼리를 MYSQL 로 만들어 수정해보았고 이걸 다시 ORACLE 용으로 변경해보았다..

좋네..ㅎ

----------------------------------------------------------------------------

오라클 초성 검색 펑션

CREATE OR REPLACE
FUNCTION FN_GET_CHOSUNG (STR IN  VARCHAR2)
RETURN VARCHAR2 IS
/******************************************************************************
   NAME        : FN_GET_CHOSUNG
   STR         : 홍길동
   RETURN      : ㅎㄱㄷ
******************************************************************************/

I INT;
J INT;
TMPSTR VARCHAR2(255);
COL1 VARCHAR2(2);

BEGIN
 I := LENGTH(STR);
 J := 1;
 TMPSTR := '';

 WHILE J <= I LOOP
  COL1 := SUBSTR(STR, J, 1);
  TMPSTR := TMPSTR ||
    (
      CASE WHEN COL1 < 'ㄱ' THEN COL1 
      WHEN ascii('ㄱ') <= ascii(COL1) and 
         ascii(COL1)<= ascii('ㅎ') THEN COL1 
      WHEN COL1 < '까' THEN 'ㄱ'
      WHEN COL1 < '나' THEN 'ㄲ'
      WHEN COL1 < '다' THEN 'ㄴ'
      WHEN COL1 < '따' THEN 'ㄷ'
      WHEN COL1 < '라' THEN 'ㄸ'
      WHEN COL1 < '마' THEN 'ㄹ'
      WHEN COL1 < '바' THEN 'ㅁ'
      WHEN COL1 < '빠' THEN 'ㅂ'
      WHEN COL1 < '사' THEN 'ㅃ'
      WHEN COL1 < '싸' THEN 'ㅅ'
      WHEN COL1 < '아' THEN 'ㅆ'
      WHEN COL1 < '자' THEN 'ㅇ'
      WHEN COL1 < '짜' THEN 'ㅈ'
      WHEN COL1 < '차' THEN 'ㅉ'
      WHEN COL1 < '카' THEN 'ㅊ'
      WHEN COL1 < '타' THEN 'ㅋ'
      WHEN COL1 < '파' THEN 'ㅌ'
      WHEN COL1 < '하' THEN 'ㅍ'
      ELSE 'ㅎ'
      END
   );
  J := J + 1;
 END LOOP;

 RETURN TMPSTR;
END;
----------------------------------------------------------------------------

MYSQL

CREATE FUNCTION `chosung`(STR VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
 DECLARE I INT;
 DECLARE J INT;
 DECLARE TMPSTR VARCHAR(255);
 DECLARE COL1 VARCHAR(2);

 SET I = char_length(STR);
 SET J = 1;
 SET TMPSTR = '';
  WHILE J <=I  DO
    SET COL1 = SUBSTRING(STR, J, 1);
  SET TMPSTR = CONCAT(TMPSTR,(
                       CASE WHEN COL1 < 'ㄱ' THEN COL1 
                       WHEN ascii('ㄱ') <= ascii(COL1) and 
                              ascii(COL1)<= ascii('ㅎ') THEN COL1 
                       WHEN COL1 < '까' THEN 'ㄱ'
                       WHEN COL1 < '나' THEN 'ㄲ'
                       WHEN COL1 < '다' THEN 'ㄴ'
                       WHEN COL1 < '따' THEN 'ㄷ'
                       WHEN COL1 < '라' THEN 'ㄸ'
                       WHEN COL1 < '마' THEN 'ㄹ'
                       WHEN COL1 < '바' THEN 'ㅁ'
                       WHEN COL1 < '빠' THEN 'ㅂ'
                       WHEN COL1 < '사' THEN 'ㅃ'
                       WHEN COL1 < '싸' THEN 'ㅅ'
                       WHEN COL1 < '아' THEN 'ㅆ'
                       WHEN COL1 < '자' THEN 'ㅇ'
                       WHEN COL1 < '짜' THEN 'ㅈ'
                       WHEN COL1 < '차' THEN 'ㅉ'
                       WHEN COL1 < '카' THEN 'ㅊ'
                       WHEN COL1 < '타' THEN 'ㅋ'
                       WHEN COL1 < '파' THEN 'ㅌ'
                       WHEN COL1 < '하' THEN 'ㅍ'
                       WHEN COL1 <= '힣' THEN 'ㅎ'
                       ELSE COL1
                       END
         ));
      SET J = J + 1 ;
  END WHILE;
  RETURN TMPSTR;
END

----------------------------------------------------------------------------

MYSQL : http://cjbox.tistory.com/111