DATA 전문가로 가는 길

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

Data Architecture/SQL Query

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

EstenPark 2016. 12. 22. 15:29

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



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


ESP_LEDG_MBR_INFO(원장회원정보)

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


ESP_MBR_INFO(회원정보)

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


1. 테이블 생성

?
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
/* 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. 데이터 생성

?
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
33
34
35
36
37
38
39
40
/* 데이터 삭제 */
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 문법 활용

?
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
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. 결과

?
1
2
3
4
5
6
7
8
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



저작자표시 (새창열림)

'Data Architecture > SQL Query' 카테고리의 다른 글

[SQL] Oracle 필수 스크립트 모음(딕셔너리, SQL 문법, 성능 분석)  (0) 2017.03.13
[SQL] MariaDB/Mysql 구분자(콤마, 파이프라인 등등)를 기준으로 열(row) 변환  (2) 2017.02.16
[SQL] Oracle 자동 테이블 레이아웃 만들기(Tablelayout, 테이블 정의서)  (0) 2016.06.03
[SQL] Oracle 구분자 행, 열, 분리, 자르기(REGEXP_SUBSTR, REGEXP_COUNT)  (6) 2016.06.02
[SQL] Oracle 결재 신청된 결재자 리스트와 최종 결재자 조회(MAX 분석 함수 KEEP 활용)  (0) 2016.05.19
'Data Architecture/SQL Query' Related Articles more
Comments