출처: http://limyt.tistory.com/18

오라클 연관테이블에 매핑된 레코드 존재여부 확인

☆리미트☆ | 2011/01/19 22:30 | etc. |  1
댓글 1
SNS로 공유하기더보기
A와 B 테이블이 있다고 할때 A 테이블의 전체 리스트를 뿌리고 
B테이블에 A가 있는지만 확인하고 싶을때 다음과 같이 조회 할 수 있습니다. 

SELECT      A.UID
                       , A.NAME
                       , A.LABEL
                       , B.UID
                       , NVL2(B.UID, 'Y', 'N')  CHK
FROM      AAA A, BBB B
WHERE A.UID = B.UID(+)

CHK 가 존재 유무가 되겠네요 존재한다면 Y 없다면 N !! 


출처: 웅의 Oracle 정리노트

Sys_Connect_By_Path를 대신할 XMLAGG(XMLELEMENT)

 | 

xmlagg는 sys_connect_by_path와 같이 여러 row의 특정 컬럼의 값 한 row로 바꿔주는 xml함수이다.
xml함수가 처리할 수 있는 DataType은 xml DataType이어야 하고 그래서 일반적으로 쓰이는 string, number DataType을 일단 xml DataType으로 변환해 주어야 한다.

create table testt(col1 varchar2(10), col2 number);
insert into testt values('빨강',10);
insert into testt values('파랑',20);
insert into testt values('노랑',30);
commit;


1. XMLELEMENT함수 : char, varchar, number DatavType을 xml DatavType으로 바꿔준다.
 - 첫번째 파라미터(필수) : xml element tag의 명칭을 지정한다. 컬럼명이나 쌍따옴표로 묶은 리터럴문자가 올 수 있다. 컬럼명이 온다고 컬럼의 데이터가 표시되는 것은 아니다. 리터럴문자처럼 처리된다. Null은 올 수 없다.

WOONGASM:WOONG >
  1  select xmlelement("Color",col1) from testt;

XMLELEMENT("COLOR",COL1)
--------------------------------------------------
<Color>빨강</Color>
<Color>파랑</Color>
<Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlelement("number",col2) from testt;

XMLELEMENT("NUMBER",COL2)
--------------------------------------------------
<number>10</number>
<number>20</number>
<number>30</number>


 - 두번째 파라미터(옵션) : xml element의 컬럼을 지정한다.(상수표현 및 서브쿼리도 가능)

WOONGASM:WOONG >
  1  select xmlelement("cons",'상수') from testt;

XMLELEMENT("CONS",'상수')
-------------------------------------------------
<cons>상수</cons>
<cons>상수</cons>
<cons>상수</cons>

WOONGASM:WOONG >
  1  select xmlelement("Rank",(select count(*) from testt where a.col2 <= col2) ) from testt a;

XMLELEMENT("RANK",(SELECTCOUNT(*)FROMTESTTWHEREA.COL2<=COL2))
-----------------------------------------------------------------------------------------------
<Rank>3</Rank>
<Rank>2</Rank>
<Rank>1</Rank>

 - 세번째 파라미터 이후(옵션) : 세번째 파라미터 이후는 concat와 같은 역할을 한다(여러개의 컬럼 또는 상수를 붙여쓸 수 있다.)

WOONGASM:WOONG >
  1  select xmlelement("cons",col1, ' concat ',col2) from testt;

XMLELEMENT("CONS",COL1,'CONCAT',COL2)
----------------------------------------------------------------
<cons>빨강 concat 10</cons>
<cons>파랑 concat 20</cons>
<cons>노랑 concat 30</cons>

WOONGASM:WOONG >
  1  select xmlelement("cons",'상수1 ','상수2 ','상수3 ') from testt;

XMLELEMENT("CONS",'상수1','상수2','상수3')
---------------------------------------------------------------------
<cons>상수1 상수2 상수3 </cons>
<cons>상수1 상수2 상수3 </cons>
<cons>상수1 상수2 상수3 </cons>

2. XMLAGG 함수 : 여러 줄로 표시된 xml element를  한 줄로 합쳐주는 xml함수이다.
 - XML ELEMENT의 순서를 정하기 위하여 order by 정렬기준컬럼( 또는 식)을 이용할 수 있고 생략 시 rowid순으로 표시된다.

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color", col1) order by col2) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2)
------------------------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by rowid) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYROWID)
------------------------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) ) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1))
----------------------------------------------------------
<Color>빨강</Color><Color>파랑</Color><Color>노랑</Color>

 - XMLAGG는 xml element노드에서 텍스트를 추출할 수 있는 method Extract를 가지고 있는데 추출을 위한 인자로 Root로터 거리를 인자로 받는다.
   ( extract('//text()')의 의미는 첫번째 슬레시는 루트를 두번째 각 슬레시는 그 다음 단계의 element노드를 의미하고 그 단계의 text를 추출함을 지정한다. )

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by col2).extract('//text()')
  2  , dump(xmlagg(xmlelement("Color",col1) order by col2).extract('//text()')) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()')
--------------------------------------------------------------------------------------------------------------
DUMP(XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()'))
--------------------------------------------------------------------------------------------------------------
빨강파랑노랑
Typ=58 Len=48: 68,143,185,12,104,139,44,246,168,56,31,246,220,227,34,246,0,0,0,4,0,4,0,0,0,4,0,0,1,0,4,0,148,1
08,92,12,0,0,0,0,68,143,185,12,144,139,44,246

 - 마지막으로 xml DataType을 문자나 숫자형으로 변환하기위한 sub method를 제공하는데 .GetStringVal() or .GetNumberVal()이다. 
   ( 직관적으로 String 또는 Number형으로 가져오라는 뜻임을 알 수 있다. )

WOONGASM:WOONG >
  1  select xmlagg(xmlelement("Color",col1) order by col2).extract('//text()').getstringval()
  2  , dump(xmlagg(xmlelement("Color",col1) order by col2).extract('//text()').getstringval()) from testt;

XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL()
----------------------------------------------------------------------------------------------------------
DUMP(XMLAGG(XMLELEMENT("COLOR",COL1)ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL())
----------------------------------------------------------------------------------------------------------
빨강파랑노랑
Typ=1 Len=12: 187,161,176,173,198,196,182,251,179,235,182,251

이전 다수의 row에 존재하는 특정컬럼을 한 row로 합쳐서 보여주기 위해 sys_connect_by_path와 start with ~ connect by를 사용하여 불필요한 row증가와 합침등 복잡한 오퍼레이션을 했왔으나 xml함수를 이용하는 것이 보다 깔끔하고 성능 상 유리한 SQL을 작성할 수 있다는 것을 알 수 있다.

WOONGASM:WOONG > --이전 sys_connect_by_path 사용 시 
  1  select ltrim(max(sys_connect_by_path(col1,',')),',')
  2    from (
  3          select col1
  4               , row_number() over (order by col2) rn
  5            from testt
  6         )
  7  start with rn = 1
  8  connect by prior rn = rn -1
  9  /

LTRIM(MAX(SYS_CONNECT_BY_PATH(COL1,',')),',')
----------------------------------------------------------
빨강,파랑,노랑

WOONGASM:WOONG > 
  1  select * from table(dbms_xplan.display_cursor(null,null,'allstats typical'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M  |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE            |       |      2 |00:00:01.68 |      56 |       |       |         |
|*  2 |   CONNECT BY WITH FILTERING|       |      6 |00:00:01.55 |      56 |  9216 |  9216 |  4/0/0  |
|*  3 |    FILTER                  |       |      2 |00:00:00.04 |      14 |       |       |         |
|   4 |     COUNT                  |       |      6 |00:00:00.04 |      14 |       |       |         |
|   5 |      VIEW                  |       |      6 |00:00:00.03 |      14 |       |       |         |
|   6 |       WINDOW SORT          |       |      6 |00:00:00.03 |      14 |  2048 |  2048 |  2/0/0  |
|   7 |        TABLE ACCESS FULL   | TESTT |      6 |00:00:00.01 |      14 |       |       |         |
|*  8 |    HASH JOIN               |       |      4 |00:00:01.49 |      42 |  1066K|  1066K|  2/0/0  |
|   9 |     CONNECT BY PUMP        |       |      6 |00:00:00.01 |       0 |       |       |         |
|  10 |     COUNT                  |       |     18 |00:00:01.18 |      42 |       |       |         |
|  11 |      VIEW                  |       |     18 |00:00:01.18 |      42 |       |       |         |
|  12 |       WINDOW SORT          |       |     18 |00:00:00.02 |      42 |  2048 |  2048 |  2/0/0  |
|  13 |        TABLE ACCESS FULL   | TESTT |     18 |00:00:00.01 |      42 |       |       |         |
|  14 |    COUNT                   |       |      0 |00:00:00.01 |       0 |       |       |         |
|  15 |     VIEW                   |       |      0 |00:00:00.01 |       0 |       |       |         |
|  16 |      WINDOW SORT           |       |      0 |00:00:00.01 |       0 | 73728 | 73728 |         |
|  17 |       TABLE ACCESS FULL    | TESTT |      0 |00:00:00.01 |       0 |       |       |         |
------------------------------------------------------------------------------------------------------


WOONGASM:WOONG > -- XML 함수사용 시
  1  select rtrim(xmlagg(xmlelement("Color",col1,',') order by col2).extract('//text()').getstringval(),',') from testt;

RTRIM(XMLAGG(XMLELEMENT("COLOR",COL1,',')ORDERBYCOL2).EXTRACT('//TEXT()').GETSTRINGVAL(),',')
-----------------------------------------------------------------------------------------------
빨강,파랑,노랑

WOONGASM:WOONG >
  1  select * from table(dbms_xplan.display_cursor(null,null,'allstats  typical'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |       |      3 |00:00:00.72 |      21 | 73728 | 73728 |          |
|   2 |   TABLE ACCESS FULL| TESTT |      9 |00:00:00.04 |      21 |       |       |          |
-------------------------------------------------------------------



출처: http://www.oracleclub.com/lecture/1032
Oracle SQL 강좌

GROUP BY와 HAVING절, 그리고 GROUPING SETS
[2002-01-20] - 김정식 (45,752:Lv60)
114192
조회수
14
댓글수
15

 

GROUP BY절

  • - GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
  • - 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
  • - 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
  • - 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
1
2
3
4
5
6
7
8
9
10
11
12
-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
  FROM emp
 GROUP BY deptno
 ORDER BY COUNT(*) DESC;
 
 
YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3

아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

1
2
3
4
5
6
7
8
9
10
11
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
       ROUND(SUM(sal)) "급여합계"
  FROM emp
 GROUP BY deptno;
 
 
  DEPTNO   COUNT(*)    급여평균    급여합계
-------- ---------- ---------- ----------
      30          6       1567       9400
      20          5       2175      10875
      10          3       2917       8750

아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
       MAX(sal) "최고급여액", MIN(sal) "최저급여액",
       SUM(sal) "급여합계"
  FROM emp
 GROUP BY job;
 
 
JOB           인원수   평균급여액   최고급여액   최저급여액    급여합계
----------- -------- ---------- ---------- ---------- ----------
CLERK              4     1037.5       1300        800       4150
SALESMAN           4       1400       1600       1250       5600
PRESIDENT          1       5000       5000       5000       5000
MANAGER            3 2758.33333       2975       2450       8275
ANALYST            2       3000       3000       3000       6000

  • - GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
  • - 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.
1
2
3
4
5
6
7
8
9
10
11
-- GROUP BY를 이용한 부서번호 조회 예
SELECT deptno
  FROM emp
 GROUP BY deptno;
 
 
DEPTNO
------
    30
    20
    10

DISTINCT와 GROUP BY절

  • - DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
  • - DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
  • - GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
  • - 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
  • - 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
  • - 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

아래의 예제는 동일한 결과를 반환한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- DISTINCT를 사용한 중복 데이터 제거
SELECT DISTINCT deptno FROM emp;
 
 
-- GROUP BY를 사용한 중복 데이터 제거
SELECT deptno FROM emp GROUP BY deptno;
 
 
DEPTNO
------
    30
    20
    10

하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다

집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자

1
2
3
4
5
6
7
8
9
10
11
-- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
SELECT COUNT(DISTINCT d.deptno) "중복제거 수",
       COUNT(d.deptno) "전체 수"
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;
 
 
-- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
SELECT deptno, MIN(sal)
  FROM emp
 GROUP BY deptno;


출처: http://zinlee.tistory.com/185
개발을 하다보면 update와 insert문 둘중에 한가지만 실행해야 할 때가 있다.

예로 해당 Data가 있으면 update해주고 없으면 insert를 해야하는 경우가 그렇다.

이럴 경우에는 오라클의 MERGE 구문을 이용해서 처리하면 간단하다.

DUAL ON 뒤의 조건에 해당하는 Data가 있으면
WHEN MATCHED THEN의 구문을 통해 UPDATE문이 실행이 되고,

해당하는 Data가 없다면
WHEN NOT MATCHED THEN 구문을 통해 INSERT문이 실행이 된다.


◇ FORMAT
─────────────────────────────────────────────

MERGE INTO dest_table_name [alias]
USING (source_table_name or view or subquery) [alias]

ON (join condition)
WHEN MATCHED THEN 
UPDATE SET col1 = value1[, col2 = value2…]
WHEN NOT MATCHED THEN 
INSERT [(col1, col2, ... coln)] VALUES(value1, value2 ... valuen)


desc_table_name ....... UPSERT¹ 하고자 하는 테이블명

alias ......................... 조인조건, UPDATE, INSERT등레 사용될 Alias명

source_table_name .... UPSERT할 값이 들어있는 테이블명

view ......................... UPSERT할 값이 들어있는 뷰테이블명

subquery .................. UPSERT할 값을 SELECT 한 쿼리문장

join condition ............ UPSERT하기위한 조건 (WHERE절에 해당한다)

지정한 join condition에 의하여 그 값이 일치하면 UPDATE를 일치하지 않으면 INSERT를 수행한다.

※ ON 절에 기술된 컬럼이 WHEN MATCHED THEN 다음에 오는 UPDATE 문장에는 올수없다

즉, KEY에 해당하는 부분일 수 있으므로 조건에 해당된 컬럼이 UPDATE되는 것을 방지한다.

─────────────────────────────────────────────


MERGE INTO SWTB_SAFETY_WORK_SUB USING DUAL ON (REQ_NO = ? AND WORK_NO = ?)
WHEN MATCHED THEN 
UPDATE SET 
CHECK_TIME = ?, 
CHECK_PERSON = ?, 
CHECK_RESULT = ? 
WHEN NOT MATCHED THEN
INSERT (REQ_NO, WORK_NO, CHECK_TIME, CHECK_PERSON, CHECK_RESULT, CHECK_EMP_NO)
VALUES(?, ?, ?, ?, ?, ?)




다른 예시--------------------------

MERGE INTO TABLE1 A
USING
(
SELECT

:SEQ_NO AS seq_no, :KOR_NAME AS kor_name, :JOB AS job, :STATUS AS status

FROM dual

) B
ON
(
A.SEQ_NO = B.SEQ_NO

)
WHEN MATCHED THEN
UPDATE SET A.JOB = B.JOB
WHEN NOT MATCHED THEN
INSERT VALUES (B.SEQ_NO, B.KOR_NAME, B.JOB, B.STATUS)

저작자 표시 비영리 동일 조건 변경 허락


select 문 이용하는 케이스

출처: 즐거움을 찾자 Find Fun!! :: 오라클 MERGE INTO - 한번에 INSERT, UPDATE 하기

아래는 실제로 사용했던 예. 테이블 명은 임의로 바꿨다. 소스 테이블에 올 내용은 아래처럼 SELECT로 구성해도 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
merge into target_table tt
    using
        (
            select
                te.te_cd tt_cd
                ,te.description tt_disp
                ,te.jt_no jt_no
                ,decode(te.is_flag, 'normal', 'Y', 'N') is_flag
            from
                temp_table te
            left join join_table jt
                on te.te_no=jt.jt_no
            where
                1=1
        ) so
    on (tt.tt_cd=so.tt_cd and tt.jt_no=so.jt_no)
     
    when matched then
        update set
            tt.is_flag=so.is_flag
            ,tt.tt_disp=so.tt_disp
 
 
    when not matched then
            insert (tt_no, tt_cd, tt_disp, jt_no, is_flag, reg_date)
            values(
                sq_target_table.nextval
                , so.tt_cd
                , so.tt_disp
                , so.jt_no
                , so.is_flag
                , sysdate
            );


  1. 줄쟁이 2016.10.05 11:50

    관리자의 승인을 기다리고 있는 댓글입니다

+ Recent posts