Operating System/ORACLE

[ORACLE] MERGE

Growing DBA 2022. 4. 5. 09:39
728x90
반응형

* MERGE 문의 이해

- MERGE문은 조건에 따라서 데이터의 삽입, 갱신, 삭제 작업을 한 번에 할 수 있다.

- 해당 행이 존재하는 경우 UPDATE(DELETE포함할 수 있음)를 수행하고, 새로운 행일 경우 INSERT를 수행한다.

- 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.

- MERGE문에서 CLOB 사용시 업데이트할 내용이 2000 bytes가 넘을 때 ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.

* MERGE Syntax

MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery
         } [ t_alias ]
   ON ( condition )
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ DELETE where_clause ]
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr [, expr ]... | DEFAULT })

- INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정

- USING : 비교할 SOURCE 테이블 또는 뷰나 서브 쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다. 

- ON : UPDATE나 INSERT를 하게 될 조건으로 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고 없으면 WHEN NOT MATCHED 이하를 실행하게 된다. 

- WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행할 내용 (UPDATE, DELETE 포함될 수 있음)

- WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용( INSERT) 

* MERGE 예제1

- 아래 예제는 부서 번호 20,30의 사원이 존재하면 급여를 10% 인상하고, 존재하지 않으면 급여가 1000보다 큰 사원 정보를 등록하는 예이다. (INSERT, UPDATE 예제)

-- 부서번호 10, 20의 사원정보를 가지는 테스트 테이블을 생성하자
CREATE TABLE emp_merge_test
AS SELECT empno, deptno, sal FROM emp WHERE deptno IN (10, 20);
 
 
-- 데이터를 확인해 보자
SELECT * FROM emp_merge_test;
 
 
-- 사원이 존재하면 급여를 10% 인상하고, 없으면 INSERT 한다.
MERGE INTO emp_merge_test m
USING ( SELECT empno, deptno, sal    -- USING절에 뷰가 올수 있다.
        FROM emp
        WHERE deptno IN (20,30)) e
ON ( m.empno = e.empno)
WHEN MATCHED THEN
  UPDATE SET m.sal = ROUND(m.sal*1.1)
WHEN NOT MATCHED THEN
INSERT (m.empno, m.deptno, m.sal)
VALUES (e.empno, e.deptno, e.sal)
WHERE e.sal > 1000                 -- INSERT 절의 조건절도 지정이 가능하다
COMMIT;
 
 
-- 20부서의 급여가 10%증가했고, 30부서는 등록되었는지 확인해 보자
SELECT * FROM emp_merge_test;
 
 
-- 다음 테스트를 위해서 emp_merge_test 테이블에 30부서 데이터를 삭제하자
-- 부서번호 10과 20의 사원정보만 남을 것이다.
DELETE FROM emp_merge_test WHERE deptno = 30;
COMMIT;

* MERGE 예제 2

- 아래는 부서 번호 10의 사원 급여를 10% 인상하고, 부서번호 20의 사원정보는 삭제하며, 부서번호 30의 사원 급여를 20% 인상하는 예이다. (INSERT, UPDATE, DELETE 예제)

-- 30 부서가 삭제되었는지 확인한다.
SELECT * FROM emp_merge_test;
 
 
-- MERGE 문 작성
MERGE INTO emp_merge_test m
USING emp e
ON (m.empno = e.empno)
WHEN MATCHED THEN
  UPDATE SET m.sal = ROUND(m.sal*1.1)
  DELETE WHERE (m.deptno = 20)   -- 부서번호 20의 사원정보는 삭제.
WHEN NOT MATCHED THEN
INSERT (m.empno, m.deptno, m.sal)
VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));
COMMIT;
 
 
-- 정상적으로 변경되었는지 확인해 보자
SELECT * FROM emp_merge_test;
 
 
-- 테스트 테이블은 삭제하자
DROP TABLE emp_merge_test;

 

 

 

 

 

 

 

 

 

출처:www.gurubee.net/lecture/2225

728x90
반응형