[oracle] merge into

MERGE 문의 이해

• - MERGE문은 조건에 따라서 데이터의 삽입,갱신,삭제 작업을 한번에 할 수 있다.
• - 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있음)를 수행하고, 새로운 행일경우 INSERT를 수행한다.
• - 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.
• - MERGE문에서 CLOB 사용시 업데이트 할 내용이 2000bytes가 넘을때 ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.

MERGE Syntax

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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 예제)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

-- 부서번호 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 예제)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

-- 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;

태그

• MERGE

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2225
• - 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

 

 

 

 

 

 

 

 

LISS_HRIF_INF ->인사 인터페이스 TB

LISS_HRIF_TB _> 인사 TB

 

PK로 존재 유무 확인하고 있으면  HRIF_TB 에 HRIF_INF 값들을 UPDATE 하고

UPDATE 하는 애들 중에 ZCMDGN 값이 D 인 애들은 삭제한다

DELETE 는 단독으로 사용 불가.. Specify the DELETE where_clause to clean up data in a table while populating or updating it.

 

존재하지 않는 애들은 INSERT함

 

 

 

CREATE OR REPLACE PROCEDURE LMCS_ADM.test2
IS
BEGIN
  MERGE INTO LISS_HRIF_TB m
  USING LISS_HRIF_INF e
  ON (m.PERNR = e.PERNR)
  WHEN MATCHED THEN
    update set
      ZPERNR = e.ZPERNR
      ,ENAME =  e.ENAME
      ,ORGEH =  e.ORGEH
      ,INFDATE =  sysdate
    DELETE WHERE ( e.ZCMDGN='D' )
  WHEN NOT MATCHED THEN
  INSERT VALUES
    (
   e.PERNR, e.ZPERNR, e.ENAME, e.ORGEH, e.ZCORGCD, e.ZMORGNM, e.ZCORG03, e.ZCORGCD1, e.ZMORGNM1, e.ZCMOFGU, e.ZCORGCD3, e.ZMORGNM3, e.ZCJIKUN2, e.ZMJIKUN2, e.ZCJIKWI, e.ZMJIKWI,
  e.ZCCAREA, e.ZMWAREA, e.ZCPAYCS, e.ZCMASSN, e.ZDMASDT, e.ZCMDGN, e.AEDTM,  sysdate
     ) WHERE e.ZCMDGN = 'I';
  COMMIT;

  exception when others then
    rollback;
END;