[ORACLE] MERGE INTO 조건의 데이터가 없는 경우 NULL Row 처리하기

지난 달부터 차세대 MSA 개발 프로젝트에 개발자로 투입되서 그동안 너무 바빴다.

FRONT-END로 넥사크로(NEXACRO)를 사용하고 있는데, GRID DataSet을 이용한 Multi Row 핸들링을 자주하게 되어 다중 처리에 대한 부분을 알아보았다.

그러던 중 merge into 를 사용한 update, insert를 구현하고 있었는데 조건절에서 데이터가 없다면 아무것도 실행하지 않는 현상이 생겼다. 조건, 업데이트문, 삽입문을 따로 돌려보면 전혀 에러가 없는 코드이고, 함께 돌려도 정상적인 코드로 멘탈이 슬슬 나가려할 때... 구글링을 하다가 내 상황과 같은 글을 찾게 되었다.(링크는 참고)

그 글에서는 오라클 9.2.0.4.0 버전에서는 되는데 9.2.0.3.0 버전에서 정상적으로 동작하지 않는 현상에 대해 알려주었다.(참고로 우리는 19.0.0.0.0 버전이다...)

 

그래서 어떻게 해야할까 찾는 도중 union all 로 묶고 NOT EXISTS 로 null row를 만드는 방법을 찾았다.

MERGE INTO 절

먼저 MERGE INTO 의 기본 형태를 알아보자.

MERGE /* sample.mergeSample */
INTO 테이블명
  USING dual
    ON (ID = #{id})
    WHEN MATCHED THEN
    	UPDATE SET
        	COL1 = #{val1}
            ...
    WHEN NOT MATCHED THEN
    	INSERT
        (
        	COL1,COL2,COL3,...
        )
        VALUES
        (
        	#{val1},#{val2},#{val3},...
        )

MERGE INTO 테이블명 : 사용할 테이블을 정의

USING DUAL : 비교할 테이블(현재는 1개 테이블 이용하기에 DUAL 사용)

ON ( ) : 비교 조건(해당 값을 기준으로 UPDATE를 하기 때문에 사용된 컬럼은 update하지 못함)

WHEN MATCHED THEN : ON 조건이 맞다면 실행한다.

WHEN NOT MATCHED THEN : ON 조건이 틀리다면 실행한다.

 

이렇게 코딩을 하면 1개의 쿼리에 insert, update로 알아서 처리되기 때문에

이미 있는 id 값이 넘어왔다면 자연스레 다른 컬럼을 update 하게 된다.

 

간단히 사용방법을 익혔으니 실제 적용한 코드를 살펴보자.

완성된 MERGE INTO

MERGE /* sample.mergeSample */
INTO 테이블 A
    USING (
    	SELECT ID
        FROM 테이블
        WHERE 1=1
        AND ROWNUM=1
        AND ... 조건추가
        
        UNION ALL
        
        SELECT NULL AS ID
        FROM DUAL
        WHERE NOT EXISTS (
            SELECT ID
            FROM 테이블
            WHERE 1=1
            AND ROWNUM=1
            AND ... 조건추가
        )
        AND ROWNUM=1
    ) B
    ON (A.ID = B.ID)
    WHEN MATCHED THEN
    	UPDATE SET
        	COL1 = #{val1}
            ...
    WHEN NOT MATCHED THEN
    	INSERT
        (
        	COL1,COL2,COL3,...
        )
        VALUES
        (
        	#{val1},#{val2},#{val3},...
        )

USING 부분의 코드가 상당부분 들어간걸 볼 수 있다.

이로써 조건에 맞는 row가 있다면 update를 실행하고,

조건에 맞는 데이터가 없다면 NULL row가 생겨 insert를 실행하게 된다.

참고

[ORACLE] MERGE INTO