[SQL] Oracle Merge(Insert, Update, Delete) 한번에 작업 하는 방법

오라클에서 Merge 문법을 이용해서 입력, 수정, 삭제를 한 번에 해보려고 합니다. 오라클 공식 문서를 보면 merge_update_clause UPDATE에 매칭되는 데이터는 삭제할 수 있다고 나와 있습니다. 풀어서 말씀드리면, WHEN MATCHED THEN에서  UPDATE 진행 후 DELETE를 할 수 있다는 말입니다. 보통은 DELETE 문법을 이용해서 삭제 후 INSERT 문법을 활용하는데 이렇게 하면 인덱스가 점점 안 좋아 질 수 있고, 서비스 테이블에서 조회하려는 시점에 아무 정보도 못 얻어서 비지니스 적인 오류가 발생합니다. 이러한 부분을 해결하기 위해서는 한 번에 DML할 수 있다면 여러가지 장점을 가질 수 있습니다. 

HR 서버에서 회원 정보를 일자별로 반영해야 되는 업무를 토대로 진행 해보겠습니다. 회원 정보를 원장 테이블에 보관하고, 반영 일자에 속한 데이터를 서비스 테이블에 반영하는 방법으로 자세한 내용은 아래와 같이 정리 하겠습니다.

ESP_LEDG_MBR_INFO(원장회원정보)

원장회원정보 테이블로서 HR 서버에서 전달 받은 데이터를 의미하며, 반영 일자에 해당하는 데이터를 서비스 테이블에 적재 하고자 합니다.

ESP_MBR_INFO(회원정보)

원장회원정보를 기준으로 현재 시점의 데이터이며, 서비스 형태의 테이블입니다.

1. 테이블 생성

 
/* EstenPark_회원정보 */
CREATE TABLE ESP_MBR_INFO (
 MBR_NO NUMBER(10) NOT NULL, /* 회원번호 */
 MBR_ID VARCHAR2(30) NOT NULL, /* 회원ID */
 MBR_NM VARCHAR2(100) NOT NULL, /* 회원명 */
 MBR_TEL_NO VARCHAR2(15), /* 회원전화번호 */
 REG_USER VARCHAR2(30) DEFAULT 'EstenPark' NOT NULL, /* 등록사용자 */
 REG_DTTM DATE DEFAULT SYSDATE NOT NULL /* 등록일시 */
)
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 NOROWDEPENDENCIES
 DISABLE ROW MOVEMENT;

COMMENT ON TABLE ESP_MBR_INFO IS 'EstenPark_회원정보|회원 정보 테이블|';

COMMENT ON COLUMN ESP_MBR_INFO.MBR_NO IS '컬럼값|컬럼값||';

COMMENT ON COLUMN ESP_MBR_INFO.MBR_ID IS '회원ID';

COMMENT ON COLUMN ESP_MBR_INFO.MBR_NM IS '회원명';

COMMENT ON COLUMN ESP_MBR_INFO.MBR_TEL_NO IS '회원전화번호';

COMMENT ON COLUMN ESP_MBR_INFO.REG_USER IS '등록사용자|등록사용자||';

COMMENT ON COLUMN ESP_MBR_INFO.REG_DTTM IS '등록일시|등록일시||';

CREATE UNIQUE INDEX ESP_MBR_INFO_PK
 ON ESP_MBR_INFO (
  MBR_NO ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

ALTER TABLE ESP_MBR_INFO
 ADD
  CONSTRAINT ESP_MBR_INFO_PK
  PRIMARY KEY (
   MBR_NO
  )
  NOT DEFERRABLE
  INITIALLY IMMEDIATE
  ENABLE
  VALIDATE;

/* EstenPark_원장회원정보 */
CREATE TABLE ESP_LEDG_MBR_INFO (
 LEDG_REFLT_DT VARCHAR2(8) NOT NULL, /* 원장반영일자 */
 MBR_NO NUMBER(10) NOT NULL, /* 회원번호 */
 MBR_ID VARCHAR2(30) NOT NULL, /* 회원ID */
 MBR_NM VARCHAR2(100) NOT NULL, /* 회원명 */
 MBR_TEL_NO VARCHAR2(15), /* 회원전화번호 */
 REG_USER VARCHAR2(30) DEFAULT 'EstenPark' NOT NULL, /* 등록사용자 */
 REG_DTTM DATE DEFAULT SYSDATE NOT NULL /* 등록일시 */
)
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 NOROWDEPENDENCIES
 DISABLE ROW MOVEMENT;

COMMENT ON TABLE ESP_LEDG_MBR_INFO IS 'EstenPark_원장회원정보|원장 회원 정보로 하루에 한번씩 데이터를 쌓는다.|';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.LEDG_REFLT_DT IS '원장반영일자';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.MBR_NO IS '컬럼값|컬럼값||';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.MBR_ID IS '회원ID';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.MBR_NM IS '회원명';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.MBR_TEL_NO IS '회원전화번호';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.REG_USER IS '등록사용자|등록사용자||';

COMMENT ON COLUMN ESP_LEDG_MBR_INFO.REG_DTTM IS '등록일시|등록일시||';

CREATE UNIQUE INDEX ESP_LEDG_MBR_INFO_PK
 ON ESP_LEDG_MBR_INFO (
  LEDG_REFLT_DT ASC,
  MBR_NO ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

ALTER TABLE ESP_LEDG_MBR_INFO
 ADD
  CONSTRAINT ESP_LEDG_MBR_INFO_PK
  PRIMARY KEY (
   LEDG_REFLT_DT,
   MBR_NO
  )
  NOT DEFERRABLE
  INITIALLY IMMEDIATE
  ENABLE
  VALIDATE;

2. 데이터 생성

 
/* 데이터 삭제 */
truncate table ESP_MBR_INFO;

truncate table ESP_LEDG_MBR_INFO;

/* ESP_MBR_INFO 테이블 데이터 생성 */
INSERT INTO ESP_MBR_INFO A( A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no ) VALUES (1, 'Admin', '관리자', '010-9999-9999');

INSERT INTO ESP_MBR_INFO A( A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no ) VALUES (2, 'user02', '이철수', '010-2222-9999');

INSERT INTO ESP_MBR_INFO A( A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no ) VALUES (3, 'user03', '김우현', '010-9999-3333');

INSERT INTO ESP_MBR_INFO A( A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no ) VALUES (4, 'user04', '신하윤', '010-9999-4444');

INSERT INTO ESP_MBR_INFO A( A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no ) VALUES (5, 'user05', '박유일', '010-9999-5555');

/* ESP_LEDG_MBR_INFO 원장 테이블 데이터 생성-> 과거 데이터 */
INSERT INTO ESP_LEDG_MBR_INFO A (A.ledg_reflt_dt, A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no)
SELECT    '20161211' as ledg_reflt_dt
         ,b.mbr_no, b.mbr_id, b.mbr_nm, b.mbr_tel_no
FROM      ESP_MBR_INFO B      
;

/* ESP_LEDG_MBR_INFO 원장 테이블 데이터 생성-> 현재 데이터 */
INSERT INTO ESP_LEDG_MBR_INFO A (A.ledg_reflt_dt, A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no)
SELECT    '20161212' as ledg_reflt_dt
         ,b.mbr_no, b.mbr_id, b.mbr_nm, b.mbr_tel_no
FROM      ESP_MBR_INFO B      
WHERE     B.MBR_NO IN(1, 2, 5)
;

/* ESP_LEDG_MBR_INFO 원장 테이블 데이터 생성-> 현재 데이터-> 추가 데이터 */
INSERT INTO ESP_LEDG_MBR_INFO A (A.ledg_reflt_dt, A.mbr_no, A.mbr_id, A.mbr_nm, A.mbr_tel_no) VALUES('20161212', 6, 'user06', '추가열', '010-9999-9393');

/* ESP_LEDG_MBR_INFO 원장 테이블 데이터 생성-> 현재 데이터-> 변경 데이터 */
UPDATE ESP_LEDG_MBR_INFO A
SET    a.mbr_tel_no = '010-8888-8888'
WHERE  a.mbr_no = 1;

COMMIT;

3. 데이터 분석 및 Merge 활용 방안

ESP_LEDG_MBR_INFO Data exists

ESP_LEDG_MBR_INFO 테이블에서 최근 반영해야 되는 회원 정보를 적재 하기 위한 테이블이며, ESP_MBR_INFO 테이블에 존재하는 대상 데이터라고 생각 하면 됩니다. UPDATE, INSERT 대상만 존재 합니다.

ESP_LEDG_MBR_INFO No data exists

ESP_LEDG_MBR_INFO 원장 테이블 기준으로 볼 때 ESP_MBR_INFO 존재 하지 않기 때문에 삭제 대상 데이터 입니다. 따라서 해당 데이터를 만들기 위해서는 별도의 비지니스 로직이 필요합니다. 

4. Merge 문법 활용

 
MERGE INTO ESP_MBR_INFO A
USING     (
           /* UPDATE, INSERT 대상 데이터 로직 구현 */
           SELECT    A.MBR_NO, A.MBR_ID, A.MBR_NM, A.MBR_TEL_NO, A.REG_USER, A.REG_DTTM, 'N' AS DEL_TP_YN
           FROM      ESP_LEDG_MBR_INFO A
           WHERE    A.LEDG_REFLT_DT = '20161212'
           UNION ALL 
           /* DELETE 대상 데이터 로직 구현 */
           SELECT    A.MBR_NO, A.MBR_ID, A.MBR_NM, A.MBR_TEL_NO, A.REG_USER, A.REG_DTTM, 'Y' AS DEL_TP_YN
           FROM      ESP_MBR_INFO A
           WHERE     1=1
           AND       NOT EXISTS ( SELECT   'O' 
                                  FROM     ESP_LEDG_MBR_INFO SS1
                                  WHERE    SS1.LEDG_REFLT_DT ='20161212'    
                                  AND      SS1.MBR_NO = A.MBR_NO
                                  )
           ) B
ON        (A.MBR_NO = B.MBR_NO)                      
WHEN MATCHED THEN
  UPDATE SET A.MBR_ID     = B.MBR_ID     ,
             A.MBR_NM     = B.MBR_NM     ,
             A.MBR_TEL_NO = B.MBR_TEL_NO ,
             A.REG_USER   = B.REG_USER   ,
             A.REG_DTTM   = B.REG_DTTM   
  DELETE WHERE (B.DEL_TP_YN = 'Y') /* 삭제구분여부(DEL_TP_YN)에서 Y일 경우 삭제 처리 */
WHEN NOT MATCHED THEN
  INSERT    (A.MBR_NO, A.MBR_ID, A.MBR_NM, A.MBR_TEL_NO, A.REG_USER, A.REG_DTTM
            )
  VALUES    (B.MBR_NO, B.MBR_ID, B.MBR_NM, B.MBR_TEL_NO, B.REG_USER, B.REG_DTTM );
;

5. 결과

 
SQL> SELECT A.* FROM ESP_MBR_INFO A;

    MBR_NO MBR_ID     MBR_NM     MBR_TEL_NO      REG_USER        REG_DTTM
---------- ---------- ---------- --------------- --------------- --------
         1 Admin      관리자     010-8888-8888   EstenPark       16/12/22
         2 user02     이철수     010-2222-9999   EstenPark       16/12/22
         5 user05     박유일     010-9999-5555   EstenPark       16/12/22
         6 user06     추가열     010-9999-9393   EstenPark       16/12/22