Oracle MERGE 기본 사용법 및 에러처리
Updated:
예제 테이블 생성
샘플 테이블 생성
오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 와 DEPT라는 테이블을 기준으로 테스트해봅니다. DEPT 테이블의 부서별 위치를 가진 테이블이고 EMP 테이블의 부모테이블로 DEPTNO가 PK입니다. EMP는 고용인 테이블로서 DEPT의 자식테이블로 EMPNO가 PK이며, DEPTNO가 FK입니다. 아래의 예제를 가지고 알아봅니다.
- EMP테이블
| Empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 00:00:00 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 00:00:00 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987/04/19 00:00:00 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981/11/17 00:00:00 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987/05/23 00:00:00 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 00:00:00 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 00:00:00 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 00:00:00 | 1300 | 10 |
- DEPT테이블
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING} | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
1. 오라클 MERGE 기본 개념
MERGE문장은 우리가 흔히 사용하는 매우 강력한 UPDATE + INSERT + DELETE문장의 집합체입니다. 오라클 9i DBMS에서는 UPSERT라는 불리는 UPDATE + INSERT문장만 사용할수 있었지만 10G DBMS부터는 3가지 DML문장을 모두 사용할수 있습니다.
MERGE문장은 금융/통신/제조/공공 산업분야에서 매우 광범위하게 사용되는 SQL문장으로 오라클의 자랑거리중 하나라고 생각됩니다. 클라우드에서 많이 사용하는 MYSQL 및 마리아 DBMS는 아직 이 MERGE기능이 없다. 아직도 오라클 MERGE를 사용하지 않고 PL/SQL로 루핑를 돌리거나, WAS로 데이타를 가져가셔 루핑 처리하면서 UPDATE나 INSERT를 사용하는 개발자들이 있는데 이 기회에 MERGE문장을 익혀 잘 사용해보길 바랍니다.
현장에서 MERGE문장과 루핑형SQL 형태의 성능비교를 해보면 그야말로 엄청난 차이가 있습니다. 다만 10g DBMS인 10.2.0.1 ~ 10.2.0.3 버젼에는 MERGE문장이 BUG나 CPU소모율이 매우 놓은 현상을 보이니 이 버젼을 사용하는 분들은 주의하기 바랍니다.
2. 오라클 MERGE 기본사용법
문법을 설명하면 아래와 같습니다. EMP테이블을 마스터로 해서 EMP_HISTORY 테이블의 값들을 변경하는것입니다.
EMP와 EMP_HISTORY 테이블을 ON절에 있는 EMPNO 조건으로 비교하여 만약 EMP_HISTORY에 동일한 값이 있습니다면 EMP테이블의 값을 HISTORY에 UPDATE하고 값이 없다면 EMP 테이블의 레코드를 HISTORY에 INSERT하라는 내용입니다.
가. 기본 문법
* table에 row를 조건적으로 insert또는 update( update와 insert를 결합한 문장으로 볼 수 있습니다.)
* MERGE를 실행하는 user는 해당 table에 insert와 update를 할 수 있는 권한이 있어야 합니다.
* 사용 예 :
MERGE INTO emp_history eh
USING emp
ON (e.empno = eh.empno)
WHEN MATCHED THEN
UPDATE SET eh.salary = e.sal
WHEN NOT MATCHED THEN
INSERT VALUES (e.empno, sysdate, sal);
INTO : data가 update되거나 insert될 table name (emp_history는 target table)
USING : 대상 table의 data와 비교한 후 update또는 insert할 때 사용할 data의 source (emp는 source table)
ON : update나 insert할 condition으로, 해당 condition을 만족하는 row가 있으면 WHEN MATCHED
이하를 실행하게 되고, 없으면 WHEN NOT METCHED 이하를 실행합니다.
WHEN MATCHED : ON의 조건이 TRUE인 row에 수행할 내용
WHEN NOT MATCHED : ON의 조건에 맞는 row가 없을 때 수행할 내용
* update, insert 후에 table명이 없다. -> 위에서 정의한 target table에 update, insert하는것이기 때문입니다.
나. 사용예제
순서대로 따라해봅니다. 많이 사용하는 sql형태이므로 쉽게 이해할수 있을것으로 생각합니다.
--타겟테이블인 emp_test생성. emp테이블의 10번 부서 3명 생성함.
SQL> CREATE TABLE emp_test nologging AS SELECT * FROM emp WHERE deptno = 10;
SQL> SELECT empno, ename, sal FROM emp_test ;
EMPNO ENAME SAL
----- ---------- -----
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
-- emp_test테이블에 데이터가 있으면 급여를 10% 인상하고, 없으면 emp테이블의 레코드를 타겟테이블로 INSERT하는 sql 작성하라
SQL> MERGE INTO emp_test eu
USING emp e
ON (eu.empno = e.empno)
WHEN MATCHED THEN UPDATE SET eu.sal = e.sal*2 ;
WHEN NOT MATCHED THEN
INSERT VALUES (e.empno,e.ename,e.job,e.mgr, e.hiredate,e.sal,e.comm,e.deptno);
14 행이 병합되었습니다.
-- 실행결과 조회.
SQL> SELECT empno, ename, sal FROM emp_test ;
EMPNO ENAME SAL
------------------
7782 CLARK 2695
7839 KING 5500
7934 MILLER 1430
... 14 개의 행이 선택되었습니다.
--emp_test의 3건의 sal이 10%가 올라가고 레코드가 11건이 새로 생성되었음을 알수있습니다.
3. 오라클 MERGE 에러처리
1) ORA-30926문제
이 에러로 인해 금융분야 큰 시스템하나를 오픈 못할뻔 하였다. 이 에러는 ON절에 사용하는 연결조건은
오라클 조인 업데이트에서도 언급한것과 같이 두 테이블의 부모-자식테이블 관계와도 연관되어 있습니다.
그리고 ON절의 연결조건은 기본적으로 UNIQUE하여야 합니다. 즉 JOB = JOB 칼럼으로 되어 있는데 이는 M-M 연결관계입니다. 그래서 30926에러가 나왔는데 오라클 에러문장이 ‘원본 테이블의 고정 행 집합을 가져올 수 없습니다’ 인데 이 문장을 이해하기 힘들어 문제가 발생한것입니다.
MERGE INTO emp_test eu
USING emp e
ON (eu.job = e.job)
WHEN MATCHED THEN UPDATE SET eu.sal = e.sal*1.1
WHEN NOT MATCHED THEN
INSERT VALUES
(e.empno,e.ename,e.job,e.mgr, e.hiredate,e.sal,e.comm,e.deptno);
ORA-30926 : 원본 테이블의 고정 행 집합을 가져올 수 없습니다
(ORA-30926 : 'unable to get a stable set of rows in the source tables')
merge 작업 중에 on 절의 조건에 multi row가 발생하는 경우에 나타나는 오류입니다. on 절의 조건은 단일 row만 발생 되어야 합니다. 일단, using 절의 테이블에 on 절의 컬럼들을 group by 쿼리로 유니크한지 확인합니다.
Select job, count(*) from emp_test group by job;
JOB COUNT
-----------------------------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
order by 절을 추가한 이유는 유니크하지 않은 row부터 확인하기 위함입니다. 해당 쿼리로 확인해보면 count(*) 컬럼의 값이 1이 아닌 row가 조회됩니다. ON절에 들어간 칼럼을 적절히 조절하여 유니크하게 만들어주면 에러가 해결됩니다.
1) ORA-38104문제
38104문제는 UPADTE시 나타나는 에러로서 ON절에 지정된 칼럼이 또 UPDATE 절의 칼럼으로 지정되어 논리적 모순상태로 발생하는 에러라 할수 있습니다
SQL> Merge into scott.emp e using scott.emp_update u
On ( e.empno = u.empno )
when matched then
update set e.empno = u.empno, e.ename = u.ename, e.job = u.job, e.mgr = u.mgr,
e.hiredate = u.hiredate, e.sal = u.sal, e.comm = u.comm, e.deptno = u.deptno
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( u.empno, u.ename, u.job, u.mgr, u.hiredate, u.sal, u.comm, u.deptno );
ERROR at line 3: ORA-38104: Columns referenced in the ON Clause cannot be updated:
에러이유 : ON구에 들어간 칼럼은 UPDATE 문장에 넣으면 안됩니다. 즉 EMPNO가 ON 구로 지정되어 있는데 UPDATE SET절에 들어가있습니다. 그래서 이 에러가 난것입니다. UPADTE문장의 칼럼값이 변경되는건 논리적으로 맞지 않는 개념입니다 보니 오라클에서 막아놓은것 같습니다. 다만 꼭 이런 ON구에 지정된 갈럼을 UPADTE에 넣어야 하는경우 아래와 같이 변칙스타일로 만들수 있으나 꼭 필요한 경우에만 사용하고 데이타값이 Wrong Results가 발생하지 않는지 확인하고 사용하기 바랍니다.
SQL> Merge into emp_test e using (SELECT u.*, e.rowid as rid
from emp u, emp_test e
where u.empno = e.empno(+)) u
ON ( e.rowid = u.rid )
When matched then
update set e.empno = u.empno, e.ename = u.ename, e.job = u.job, e.mgr = u.mgr,
e.hiredate = u.hiredate, e.sal = u.sal, e.comm = u.comm, e.deptno = u.deptno
When not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( u.empno, u.ename, u.job, u.mgr, u.hiredate, u.sal, u.comm, u.deptno );
4. 결언
오라클 MERGE는 매우 중요한 기능입니다. PL/SQL이나 WAS에서 루핑형SQL형태로 처리하는것을 획기적으로 원쿼리로 만들어 줄수 있는 중요한 기능입니다. 이 기능이 없는 DBMS로서 대표적인것이 마리아나 MYSQL로 각 산업군의 중요시스템에 사용하기 어려운 이유중하나가 바로 이 MERGE를 지원하지 않는다점도 있습니다. 현장에서 MERGE문장이 사용되는것을 보면 정말 개발자들의 상상력과 창의력에 경의를 표하고 싶을만큼 잘 사용하는곳도 많고, MERGE문장이 있는지도 모르고 절차형언어로 처리 프로그램을 작성해서 많은 성능문제가 일어나는 곳들도 보아왔습니다. MERGE에서 많이, 그리고 흔하게 발생하는 대표적인 ORA에러와 UPADTE방향 등을 오라클 조인 업데이트 1, 2, 그리고 이번 칼럼에서 이야기하였는데 꼭 테스트를 해보고 잘 활용하기 바랍니다.