oracle merge 문

merge 문은 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작이다.
예를 들어, 하루에 수만건씩 발생하는 데이터를 하나의 테이블에 관리할 경우 대량의 데이터로 인해 질의문의 성능이 저하된다.
이런 경우, 지점별로 별도의 테이블에서 관리하다가 년말에 종합 분석을 위해 하나의 테이블로 합칠 때 merge 문을 사용하면 편리하다.

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>