Oracle/SQL2020. 7. 6. 08:45

MERGE ๋ฌธ์˜ ๋™์‹œ์„ฑ

MERGE ๋ฌธ์€ WHEN ์ ˆ์˜ ๊ธฐ์ˆ  ์ˆœ์„œ์™€ ๋ฌด๊ด€ํ•˜๊ฒŒ UPDATE, INSERT ์ˆœ์„œ๋กœ ๋™์ž‘ํ•œ๋‹ค. ์ด๋•Œ ON ์ ˆ์€ UPDATE ๋ฌธ์˜ WHERE ์ ˆ๊ณผ ์œ ์‚ฌํ•˜๊ฒŒ ๋™์ž‘ํ•œ๋‹ค. ์ด๋กœ ์ธํ•ด ์˜ˆ์ƒ์น˜ ๋ชปํ•œ ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋‹ค.


ํ…Œ์ŠคํŠธ๋ฅผ ์œ„ํ•ด ์•„๋ž˜์˜ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜์ž.

1
2
3
4
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(1), c2 NUMBER);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);

S1 ์„ธ์…˜์—์„œ ์•„๋ž˜์˜ MERGE ๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜์ž. ์ตœ์ดˆ ๊ฑด์€ INSERT ๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜๊ณ , ์ดํ›„ ๋™์ผ ๊ฑด์— ๋Œ€ํ•ด UPDATE ๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜๋ ค๋Š” ์˜๋„๋กœ ์ž‘์„ฑ๋œ MERGE ๋ฌธ์ด๋‹ค.
1
2
3
4
5
6
7
8
9
10
11
-- 2: S1
MERGE
 INTO t1
USING DUAL
   ON (c1 = 'A')
 WHEN MATCHED THEN
      UPDATE SET c2 = c2 + 1
 WHEN NOT MATCHED THEN
      INSERT VALUES ('A', 1);
 
1 ํ–‰์ด ๋ณ‘ํ•ฉ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

S2 ์„ธ์…˜์—์„œ ๋™์ผํ•œ MERGE ๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜๋ฉด ๋ธ”๋กœํ‚น์ด ๋ฐœ์ƒํ•œ๋‹ค.
1
2
3
4
5
6
7
8
9
10
11
-- 3: S2
MERGE
 INTO t1
USING DUAL
   ON (c1 = 'A')
 WHEN MATCHED THEN
      UPDATE SET c2 = c2 + 1
 WHEN NOT MATCHED THEN
      INSERT VALUES ('A', 1);
 
-- ๋ธ”๋กœํ‚น

S1 ์„ธ์…˜์—์„œ ์ปค๋ฐ‹์„ ์ˆ˜ํ–‰ํ•˜์ž.
1
2
3
4
-- 4: S1
COMMIT;
 
์ปค๋ฐ‹์ด ์™„๋ฃŒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

S2 ์„ธ์…˜์€ UPDATE ๋ฌธ์ด ์ˆ˜ํ–‰๋˜์ง€ ์•Š๊ณ  ORA-00001 ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•œ๋‹ค.
1
2
-- 5: S2
ORA-00001: ๋ฌด๊ฒฐ์„ฑ ์ œ์•ฝ ์กฐ๊ฑด(SCOTT.T1_PK)์— ์œ„๋ฐฐ๋ฉ๋‹ˆ๋‹ค

์˜ˆ์ œ์˜ MERGE ๋ฌธ์€ ์•„๋ž˜ ์ฝ”๋“œ์™€ ์œ ์‚ฌํ•˜๊ฒŒ ๋™์ž‘ํ•œ๋‹ค. UPDATE ๋ฌธ์˜ WHERE ์ ˆ์€ consistent read๋กœ ๋™์ž‘ํ•œ๋‹ค. S2 ์„ธ์…˜์˜ MERGE ๋ฌธ์€ UPDATE ๋Œ€์ƒ์ด ์—†์–ด INSERT ๋ฌธ์ด ์ˆ˜ํ–‰ํ–ˆ์ง€๋งŒ, S1 ์„ธ์…˜์ด ๋™์ผ PK๋กœ INSERT ๋ฌธ์ด ์ˆ˜ํ–‰ํ•˜๊ณ  ์žˆ์–ด ๋ธ”๋กœํ‚น์ด ๋ฐœ์ƒํ•œ ๊ฒƒ์ด๋‹ค.

1
2
3
4
5
6
7
8
-- 6
BEGIN
    UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'A';
 
    IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO t1 VALUES ('A', 1);
    END IF;
END;

์˜๋„๋Œ€๋กœ ๊ตฌ๋ฌธ์„ ๋™์ž‘์‹œํ‚ค๋ ค๋ฉด ์•„๋ž˜ ์ฝ”๋“œ๋ฅผ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค. S1 ์„ธ์…˜์—์„œ ์•„๋ž˜์˜ ์ฝ”๋“œ๋ฅผ ์ˆ˜ํ–‰ํ•˜์ž.
1
2
3
4
5
6
7
8
9
10
-- 7: S1
BEGIN
    INSERT INTO t1 VALUES ('B', 1);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'B';
END;
/
 
PL/SQL ์ฒ˜๋ฆฌ๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ์™„๋ฃŒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

S1 ์„ธ์…˜์—์„œ ๋™์ผํ•œ ์ฝ”๋“œ๋ฅผ ์ˆ˜ํ–‰ํ•˜๋ฉด ๋ธ”๋กœํ‚น์ด ๋ฐœ์ƒํ•œ๋‹ค.
1
2
3
4
5
6
7
8
9
10
-- 8: S2
BEGIN
    INSERT INTO t1 VALUES ('B', 1);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE t1 SET c2 = c2 + 1 WHERE c1 = 'B';
END;
/
 
-- ๋ธ”๋กœํ‚น

S1 ์„ธ์…˜์—์„œ ์ปค๋ฐ‹์„ ์ˆ˜ํ–‰ํ•˜์ž.
1
2
3
4
-- 9: S1
COMMIT;
 
์ปค๋ฐ‹์ด ์™„๋ฃŒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

S2 ์„ธ์…˜์€ ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•˜์ง€ ์•Š๊ณ  UPDATE ๋ฌธ์ด ์ˆ˜ํ–‰๋œ๋‹ค. ์—๋Ÿฌ๋Š” ๋ฐœ์ƒํ•˜์ง€ ์•Š์ง€๋งŒ UPDATE ๋ฌธ์œผ๋กœ ์ธํ•ด ๋™์‹œ์„ฑ ์ €ํ•˜๋Š” ์—ฌ์ „ํžˆ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์ ์— ์ฃผ์˜ํ•ด์•ผ ํ•œ๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
-- 10: S2
 
PL/SQL ์ฒ˜๋ฆฌ๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ์™„๋ฃŒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.
 
SELECT * FROM t1;
 
C1 C2
-- --
A   1
B   2
 
2 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


์ €์ž‘์žํ‘œ์‹œ ๋น„์˜๋ฆฌ ๋ณ€๊ฒฝ๊ธˆ์ง€

'Oracle > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

ํŠน์ • ํ–‰์˜ ๊ณ„์ธต ๊ฒฝ๋กœ ์กฐํšŒ  (0) 2020.03.03
์—ฐ์† ๊ฐ’ ๊ทธ๋ฃนํ•‘  (0) 2019.09.02
ORA-00937 ์—๋Ÿฌ  (0) 2019.04.22
NVL, NVL2 ํ•จ์ˆ˜์˜ ์ธ์ˆ˜ ํ‰๊ฐ€  (0) 2019.03.29
์šฉ์–ด ๋‹จ์–ด ๋ถ„๋ฆฌ  (0) 2019.03.14
Posted by ์ •ํฌ๋ฝ_

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”