오라클 MERGE 구문 사용 시 유의 점(ORA-38104)

오라클에서 MERGE 구문이 익숙하지 않다면 사용 시 유의해야 될 점이 있는데요. 그 유의점 중 하나는 참조되는 열을 업데이트할 경우 발생하는 ORA-38104 에러를 방지하는 것입니다.

오라클 MERGE 구문 사용 방법 요약

오라클 MERGE 구문이 뭔지 모르고 사용하려는 분들은 아마 없겠죠. 어떤 테이블 내에서 조건에 해당하는 데이터 존재 유무에 따라서 UPDATE, DELETE, INSERT 등의 DML 구문을 사용하는 쿼리인데요.

사용 방법은 아래와 같이 간단하게 요약할 수 있습니다.

반응형

MERGE INTO 조작할테이블
USING (참조할테이블)
ON (조건절)
WHEN MATCHED THEN --생략가능
(일치할 경우 UPDATE, DELETE 쿼리)
WHEN NOT MATCHED THEN --생략가능
(불일치할 경우 INSERT 쿼리)

참조할 테이블과 오라클 DML 구문을 이용해 바꿀 테이블을 조건절로 이어주고 이 조건이 일치할 경우 UPDATE, DELETE 쿼리를, 불일치할 경우 INSERT 쿼리를 사용해 오라클 테이블의 내용을 변경해주는 DML 구문인데요.

실제 사용 예시를 다시 보여드릴게요.

CREATE TABLE TAB1 AS 
SELECT LEVEL LEV,
            DECODE(MOD(LEVEL,4),0,'N','Y') ODD_YN ---홀수 여부
FROM DUAL
CONNECT BY LEVEL <= 20;                      

MERGE INTO TAB1 A
USING (SELECT LEVEL LEV ,
                        DECODE(MOD(LEVEL,2),0,'N','Y') ODD_YN
              FROM DUAL
           CONNECT BY LEVEL <= 30
          ) AA
ON (A.LEV = AA.LEV AND A.ODD_YN = 'N')
WHEN MATCHED THEN
UPDATE SET ODD_YN = DECODE(MOD(A.LEV,2),0,'N','Y')
WHEN NOT MATCHED THEN
INSERT (A.LEV,A.ODD_YN) VALUES (AA.LEV,AA.ODD_YN);

일단 오라클 CREATE 구문을 이용해서 홀수 여부가 저장된 TAB1이라는 테이블을 만들었는데요.

4로 나눈 값을 짝수로 확인했기에 잘못 들어간 데이터가 있겠죠.

여기서 MERGE 구문을 사용해서 30까지 범위를 확대하고 잘못 들어간 데이터를 업데이트 쳐주도록 수정해주는 쿼리입니다.

다만 이 오라클 SQL문을 이용하면 에러가 떨어질텐데요.

오라클 ORA-38104 에러

이 에러가 바로 ORA-38104 에러로 이 에러가 나지 않기 위한 유의점을 지금부터 알려드릴게요.

 

오라클 MERGE 구문에서 ORA-38104 에러가 발생하는 이유와 에러 내용

반응형

먼저 오라클 MERGE 구문에서 발생하는 ORA-38104 에러의 내용은 ON 절에서 참조되는 열을 업데이트할 수 없다는 내용입니다.

다시 말해 ON 절에 있는 칼럼은 UPDATE에서 사용할 수 없다는 말이죠.

따라서 우리는 위에 있는 쿼리에서 ON절에 홀수 여부 칼럼을 제외해주어야 합니다.

수정한 오라클 MERGE문은 아래와 같습니다.

MERGE INTO TAB1 A
USING (SELECT LEVEL LEV ,
                        DECODE(MOD(LEVEL,2),0,'N','Y') ODD_YN
              FROM DUAL
           CONNECT BY LEVEL <= 30
          ) AA
ON (A.LEV = AA.LEV)
WHEN MATCHED THEN
UPDATE SET ODD_YN = DECODE(MOD(A.LEV,2),0,'N','Y')
WHEN NOT MATCHED THEN
INSERT (A.LEV,A.ODD_YN) VALUES (AA.LEV,AA.ODD_YN);

이렇게 수정해주면 ORA-38104 에러는 발생하지 않게 됩니다.

오라클 MERGE 구문에서 부득이하게 참조 필드를 UPDATE 해야 하는 경우

그럼 오라클 MERGE 구문에서 부득이하게 해당 필드를 참조해주어야 하는 경우는 오라클 MERGE 구문을 어떻게 구성해주면 될까요?

먼저 MERGE 구문 내의 UPDATE에 WHERE 절을 사용하는 방법이 있습니다.

예를 들면 아래와 같이 사용하는 거죠.

MERGE INTO TAB1 A
USING (SELECT LEVEL LEV ,
                        DECODE(MOD(LEVEL,2),0,'N','Y') ODD_YN
              FROM DUAL
           CONNECT BY LEVEL <= 30
          ) AA
ON (A.LEV = AA.LEV)
WHEN MATCHED THEN
UPDATE SET ODD_YN = 'N' WHERE A.ODD_YN = 'Y' AND MOD(A.LEV,2) = 0
WHEN NOT MATCHED THEN
INSERT (A.LEV,A.ODD_YN) VALUES (AA.LEV,AA.ODD_YN);

두 번째는 오라클의 오리지널 UPDATE DML 구문을 사용하는 방법이 있습니다.

반응형

UPDATE 구문을 사용하면 참조하는 필드를 수정하는 게 가능하기 때문에 ORA-38104 에러가 발생하지 않습니다.

마지막으로 테이블에 칼럼을 하나 더 추가해서 그 필드를 참조하도록 하는 방법이 있습니다.

예를 들면 ODD_YN2라는 칼럼을 추가해서 같은 데이터를 넣어주고 참조할 때는 ODD_YN2를, 수정 구문에서는 ODD_YN을 사용하는 방법이죠.

 

개인적으로는 첫 번째 방식을 가장 선호하는데 다양한 상황이 있고 상황에 따라 제대로 된 결과를 얻어내기 좋은 방식이 또 있기 때문에 어떤 방법이 가장 좋다고 할 수는 없으니 그 상황에 가장 적합한 방식을 사용하시면 될 것 같습니다.

 

마지막으로 오라클의 INSERT, UPDATE에 비해 MERGE 구문이 복잡해 보일 수 있고 실제로 적응이 힘들긴 하지만 적응하고 잘만 사용하면 상당히 유용하다는 점, 그리고 제대로 사용하기 위해서는 자주 SQL문 연습을 통해 숙달이 필요하다는 점을 팁으로 전해드리고 마치겠습니다.