Oracle의 MERGE INTO 기초

Database를 Oracle를 사용할때 유용하게 사용하는 Merge Into에 대해서 간단하게 정리.

개인적으로 실서비스에서는 크게 사용한적은 없는 것 같고 Admin이나 배치 개발시에 MyBatis와 함께 유용하게 사용하고 있다.

요구사항 : 상품이 있으면 수정하고 없으면 삽입해라.

기존 Update와 Insert 만 사용예시

mybatis를 사용한다고 했을때 가장 간단하게 처리하는 방법은 아래와 같다.

1. 우선 Update query를 실행한 후 update 된 행의 개수를 resultType으로 return

2. Insert into query실행.(위 update의 update rows가 0인 경우에만 실행.)

Update Query 예시.

UPDATE PRODUCT pd
    SET USE_YN = #{useYn}
        ,PRODUCT_NAME = #{productName}
        ,MODIFY_DATE = SYSDATE
    WHERE pd.PRODUCT_ID = #{productId}

Insert Query 예시.

INSERT INTO PRODUCT (
      PRODUCT_ID
      , PRODUCT_NAME
      , USE_YN
      , MODIFY_DATE
    ) values (
      #{productId}
      , #{productName}
      , #{useYn}
      , SYSDATE
    )

Merge into 사용하기(Update 와 Insert 합치기.)

위 요구사항을 Merge Into를 이용하여 구현하면 아래와 같다.

기본적인 사용법은 너무 간단해서 따로 추가 내용은 필요가 없을 정도이다.

레퍼런스 사이트에 가면 좀 더 디테일 하게 내용을 확인 할 수 있다.

Merge into Query with comment

많이 사용하는 구성은 의외로 간단하다. 다만 생각해야 할 것은 Update 또는 Insert를 한번에 처리하므로 Parameter값을 잘 생각해서 주어야 한다.

MERGE INTO Product pd /* 대상이 되는 테이블 또는 뷰*/
USING DUAL /* 위의 하나의 테이블만 이용할려면 DUAL을 적으면 됨.*/
    ON ( pd.PRODUCT_ID = #{productId} ) /* ON 에 해당되는 데이타가 UPDATE시 Rows가 됨. */
WHEN MATCHED THEN /* ON 조건에 해당되는 것이 있으면 아래 실행(UPDATE) */
    UPDATE SET       /* Update시 테이블명이나 Where조건이 모두 위에 있다고 생각하면 됨 */
        USE_YN = #{useYn}
        ,PRODUCT_NAME = #{productName}
        ,MODIFY_DATE = SYSDATE
WHEN NOT MATCHED THEN /* ON 조건에 매칭되지 않으면 아래 실행(Insert)*/
    INSERT ( /* INSERT INTO PRODUCT -> INSERT로 퉁쳤다는걸 기억하다. */
      PRODUCT_ID
      , PRODUCT_NAME
      , USE_YN
      , MODIFY_DATE
    ) values (
      #{productId}
      , #{productName}
      , #{useYn}
      , SYSDATE
    )

Merge into Query without comment

MERGE INTO Product pd
USING DUAL
    ON ( pd.PRODUCT_ID = #{productId} )
WHEN MATCHED THEN
    UPDATE SET
        USE_YN = #{useYn}
        ,PRODUCT_NAME = #{productName}
        ,MODIFY_DATE = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (
      PRODUCT_ID
      , PRODUCT_NAME
      , USE_YN
      , MODIFY_DATE
    ) values (
      #{productId}
      , #{productName}
      , #{useYn}
      , SYSDATE
    )

Syntax diagram

아래는 Oracle 내 레퍼런스 사이트내 Merge Into에 대한 syntax diagram이다.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

merge_update_caluse 구문

merge_insert_clause 구문

Mybatis시 아래와 같이 에러가 났다면 십중팔구 해당 쿼리에 세미콜론이 있는지 확인하세요.

### Cause: java.sql.SQLSyntaxErrorException: ORA-00911: 문자가 부적합합니다
​
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00911: 문자가 부적합합니다
​
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
    at com.sun.proxy.$Proxy44.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
​