[Oracle] Insert와 Update를 동시에 처리할 때

AhnMin·2024년 7월 23일

ERP 개발을 진행하다 보면 INSERT와 UPDATE를 동시에 처리해야 되는 경우가 빈번하다.

보통 비즈니스 로직에서는 REST API를 설계할 때 이를 각 요청에 맞게 처리를 하지만 코드가 길어지는건 유지보수 측면에서 그 만큼 꾸준히 관리돼야 하는 부분도 많아진다는 단점이 있다.

1. 데이터가 존재하는지 확인
2. 데이터가 존재하면 UPDATE
3. 데이터가 없으면 INSERT

위와 같이 3가지 경우를 모두 선언하는건 너무 번거러운 부분이 있다.

Oracle MERGE INTO문

이 경우 Oracle에 도움을 받으면 코드를 획기적으로 줄일 수 있는데 바로 MERGE INTO를 사용하는 것이다.

MERGE INTO [TABLE / VIEW] 		// update 또는 insert할 테이블 혹은 뷰
     USING [TABLE / VIEW / DUAL] 	// 비교할 대상 테이블 혹은 뷰 (위 테이블과 동일할 경우 DUAL을 사용)
        ON [조건] 			// UPDATE 와 INSERT 처리할 조건문 (조건이 일치하면 UPDATE / 불일치 시 INSERT)
      WHEN MATCHED THEN 
                 UPDATE 
                    SET
                       [COLUMN1] = [VALUE1],
                       [COLUMN2] = [VALUE2],
                             ...
                             
            (DELETE [TABLE] WHERE [COLUMN 1] = [VALUE 1] AND ...) // UPDATE 뿐만 아니라 DELETE 구문도 사용 가능
      
      WHEN NOT MATCHED THEN
                     INSERT (COLUMN1, COLUMN2, ...)
         	     VALUES (VALUE1, VALUE2, ...)

MERGE INTO 예 기본적인 구조는 위와 같고 주의할 점은 동시에 여러 트랜잭션이 실행될 경우 데드락이 발생할 수 있지만 어지간한 경우가 아니면 거의 없긴하다.

MERGE INTO 활용

MERGE INTO T_I_FAMILY tgt
-- 대상 테이블 T_I_FAMILY을 업데이트하거나 삽입할 대상 테이블로 설정
USING (
    SELECT EMP_CD, SEQ, JUMIN_NO, NM, RELATION_CD, ABILITY_CD
    FROM T_I_FAMILY_MIS
    WHERE CNF_YN = 'N'
      AND EMP_CD = #{empCd}
) src
-- 소스 테이블 T_I_FAMILY_MIS에서 조건에 맞는 데이터를 선택
-- CNF_YN이 'N'이고 EMP_CD가 특정 값인 데이터를 선택
ON (tgt.EMP_CD = src.EMP_CD AND tgt.SEQ = src.SEQ)
-- 대상 테이블과 소스 테이블을 EMP_CD와 SEQ 컬럼을 기준으로 매칭
WHEN MATCHED THEN
    -- 매칭되는 경우 업데이트 수행
    UPDATE SET
        tgt.JUMIN_NO = src.JUMIN_NO,
        tgt.NM = src.NM,
        tgt.RELATION_CD = src.RELATION_CD,
        tgt.ABILITY_CD = src.ABILITY_CD
    -- 소스 테이블의 JUMIN_NO, NM, RELATION_CD, ABILITY_CD 값을 대상으로 업데이트
WHEN NOT MATCHED THEN
    -- 매칭되지 않는 경우 삽입 수행
    INSERT (EMP_CD, SEQ, JUMIN_NO, NM, RELATION_CD, ABILITY_CD)
    VALUES (src.EMP_CD, src.SEQ, src.JUMIN_NO, src.NM, src.RELATION_CD, src.ABILITY_CD)
    -- 소스 테이블의 EMP_CD, SEQ, JUMIN_NO, NM, RELATION_CD, ABILITY_CD 값을 대상으로 삽입