(Oracle) MERGE INTO 구문 사용 방법 및 예시

오라클 MERGE INTO 구문 사용 방법 및 예시 정리

merge into 구문 사용 방법 및 예시

시스템 상의 여러 로직에서는 데이터 처리 시 다음과 같이 데이터를 처리해야 하는 경우가 다수 있습니다.

 

1. 해당 데이터가 있는지 확인 'SELECT'

2-1. 데이터가 있는 경우 'UPDATE'

2-2. 데이터가 없는 경우 'INSERT'

 

이럴 때 오라클의 'MERGE INTO' 구문을 사용하면 조건에 따라 테이블에 데이터를 삽입하거나 업데이트하는 것을 한 번에 처리할 수 있는데요.

이어지는 내용을 통해 'MERGE INTO 구문의 사용 방법과 예시'에 대해서 살펴보겠습니다.

 

---

MERGE INTO 구문 사용 방법

MERGE INTO [TABLE / VIEW]      -- UPDATE 또는 INSERT 할 대상 테이블 또는 뷰
USING [TABLE / VIEW / DUAL]    -- 비교 대상 테이블 또는 뷰 (위 UPDATE 또는 INSERT 할 대상과 동일할 경우 DUAL 사용)
ON (CONDITION)                 -- 비교 조건
WHEN MATCHED THEN              -- 조건에 일치하는 경우 (UPDATE 구문 뿐만 아니라 DELETE 구문도 사용 가능)
	UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN          -- 조건에 일치하지 않는 경우
	INSERT (column1, column2, ...) VALUES (value1, value2, ...);

 

MERGE INTO 구문은 기본적으로 다음과 같은 문법으로 사용되는데요.

해당 구문은 오라클 9i 버전부터 사용할 수 있으며, WHEN MATCHED THEN에서 'DELETE' 구문을 사용하기 위해서는 오라클 10g 버전 이상이 되어야 사용할 수 있습니다.

 

추가적으로 조건에 따른 분기 처리를 하는 'WHEN MATCHED THEN', 'WHEN NOT MATCHED THEN' 부분은 둘 중 하나만 단독으로도 존재할 수 있습니다.

 

 

---

MERGE INTO 구문 사용 예시

예시에 사용할 간단한 테이블

MERGE INTO accumulated_orders ao
USING sameday_orders so
ON (ao.product_no = so.product_no)                 
WHEN MATCHED THEN             
	UPDATE SET ao.quantity = ao.quantity + so.quantity
WHEN NOT MATCHED THEN          
	INSERT (ao.product_no, ao.product_nm, ao.quantity, ao.last_date) 
	VALUES (so.product_no, so.product_nm, so.quantity, so.sale_date);

 

예를 들어 다음과 같이 '누적 판매(accumulated_orders)' 테이블과 '당일 판매(sameday_orders)' 테이블이 있을 때, '상품 번호(product_no)'를 비교하여 누적 판매 테이블에 해당 상품 번호로 누적 판매된 데이터가 있을 경우 데이터를 update 하고, 없을 경우 새로운 데이터를 insert 하는 merge into 구문을 위와 같이 구현할 수 있습니다.

 

 

WHEN MATCHED THEN
    UPDATE SET ao.quantity = ao.quantity + so.quantity
    WHERE ao.quantity > 10

 

또한 오라클 10g 버전부터는 다음과 같이 update, delete 문에서 where 조건 절을 사용할 수 있습니다.

(하지만 INSERT 문에서는 WHERE 조건을 사용하면 오류가 발생합니다.)

 

 

 

merge into 구문 사용 시 주의해야 할 점은 on 조건 절을 통해 비교되는 데이터가 하나가 아닌 경우(multi row) 아래와 같이 sql 오류(ORA_30926)가 발생할 수 있다는 것입니다.

 

SQL 오류: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 -  "unable to get a stable set of rows in the source tables"
*Cause:    A stable set of rows could not be got because of large dml
           activity or a non-deterministic where clause.
*Action:   Remove any non-deterministic where clauses and reissue the dml.

 

(sameday_orders 테이블의 product_no 컬럼에는 pk가 걸려있지 않기 때문에 동일한 product_no를 가진 데이터를 여러 개 넣어 merge into 구문을 실행시켰습니다.) 

 

 

MERGE INTO accumulated_orders ao
USING sameday_orders so
ON (ao.quantity = 15)
WHEN MATCHED THEN 
    UPDATE SET ao.quantity = ao.quantity + so.quantity
WHEN NOT MATCHED THEN
    INSERT (ao.product_no, ao.product_nm, ao.quantity, ao.last_date)
    VALUES (so.product_no, so.product_nm, so.quantity, so.sale_date);

 

또한 다음 예시처럼 on 조건 절에서 참조되는 열(ao.quantity)을 update 하려고 했을 때도 아래와 같은 sql 오류(ORA-38104)가 발생하게 됩니다.

 

SQL 오류: ORA-38104: Columns referenced in the ON Clause cannot be updated: "AO"."QUANTITY"
38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"
*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause

 

 

---

단일 대상에 대한 MERGE INTO (DUAL)

MERGE INTO accumulated_orders ao
USING DUAL
ON (ao.product_no = 1)
WHEN MATCHED THEN 
    UPDATE SET ao.quantity = 0
WHEN NOT MATCHED THEN
    INSERT (ao.product_no, ao.product_nm, ao.quantity, ao.last_date)
    VALUES (1, 'product1', 0, '2024-04-18');

 

 

만약 비교하려는 대상 테이블 없이 단일 테이블(또는 뷰)로만 merge into 구문을 사용해야 할 때는 다음과 같이 using 절에 dual 테이블을 사용할 수 있습니다.