merge into 9i,10g차이점

출처 : http://www.dbguide.net/blog/post/post_view.jsp?urlid=y2k0314&cnum=14539&pnum=8633
참고 : merge enhancements in 10g

☞ MERGE
 - 한번에 조건에 따라 INSERT,UPDATE 가 가능합니다.
 - 해당 ROW가 있으면 UPDATE, 없으면 INSERT 문장이 실행 됩니다.
 
 ◈syntax)
 MERGE INTO target_table_name
       USING (table|view|subquery) ON (join condition)
 WHEN MATCHED THEN
      UPDATE SET col1 = val1[, col2 = val2…]
 WHEN NOT MATCHED THEN
      INSERT(...) VALUES(...)
     
 MERGE INTO table_name alias                       --테이블 명만 올수 있음(셀렉트 쿼리 No)
        USING (table|view|subquery) alias        --일반적으로 쓰는 쿼리 Ok
        ON (join condition)                                --where 절이라고 생각하면됨(조건에 서브 OK)
   WHEN MATCHED THEN                      --데이터 중복건이 있는경우 Update
                UPDATE SET col1 = val1[, col2 = val2…]
   WHEN NOT MATCHED THEN              -- 중복건이 없는 경우 처리 Insert
                INSERT (컬럼리스트) VALUES (값들....);

 ◈ syntax 설명
 - INTO : DATA가 UPDATE되거나 INSERT될 TABLE이름을 지정 합니다.

 - USING : 대상 TABLE의 DATA와 비교한 후 UPDATE 또는 INSERT할 대상이 되는 DATA의 SOURCE 테이블 또는 뷰를 지정
 - ON  : UPDATE나 INSERT를 하게 될 조건으로, 해당 condition을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 됩니다.

 - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용

 - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용
SQL> create table master  (no number, code varchar2(5),fixed varchar2(5));
 
SQL> insert into master values (1, 'A', 'N');
     insert into master values (2, 'B', 'N');
     insert into master values (3, 'C', 'Y');
     insert into master values (4, 'D', 'N');
     insert into master values (5, 'E', 'Y');
     Commit;
 
SQL> select * from master;
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         2 B     N
         3 C     Y
         4 D     N
         5 E     Y
 
SQL> create table detail (no number,     code varchar2(5));

 
SQL> insert into detail values (2, 'G');
     insert into detail values (3, 'H');
     insert into detail values (6, 'F');
     insert into detail values (7, 'Z');
     Commit;
SQL> select * from detail;
        NO CODE
---------- -----
         2 G
         3 H
         6 F
         7 Z
 
SQL> commit;
커밋이 완료되었습니다.
 
여기서, detail 테이블을 이용하여 master 테이블을 merge 해보도록 하겠습니다.
예를 들면, master 테이블의 no 컬럼의 값이 detail 테이블의 no 컬럼의 값과 일치되는 경우에는
mater 테이블의 데이터를 update하고, 일치하지 않는 경우는 insert를 수행해야 합니다.
그 결과는 다음과 같이 출력되어야 합니다.
 
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         2 G     N  <-- update
         3 H     Y  <-- update
         4 D     N
         5 E     Y 
         6 F     N  <-- insert
         7 Z     N  <-- insert
 
먼저, Oracle 9i에서 지원하는 merge는 다음과 같습니다.
 
SQL> merge into master m
    using detail d
    on (m.no=d.no)
    when matched then
      update set m.code=d.code
    when not matched then
      insert values(d.no, d.code, 'N');
4 행이 병합되었습니다.
 
SQL> select * from master;
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         2 G     N
         3 H     Y
         4 D     N
         5 E     Y
         6 F     N
         7 Z     N
7 개의 행이 선택되었습니다.
 
SQL> rollback;
롤백이 완료되었습니다.
 
여기서, master의 fixed 컬럼의 값이 'N'인 경우에만 update를 수행해야 한다고 가정합니다.
Oracle 10g에서는 merge 문의 update 및 insert에 where 절을 포함시킬 수 있게 되었기 때문에 간단하게 작업을 수행 할 수 있습니다.
 
SQL> merge into master m
  2  using detail d
  3  on (m.no=d.no)
  4  when matched then
  5    update set m.code=d.code
  6    where m.fixed='N'
  7  when not matched then
  8    insert values(d.no, d.code, 'N');
3 행이 병합되었습니다.
 
SQL> select * from master;
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         2 G     N  <-- update
         3 C     Y  <-- 변경되지 않았습니다.
         4 D     N
         5 E     Y
         6 F     N  <-- insert
         7 Z     N  <-- insert
7 개의 행이 선택되었습니다.
 
SQL> rollback;
롤백이 완료되었습니다.
 
추가로, detail 테이블의 code 컬럼이 'Z'가 아닌 경우에만 insert를 수정한다고 가정하면,
마찬가지로 insert 문에 where 절을 추가하면 됩니다.
 
SQL> merge into master m
  2  using detail d
  3  on (m.no=d.no)
  4  when matched then
  5    update set m.code=d.code
  6    where m.fixed='N'
  7  when not matched then
  8    insert values(d.no, d.code, 'N')
  9    where d.code <> 'Z';
2 행이 병합되었습니다.
 
SQL> select * from master;
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         2 G     N  <-- update
         3 C     Y  <-- 변경되지 않았습니다.
         4 D     N
         5 E     Y 
         6 F     N  <-- insert
6 개의 행이 선택되었습니다.
 
이외에 update 구문에 delete를 사용 할 수 있습니다.
delete는 update 구문이 수행 된 이후, where 조건의 만족 여부에 따라 실행됩니다.
예를 들어, master 테이블에서 update 된 행 중에서 fixed 컬럼이 'N'인 행을 삭제하려면 다음과 같이 실행합니다.

SQL> merge into master m
  2  using detail d
  3  on (m.no=d.no)
  4  when matched then
  5    update set m.code=d.code
  6    delete where m.fixed='N'
  7  when not matched then
  8    insert values(d.no, d.code, 'N');
4 행이 병합되었습니다.
 
SQL> select * from master;
        NO CODE  FIXED
---------- ----- -----
         1 A     N
         3 H     Y
         4 D     N
         5 E     Y
         6 F     N
         7 Z     N
6 개의 행이 선택되었습니다.
 
이상입니다.

 

-- Both clauses present.
   MERGE INTO test1 a 
         USING all_objects b   
         ON (a.object_id = b.object_id) 
    WHEN MATCHED THEN   
         UPDATE SET a.status = b.status 
    WHEN NOT MATCHED THEN   
         INSERT (object_id, status)    VALUES (b.object_id, b.status);
-- No matched clause, insert only.
   MERGE INTO test1 a 
         USING all_objects b   
         ON (a.object_id = b.object_id) 
    WHEN NOT MATCHED THEN   
         INSERT (object_id, status)    VALUES (b.object_id, b.status);
-- No not-matched clause, update only.
   MERGE INTO test1 a 
         USING all_objects b   
         ON (a.object_id = b.object_id) 
    WHEN MATCHED THEN   
         UPDATE SET a.status = b.status;