[Oracle] Insert, Update 판단을 한번에... Merge into 구문 살펴보기

서버 개발을 하면 주기적으로 갱신되거나 신규 유입되는 데이터의 경우에 DB에 해당 Key를 가진 레코드를 확인해보고 있으면 Update, 없으면 Insert로 분기시키는 로직을 많이 작성하게 된다.

 

간단한 개념이지만 은근히 귀찮은 작업이기도 하다. 하지만 오라클 DB에서는 이를 한번에 판단해서 처리해주는 구문이 있다. 바로 Merge into 구문이다.

 

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], [merge_insert_clause], [error_logging_clause] 각각 세부 문법은 아래 참조

 

merge_update_clause

WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]

 

merge_insert_clause

WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
          [, { expr | DEFAULT } ]...
       )
[ where_clause ]

 

error_logging_clause

LOG ERRORS 
  [ INTO [schema.] table ]
  [ (simple_expression) ]
  [ REJECT LIMIT { integer | UNLIMITED } ]

 

INTO 절

이 INTO절을 사용하여 업데이트하거나 삽입하는 대상 테이블이나 뷰를 지정합니다. 

 

USING 절

이 USING절을 사용하여 업데이트하거나 삽입할 소스를 지정합니다.

 

ON 절

이 ON절을 사용하여 MERGE작업이 업데이트되거나 삽입 되는 조건을 지정 합니다. 검색 조건이 true인 대상 테이블의 각 행에 대해 Oracle Database는 소스의 해당 데이터로 행을 업데이트합니다. 조건이 행에 대해 참이 아닌 경우 데이터베이스는 해당 소스 행을 기반으로 대상 테이블에 삽입합니다.

 

merge_update_clause

ON절의 조건이 true 인 경우 이 업데이트를 수행 합니다. update 절이 실행되면 대상 테이블에 정의된 모든 업데이트 트리거가 활성화됩니다.

where_clause 추가하면 데이터베이스가 지정된 조건이 true 인 경우에만 업데이트 작업을 실행하려는 경우. 조건은 데이터 소스 또는 대상 테이블을 참조할 수 있습니다. 조건이 true가 아니면 행을 테이블에 병합할 때 데이터베이스가 업데이트 작업을 건너뜁니다.

DELETE where_clause테이블을 채우거나 업데이트하는 동안 테이블의 데이터를 정리하도록 지정합니다 . 이 절의 영향을 받는 유일한 행은 병합 작업에 의해 업데이트된 대상 테이블의 행입니다. DELETE WHERE조건은 갱신 된 값에 의해 평가되지 않은 원래의 값으로 평가 UPDATE SET... WHERE조건. 대상 테이블의 행이 DELETE조건을 충족 하지만 ON절에서 정의한 조인에 포함 되지 않은 경우 삭제되지 않습니다. 대상 테이블에 정의된 모든 삭제 트리거는 각 행 삭제에 대해 활성화됩니다.

이 절을 단독으로 또는 와 함께 지정할 수 있습니다 merge_insert_clause. 둘 다 지정하면 두 순서 중 하나가 될 수 있습니다.

 

merge_update_clause 에 대한 제한 사항

• ON condition절 에서 참조하는 열은 업데이트할 수 없습니다 ..

 

merge_insert_clause

ON 절의 조건이 false일 경우 insert 절이 실행되며 대상 테이블에 정의된 모든 삽입 트리거가 활성화됩니다. INSERT키워드 뒤에 열 목록을 생략하면 대상 테이블의 열 수가 VALUES절의 값 수와 일치해야 합니다 .

merge_insert_clause단독으로 또는 와 함께 지정할 수 있습니다 merge_update_clause. 둘 다 지정하면 두 순서 중 하나가 될 수 있습니다.

 

Merging into a Table: Example

 

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM hr.employees e, oe.orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id); 

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM hr.employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        164         72
        165         68
        166         64
        167         62
        171         74
        172         73
        173         61
        179         62

 

 

Conditional Insert and Update: Example

 

CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');

COMMIT;

 

입력된 row에 대해서 person_id가 Match되는 케이스가 있을경우 해당 row를 업데이트 한다.

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title;

 

입력된 row에 대해서 person_id기 match되는경우 update, match되는 경우가 없는 경우 insert를 수행한다.

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
WHEN NOT MATCHED THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

 

Update 세부 절에서 DELETE 구문도 함께 사용한 경우. person_id가 존재할 경우 update를 수행하며 업데이트가 되는 대상 행에 대해서 title 이 'Mrs'인 경우 DELETE 한다. 

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT MATCHED THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr';

 

참고링크 : https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

 

-- The End --