[오라클] MERGE INTO 구문 정리

새로 시스템을 구축하다는 과정에서 테이블 구조상 MERGE INTO 구문을 많이 사용하게 되었다.  모든 잘 알면 피와 살이 되고 개발의 질이 향상된다는 사실은 당연지사!!  이것저것 화면에서 처리하려고 했고 또는 JAVA 단에서 많은 로직을 구현을 해가며 복잡하게 했던 것들.. 사실은 쿼리로 해결하면 간단히 해결될 것들이 많다! 그 중에 하나가 MERGE INTO 구문의 사용이다. (물론 머리싸메가며 쿼리로 짜기 어려운것들.. 반대로 자바단이라 화면단에서 기능 구현을 함으로써 간단히 처리가능한 경우도 많다.) 그러니 적절한 선에서 많이 알고있으면 쉬우면서도 시스템 성능을 떨어뜨리지 않는 범위내에서 잘 선택해서 개발하는 것이 좋다.

 

우선 MERGE 란 뜻은 무엇인가? 충돌나지 않게 합친다는 개념이다.   SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게  누락되지 않게 통합하기 위해 사용한다.  오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재하는 데이터는 그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다. 즉,

 

MERGE INTO 구문은 대상 테이블 해당 KEY에 맞는 데이터가 이미 존재하면 UPDATE!!,존재하지 않으면 INSERT를 하여 테이블 ROW가 충돌나지 않으며 데이터를 UPDATE,INSERT 등의 작업을 한번에 해줄 수 있다. 

 

#오라클 MERGE INTO 문법#

 

MERGE INTO [1. 테이블 명 혹은 VIEW명] - Update또는 Insert할 테이블 명 혹은 뷰

USING [2. 조회서브쿼리]      --(만약 INTO절의 동일 테이블이라면  dual 사용)

ON [1과2의 조인 조건]  - 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]

WHEN MATCHED THEN   -일치되는 경우 UPDATE

UPDATE SET -- ※조인조건(on)절에 사용한 컬럼은 UPDATE가 불가하다!!

[컬럼1] = [값1],

[컬럼2] = [값2]

...

WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT

INSERT(컬럼1, 컬럼2...)

VALUES(값1, 값2...)

 

※오라클 9i 버전 이상부터 사용이 가능하다.

※조건절에 사용한 컬럼은 UPDATE가 불가하다!!

 

그리고 놀라운 사실!! UPDATE 구문만 가능한게 아니라 상황에 따라서는 DELETE 구문도 가능하다! 잘 쓸 경우는 없지만. 존재하는 ROW는 삭제하고 존재하지 않는 ROW는 삽입해야하는 경우라든지 다양하게 응용 하여 한번에 삽입 삭제 변경 처리가 가능하다.

 

MERGE INTO [1. 테이블 명] - Update또는 Insert할 테이블 명

USING [2. 조회쿼리] --(만약 동일 테이블이라면 dual 사용)

ON [1과2의 조인 조건] -- 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]

WHEN MATCHED THEN  -일치되는 경우 DELETE

DELETE [테이블 명] WHERE  [컬럼1] = [값1] AND [컬럼2] = [값2]...

WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT

INSERT(컬럼1, 컬럼2...)

VALUES(값1, 값2...)

 

※오라클 10g 버전 부터 DELETE구문 가능하다.

 

위의 문법처럼 작성한 두가지 쿼리예이다.

 -테이블의 비교 대상이 같은 경우 다음처럼 DUAL을 사용하면 된다.

MERGE INTO EXTTB020 ORG
        USING (
                SELECT TO_CHAR(SYSDATE,'YYYYMMDD') PROC_DATE,
                        p_item_id  ITEM_ID,
                        p_rev_no REV_NO,
                        v_matr_type MATR_TYPE,
                        '0000'  COMPANY,
                        '0000'  PLANT,
                        p_chg_emp CHG_EMP
                FROM  DUAL   -비교 대상이 같을 경우  DUAL 사용 
              ) TMP
          ON  (
                    ORG.ITEM_ID = TMP.ITEM_ID
              AND ORG.REV_NO  = TMP.REV_NO 
              AND ORG.MATR_TYPE = TMP.MATR_TYPE
              AND ORG.COMPANY  = TMP.COMPANY
              AND ORG.PLANT  = TMP.PLANT
              ) WHEN MATCHED THEN
                     UPDATE
                        SET ORG.CONFIRM  = TMP.CONFIRM,
                            ORG.EMP_NO  = TMP.CHG_EMP,
                            ORG.CHG_DATE = SYSDATE
                WHEN NOT MATCHED THEN
                INSERT
                (
                  ORG.PROC_DATE, ORG.ITEM_ID, ORG.REV_NO, ORG.MATR_TYPE, ORG.COMPANY,   

                  ORG.PLANT, ORG.EMP_NO, ORG.CHG_DATE
                ) VALUES
                (
                    TMP.PROC_DATE, TMP.ITEM_ID, TMP.REV_NO, TMP.MATR_TYPE, TMP.COMPANY,

                   TMP.PLANT,  TMP.CHG_EMP, SYSDATE
                );

 

 MERGE INTO EXTTB011 ORG
        USING (
                SELECT T1.ITEM_ID,
                        T1.ATTR_ID,
                        T1.SCOPE,
                        T1.VALUE WORK_VALUE,
                        T1.CHG_EMP,
                        SYSDATE CHG_DATE
                FROM EXTTB010 T1
                WHERE T1.ITEM_ID = p_item_id
                AND  (T1.SCOPE) IN
                        (
                        SELECT v_region
                        FROM DUAL
                        UNION ALL
                        SELECT DISTINCT COMPANY
                        FROM EXTCT050
                        WHERE PLANT IN (       
                                         SELECT  DISTINCT SCOPE FROM mdmtb011
                                                    WHERE item_id = p_item_id
                                                    AND attr_id = '69'
                                                    AND REV_NO = p_rev_no
                                                    AND TO_CHAR(CHG_DATE,'YYYYMMDDHHMM') IN (
                                                                 SELECT MAX(TO_CHAR(CHG_DATE,'YYYYMMDDHHMM')) CHG_DATE  FROM mdmtb011
                                                                WHERE item_id = p_item_id
                                                                AND attr_id = '69' /* PLANT */
                                                                AND REV_NO = p_rev_no
                                                    )
                                        ) 
                         )
              ) TMP
          ON  (
                ORG.ITEM_ID = TMP.ITEM_ID
              AND ORG.REV_NO = 'EXT'  
              AND ORG.ATTR_ID = TMP.ATTR_ID
              AND ORG.SCOPE = TMP.SCOPE
              ) WHEN MATCHED THEN
                     UPDATE
                        SET ORG.VALUE  = TMP.WORK_VALUE,
                            ORG.CHG_EMP  = TMP.CHG_EMP,
                            ORG.CHG_DATE = TMP.CHG_DATE
                WHEN NOT MATCHED THEN
                INSERT
                (
                  ORG.ITEM_ID, ORG.REV_NO, ORG.ATTR_ID, ORG.SCOPE, ORG.VALUE, ORG.CHG_EMP, ORG.CHG_DATE
                ) VALUES
                (
                  TMP.ITEM_ID, p_rev_no, TMP.ATTR_ID, TMP.SCOPE, TMP.WORK_VALUE, TMP.CHG_EMP, TMP.CHG_DATE
                );

오라클에서 MERGE  INTO 구문은 자주 사용하는 쿼리 구문이니 익혀두고  이용하면 좋을 듯 하다. 단, 9i 이상 오라클에서 사용 가능하다고 했지만 9i에서의 Merge 구문은 불완전 (DELETE구문도 10g 부터 사용가능 )하다. 또한 구문상 제약사항이 많기때문에 ON 구문에서 조건식을 잘못 넣으면 작동이 안된다; 10g 이상부터 구문을 자유롭게 이용할 수있다. 또한 Trigger 발생이 안된다는 것도 주의해야 하겠다. 그리고 당연한 애기겠지만 단순 INSERT 구문이나 UPDATE구문을 써도 문제가 안되는데 궂이 MERGE INTO를 남발? 하는것도 성능이 떨어지기 때문에(100~200만건 이상 TABLE에서는 느려지는듯 ㅜㅜ) 꼭 필요할 때 잘 쓰도록 하자^^