너는 개발자냐?

merge하고자 하는 소스 테이블의 행을 읽어 타킷 테이블에 매치되는 행이 존재하면 새로운 값으로 UPDATE를 수행하고, 만일 매치되는 행이 없을 경우 새로운 행을 타킷 테이블에서 INSERT를 수행한다.

Merge는 여러 데이터 소스로부터 다양한 데이터를 수집해서 작업을 수행하는 dataware housing등에 적용하면 유용하다.

merge 문에서 where 절은 사용할 수 없으며 대신 on이 사용된다. 또한 when matched then 절과 when not matched then 절에는 테이블명 대신 alias를 사용한다.

【형식】
    MERGE [hint] INTO [schema.] {table ¦ view} [t_alias]
      USING {{[schema.] {table ¦ view}} ¦
            subquery} [t_alias]
      ON (condition) [merge_update_clause] [merge_insert_clause] [error_logging_clause];

【merge_update_clause 형식】
   WHEN MATCHED THEN UPDATE SET {column = {expr ¦ DEFAULT},...}
     [where_clause] [DELETE where_clause]

【merge_insert_clause 형식】
   WHEN MATCHED THEN INSERT [(column,...)]
    VALUES ({expr,... ¦ DEFAULT}) [where_clause]
   
【where_clause 형식】
   WHERE condition

【error_logging_clause 형식】
   LOG ERROR [INTO [schema.] table] [(simple_expression)]
     [REJECT LIMIT {integer ¦ UNLIMITED}]

【예제】
SQL> create table emp(
  2  id number primary key, 
  3  name varchar2(10) not null,
  4  salary  number,
  5  bonus number default 100);

Table created.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER
 BONUS                                              NUMBER

SQL> insert into emp(id,name,salary) values(1001,'jijoe',150);
SQL> insert into emp(id,name,salary) values(1002,'cho',130);
SQL> insert into emp(id,name,salary) values(1003,'kim',140);
SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1001 jijoe             150        100
      1002 cho               130        100
      1003 kim               140        100

SQL> create table bonus(id number, bonus number default 100);

Table created.

SQL> insert into bonus(id)
  2    (select e.id from emp e);

3 rows created.

SQL> select * from bonus;

        ID      BONUS
---------- ----------
      1001        100
      1002        100
      1003        100

SQL> merge into bonus D
  2    using (select id,salary from emp) S
  3    on (D.id = S.id)
  4  when matched then update set
  5    D.bonus=D.bonus + S.salary*0.01
  6  when not matched then insert(D.id, D.bonus)
  7    values(S.id,S.salary*0.01);

3 rows merged.

SQL> select * from bonus;

        ID      BONUS
---------- ----------
      1001      101.5
      1002      101.3
      1003      101.4

SQL>
 
【예제】
SQL> delete from bonus where id=1003;

1 row deleted.

SQL> merge into bonus D
  2    using (select id,salary from emp) S
  3    on (D.id = S.id) 
  4  when matched then update set
  5    D.bonus=D.bonus + S.salary*0.02
  6  when not matched then insert(D.id, D.bonus)
  7    values(S.id, S.salary*0.01);

3 rows merged.

SQL> select * from bonus;

        ID      BONUS
---------- ----------
      1001      104.5
      1002      103.9
      1003        1.4

SQL>

【예제】
SQL> show user
USER is "OE"
SQL> CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

Table created.

SQL> INSERT INTO bonuses (employee_id)
  2    (SELECT e.employee_id FROM employees e, orders o
  3      WHERE e.employee_id = o.sales_rep_id
  4      GROUP BY e.employee_id);

7 rows created.

SQL> SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        156        100
        158        100
        159        100
        160        100
        161        100

7 rows selected.

SQL> MERGE INTO bonuses D
  2    USING (SELECT employee_id, salary, department_id FROM employees
  3    WHERE department_id = 80) S
  4    ON (D.employee_id = S.employee_id)
  5    WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary * .01
  6      DELETE WHERE (S.salary > 8000)
  7    WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
  8      VALUES (S.employee_id, S.salary*0.1)
  9      WHERE (S.salary <= 8000);

7 rows merged.

SQL> SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        159        180
        160        175
        161        170

SQL>

merge에 의한 row 삽입



http://radiocom.kunsan.ac.kr/lecture/oracle/sql/merge_insert.html

Merge를 사용하여 row 삽입
merge를 하려고 하는 source 테이블의 행을 읽어 target 테이블에 매치되는 행이 있을 경우에는 새로운 값으로 UPDATE를 수행하고,
만일 매치되는 행이 없을 경우에는 새로운 행으로 인식하여 target 테이블에 INSERT를 수행한다. 

merge 문을 수행하기 위해서는 다음과 같은 권한이 주어져야 한다.
• target 테이블 새로운 행을 삽입하기 위한 insert 권한.
• target 테이블에서 기존 행을 갱신하기 위한 update 권한.
• source 테이블 행을 검색하기 위한 select 권한.
【형식】
	MERGE INTO 테이블명 alias
	USING [테이블명 | 뷰명 | subquery] alias
	ON 조건
	WHEN MATCHED THEN
		UPDATE SET ...
	WHEN NOT MATCHED THEN
		INSERT ...
		VALUES ...;
• merge into 뒤에 테이블을 삽입하거나 target 테이블명을 지정한다.
• WHERE 절은 사용할 수 없고, 대신 ON 절에 JOIN 조건을 기술한다.
• WHEN MATCHED THEN 절에는 ON 절에서 기술한 JOIN 조건을 만족하는 즉, source 테이블과 target 테이블간에 매치되는 행이 존재할 경우 target 테이블의 행을 새로운 값으로 UPDATE하기 위한 구문을 기술한다.
• WHEN NOT MATCHED THEN 절에는 ON 절에서 기술한 JOIN 조건을 만족하지 않는 경우 즉, target 테이블에 행을 INSERT하기 위한 구문을 기술한다.
• WHEN MATCHED THEN 절과 WHEN NOT MATCHED THEN 절에는 테이블명 대신 alias를 사용한다. 
【예제】
SQL> create table dept1
  2  AS select * from dept;
 
테이블이 생성되었습니다.
 
SQL> create table dept2
  2  AS select * from dept;
 
테이블이 생성되었습니다.
 
SQL> insert into dept2 values(50,'KIM_gun','COREA');
 
1 개의 행이 만들어졌습니다.
 
SQL> update dept2 
  2  set dname='LEE_gun' where deptno=30;
 
1 행이 갱신되었습니다.
 
SQL> select * from dept2;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 LEE_gun        CHICAGO
        40 OPERATIONS     BOSTON
        50 KIM_gun        COREA
 
SQL> select * from dept1;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
SQL> merge into dept1 d
  2  using dept2 de
  3  on (d.deptno=de.deptno)  ☜ ON 조건절에 들어 있는 컬럼은 update절에 넣으면 오류발생
  4  when matched then
  5  update set
  6   d.dname=de.dname,
  7   d.loc=de.loc
  8  when not matched then
  9   insert values(de.deptno, de.dname, de.loc);
 
5 행이 병합되었습니다.
 
SQL> select * from dept1;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 LEE_gun        CHICAGO
        40 OPERATIONS     BOSTON
        50 KIM_gun        COREA
 
SQL> 
테이블 aa
1111 
홍길동 
2222 
길동무 
3333 
죽마고우 
테이블 aa를 bb로 merge
====================> 
merge전 bb의 내용
1111 
홍길동 
2222 
옛친구 
테이블 bb의 값이 바뀜
====================> 
merge후 bb의 내용
1111 
홍길동 
2222 
길동무 
3333 
죽마고우 
SQL> create table aa(id number(4), name varchar2(10));
 
테이블이 생성되었습니다.
 
SQL> create table bb (id number(4), name varchar2(10));
 
테이블이 생성되었습니다.
 
SQL> insert into aa values(1111,'홍길동');
 
1 개의 행이 만들어졌습니다.
 
SQL> insert into aa values(2222,'길동무');
 
1 개의 행이 만들어졌습니다.
 
SQL> insert into aa values(3333,'죽마고우');
 
1 개의 행이 만들어졌습니다.
 
SQL> insert into bb values(1111,'홍길동');
 
1 개의 행이 만들어졌습니다.
 
SQL> insert into bb values(2222,'옛친구');
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from aa;
 
        ID NAME
---------- ----------
      1111 홍길동
      2222 길동무
      3333 죽마고우
 
SQL> select * from bb;
 
        ID NAME
---------- ----------
      1111 홍길동
      2222 옛친구
 
SQL> merge into bb target
  2  using aa source
  3  on (source.id=target.id)
  4  when matched then
  5    update set
  6     target.name=source.name
  7  when not matched then
  8    insert values(source.id,source.name);
 
3 행이 병합되었습니다.
 
SQL> select * from bb;
 
        ID NAME
---------- ----------
      1111 홍길동
      2222 길동무
      3333 죽마고우
 
SQL>