Oracle 9i부터 추가된 Merge

*Merge는 Oracle 9i부터 추가됐다.
9i와 10g에서 Merge의 사용법이 약간 상이한 것 같다.

 

CREATE TABLE em01 AS SELECT *
FROM emp

;

 

CREATE TABLE em02
    AS SELECT *
  FROM emp

;

 

UPDATE em02
    SET job='TEST'
FROM emp
WHERE job='MANAGER'

;

1. 9i에서 Merge
MERGE INTO em01 n1 USING (select ename, job, mgr, hiredate, sal, comm, deptno, empno from em02) e1

 ON ( n1.empno = e1.empno )
WHEN MATCHED THEN UPDATE
     SET n1.ename = e1.ename,
            n1.job = e1.job, 
            n1.mgr = e1.mgr,
            n1.hiredate = e1.hiredate,
            n1.sal = e1.sal,
            n1.comm = e1.comm,
            n1.deptno = e1.deptno
WHEN NOT MATCHED THEN
    INSERT ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
        values ( e1.empno,
                    e1.ename,
                    e1.job, 
                    e1.mgr,
                    e1.hiredate,
                    e1.sal,
                    e1.comm,
                     e1.deptno )

;

2. 10g에서 Merge
MERGE INTO em01 n1 USING em02 e1
    ON ( n1.empno = e1.empno )
WHEN MATCHED THEN UPDATE
    SET n1.empno = e1.empno,
        n1.ename = e1.ename,
        n1.job = e1.job, 
        n1.mgr = e1.mgr,
        n1.hiredate = e1.hiredate,
        n1.sal = e1.sal,
        n1.comm = e1.comm,
        n1.deptno = e1.deptno
WHEN NOT MATCHED THEN
    INSERT ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
        values ( e1.empno,
                e1.ename,
                e1.job, 
                e1.mgr,
                e1.hiredate,
                e1.sal,
                e1.comm,
                e1.deptno )

;

Merge when no data found

https://community.oracle.com/tech/developers/discussion/2595696/merge-when-no-data-found


insert
  into emp
       (
           empno
         , ename
         , job
         , mgr
         , hiredate
         , sal
         , comm
         , deptno
       )
select 1, 's', 's', 23, sysdate, 12, '', 34
  from dual
where not exists
       (
          select null
            from emp
           where empno = 1
        )