[Oracle] Merge into using on 이해하기, 사용하는 방법

오라클에서 Merge Statement 는 쿼리 한 문장으로 인서트나 업데이트 여부를 조건에 따라 판별할 수 있도록 지원하는 기능을 제공합니다. 머지가 사전적으로 병합을 의미하는 단어라는 점에서 해당 부분을 유추하실 수 있을 것입니다. 머지는 데이터조작어(DML : Data Manipulation Language)와 연관이 있습니다. insert 구문과 update 구문이 바로 그것입니다.

Merge into

즉, merge의 병합한다는 뜻은 두 개의 테이블(뷰)을 비교하여 조건에 따라 insert와 update(delete)를 동시에 수행하여 테이블의 내용을 합치는 기능을 제공한다는 것이지요. 머지 절에서 인서트, 업데이트, 딜리트가 어떻게 이루어지는지 안내해드리는 시간을 가지도록 하겠습니다.

주제는 다음과 같으며 Merge의 문법을 이해하고 사용 예시를 간략하게 알아보도록 하겠습니다.

Oracle Merge into using on (merge statement syntax)
Into Clause
Using Clause
Merge Update Clause
When Matched
Delete
Merge Insert Clause
When Not Matched
Merge Example

Oracle Merge into using on (merge statement syntax)

기본적인 문법은 다음과 같습니다.

MERGE INTO (target table/view) USING
(source table/view)
ON (conditions)
WHEN NOT MATCHED THEN
INSERT ~
[WHERE]
WHEN MATCHED THEN
UPDATE SET ~
[DELETE WHERE]

Into Clause

MERGE INTO target (table/view)

저장 또는 수정하려는 대상 테이블(뷰)을 지정합니다.

보통 같은 컬럼을 사용하기에 alias를 지정하는 편입니다.

Using Clause

insert, update, delete 하려는 소스 테이블/뷰 또는 서브쿼리 결과 집합입니다.

보통 하나의 데이터를 머지 하는 경우 dual 테이블을 활용합니다.

Merge Update Clause

머지에서 업데이트 절에 대한 안내입니다. 업데이트가 되려면 조건에 맞는(매치하는) 행이 있어야 하기에 When Matched와 함께하며, 업데이트 이외에도 삭제가 가능하기에 delete도 상황에 따라 가능합니다.

When Matched

When Matched then과 함께 합니다.

On절에서 명시한 조건이 일치하는 경우 업데이트 절을 실행합니다.

Delete

[DELETE WHERE]는 생략할 수 있습니다.

DELETE 절을 명시하는 경우, 대상이 되는 값은 업데이트 이후의 값입니다.

업데이트 이전 값(original value)을 먼저 삭제하고 싶다고 delete를 앞으로 옮기시면 안 됩니다.(오류 발생)

머지는 기본적으로 저장, 수정을 목적으로 하기 때문입니다.

타겟 테이블에 이미 {EMPNO}가 9999이고 {JOB}이 'OLD'인 값이 있으므로 해당 구문을 실행하면 해당 로우는 삭제가 됩니다.

{EMPNO}가 9999인 로우의 {JOB}이 'NEW'로 갱신되며 삭제처리는 하지 않습니다.

Merge Insert Clause

머지에서 인서트 절에 대한 안내입니다. 인서트가 되려면 조건에 맞는(매치하는) 행이 없어야 하기에 When Not Matched와 함께 합니다.

When Not Matched

When Not Matched then과 함께 합니다.

당연히 일치하는 레코드가 없어야 insert를 하겠죠?

타겟과 소스 간의 On 조건이 불일치하는 경우 인서트 절을 실행합니다.

INSERT VALUES 이후에 필요 시 WHERE 조건을 추가할 수 있습니다.

Merge Example

using 대상이 되는 테이블을 dual로 할 수도 있고 특정 테이블을 지정할 수도 있습니다.
머지를 어떻게 활용할 수 있는지 2가지를 기준으로 예시를 보여드리겠습니다.

DUAL 테이블로 1개 레코드 인서트/업데이트

MERGE INTO USERNAME.EMP A USING
 (SELECT
  9999 as EMPNO,
  'BEOMSANG' as ENAME,
  NULL as JOB,
  NULL as MGR,
  TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS') as HIREDATE,
  1 as SAL,
  NULL as COMM,
  10 as DEPTNO
  FROM DUAL) B
ON (A.EMPNO = B.EMPNO)
WHEN NOT MATCHED THEN 
INSERT (
  EMPNO, ENAME, JOB, MGR, HIREDATE, 
  SAL, COMM, DEPTNO)
VALUES (
  B.EMPNO, B.ENAME, B.JOB, B.MGR, B.HIREDATE, 
  B.SAL, B.COMM, B.DEPTNO)
WHEN MATCHED THEN
UPDATE SET 
  A.ENAME = B.ENAME,
  A.JOB = B.JOB,
  A.MGR = B.MGR,
  A.HIREDATE = B.HIREDATE,
  A.SAL = B.SAL,
  A.COMM = B.COMM,
  A.DEPTNO = B.DEPTNO    
;

테이블/뷰 복수 개의 레코드 머지

테이블, 뷰, 서브쿼리 등으로 머지를 원하는 객체를 구성할 수 있습니다.

MERGE INTO USERNAME.EMP A USING
 (SELECT
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
  FROM USERNAME.EMP) B
ON (A.EMPNO = B.EMPNO)
WHEN NOT MATCHED THEN 
INSERT (
  EMPNO, ENAME, JOB, MGR, HIREDATE, 
  SAL, COMM, DEPTNO)
VALUES (
  B.EMPNO, B.ENAME, B.JOB, B.MGR, B.HIREDATE, 
  B.SAL, B.COMM, B.DEPTNO)
WHEN MATCHED THEN
UPDATE SET 
  A.ENAME = B.ENAME,
  A.JOB = B.JOB,
  A.MGR = B.MGR,
  A.HIREDATE = B.HIREDATE,
  A.SAL = B.SAL,
  A.COMM = B.COMM,
  A.DEPTNO = B.DEPTNO;