[Oracle] 오라클 MERGE INTO 사용법 / 머지를 사용하여 INSERT & UPDATE & DELETE 하나의 쿼리문으로 한번에 처리!

 

오라클에서 쿼리문을 작성하다 보면, 하나의 쿼리문으로 INSERT, UPDATE, DELETE 작업을 해야 하는 경우가 있다.

예를 들어 이미 존재하는 값은 UPDATE, 신규이면 INSERT를 하고싶은 경우가 많을 거다.

이럴 때에는 MERGE 문을 사용하면 된다.

 

MERGE문 사용조건
오라클 9i 사용가능
DELETE 절은 10g부터 사용가능

 

 MERGE 문 사용법 

MERGE
   INTO { INSERT/UPDATE/DELETE할 테이블 | 뷰 } (별칭)
USING {테이블 | 뷰| 서브쿼리} (별칭) - 원하는 결과를 조회하는 곳
   ON ( 조건절 ) - 조회한 결과와 테이블을 연결하는 곳
WHEN MATCHED THEN 조건이 일치하는 경우
    { UPDATE | DELETE 절}
WHEN NOT MATCHED THEN 조건이 불일치하는 경우
    { INSERT절 }

 

[예제]

 단일 테이블 사용법 

MERGE 
 INTO emp a
USING dual
   ON (a.empno = 7788)
 WHEN MATCHED THEN
      UPDATE
         SET a.deptno = 20
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.ename, a.deptno)
      VALUES (7788, 'SCOTT', 20);

단일(자신)의 테이블에 MERGE문은 자주 사용한다.

USING 절에 테이블 대신 dual을 사용하면 된다. ON 조건절이 일치하면 UPDATE, 불일치하면 INSERT를 하는 쿼리이다.

 

 JOIN 사용법 

MERGE 
 INTO job_history a
USING emp b
   ON (a.empno = 7788 AND a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE
         SET a.job = b.job
           , a.deptno = b.deptno
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.job, a.deptno)
      VALUES (b.empno, b.job, b.deptno);

조인을 사용하는 방법은 UPDATE 문 대신 사용하는 경우가 많다. 기본 UPDATE 문도 조인을 할 수 있지만 쿼리문이 조금 복잡해 지는 경향이 있다. MERGE 문을 사용하면 UPDATE 문 조인을 쉽게 사용할 수 있다.

 

 서브쿼리(인라인뷰)를 사용하는 법 

MERGE 
 INTO emp a
USING (SELECT aa.empno
            , aa.job
            , aa.deptno
         FROM emp aa
            , dept bb
        WHERE aa.empno = 7788
          AND aa.deptno = bb.deptno) b
   ON (a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE
         SET a.job = b.job
           , a.deptno = b.deptno
 WHEN NOT MATCHED THEN
      INSERT (a.empno, a.job, a.deptno)
      VALUES (b.empno, b.job, b.deptno);

서브쿼리의 결과와 조인하여 MERGE 문을 사용할 수 있다.

 

 WHERE절 사용 

MERGE 
 INTO emp a
USING dual
   ON (a.empno = 7788)
 WHEN MATCHED THEN
      UPDATE
         SET a.deptno = 20
       WHERE a.job = 'ANALYST';

INSERT 절에서 WHERE 절을 사용하면 오류가 발생한다.

 

 DELETE 절 사용 

MERGE 
 INTO emp a
USING dual
   ON (a.empno = 7788)
 WHEN MATCHED THEN
      UPDATE
         SET a.deptno = 20
       WHERE a.job = 'ANALYST'
      DELETE
       WHERE a.job <> 'ANALYST';

WHERE 절을 사용하지 않고 DELETE 문만 작성하면 MATCHED 된 모든 데이터는 삭제된다.

 

※ 주의 ※

ON 조건절에 사용한 컬럼을 업데이트하면 오류가 발생한다.

 

[참고사이트]

[Oracle] 오라클 MERGE INTO 사용법 & 노하우 정리