Oracle/SQL2020. 7. 6. 08:45

MERGE 문의 λ™μ‹œμ„±

MERGE 문은 WHEN 절의 기술 μˆœμ„œμ™€ λ¬΄κ΄€ν•˜κ²Œ UPDATE, INSERT μˆœμ„œλ‘œ λ™μž‘ν•œλ‹€. μ΄λ•Œ ON μ ˆμ€ UPDATE 문의 WHERE 절과 μœ μ‚¬ν•˜κ²Œ λ™μž‘ν•œλ‹€. μ΄λ‘œ 인해 μ˜ˆμƒμΉ˜ λͺ»ν•œ μ—λŸ¬κ°€ λ°œμƒν•  수 μžˆλ‹€.


ν…ŒμŠ€νŠΈλ₯Ό μœ„ν•΄ μ•„λž˜μ˜ ν…Œμ΄λΈ”μ„ μƒμ„±ν•˜μž. 

-- 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 문이닀. 
-- 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 문을 μˆ˜ν–‰ν•˜λ©΄ λΈ”λ‘œν‚Ήμ΄ λ°œμƒν•œλ‹€. 
-- 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 μ„Έμ…˜μ—μ„œ 컀밋을 μˆ˜ν–‰ν•˜μž. 
-- 4: S1
COMMIT;

컀밋이 μ™„λ£Œλ˜μ—ˆμŠ΅λ‹ˆλ‹€.

S2 μ„Έμ…˜μ€ UPDATE 문이 μˆ˜ν–‰λ˜μ§€ μ•Šκ³  ORA-00001 μ—λŸ¬κ°€ λ°œμƒν•œλ‹€. 
-- 5: S2
ORA-00001: 무결성 μ œμ•½ 쑰건(SCOTT.T1_PK)에 μœ„λ°°λ©λ‹ˆλ‹€

예제의 MERGE 문은 μ•„λž˜ μ½”λ“œμ™€ μœ μ‚¬ν•˜κ²Œ λ™μž‘ν•œλ‹€. UPDATE 문의 WHERE μ ˆμ€ consistent read둜 λ™μž‘ν•œλ‹€. S2 μ„Έμ…˜μ˜ MERGE 문은 UPDATE λŒ€μƒμ΄ μ—†μ–΄ INSERT 문이 μˆ˜ν–‰ν–ˆμ§€λ§Œ, S1 μ„Έμ…˜μ΄ 동일 PK둜 INSERT 문이 μˆ˜ν–‰ν•˜κ³  μžˆμ–΄ λΈ”λ‘œν‚Ήμ΄ λ°œμƒν•œ 것이닀. 

-- 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 μ„Έμ…˜μ—μ„œ μ•„λž˜μ˜ μ½”λ“œλ₯Ό μˆ˜ν–‰ν•˜μž. 
-- 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 μ„Έμ…˜μ—μ„œ λ™μΌν•œ μ½”λ“œλ₯Ό μˆ˜ν–‰ν•˜λ©΄ λΈ”λ‘œν‚Ήμ΄ λ°œμƒν•œλ‹€. 
-- 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 μ„Έμ…˜μ—μ„œ 컀밋을 μˆ˜ν–‰ν•˜μž. 
-- 9: S1
COMMIT;

컀밋이 μ™„λ£Œλ˜μ—ˆμŠ΅λ‹ˆλ‹€.

S2 μ„Έμ…˜μ€ μ—λŸ¬κ°€ λ°œμƒν•˜μ§€ μ•Šκ³  UPDATE 문이 μˆ˜ν–‰λœλ‹€. μ—λŸ¬λŠ” λ°œμƒν•˜μ§€ μ•Šμ§€λ§Œ UPDATE 문으둜 인해 λ™μ‹œμ„± μ €ν•˜λŠ” μ—¬μ „νžˆ λ°œμƒν•  수 μžˆλ‹€λŠ” 점에 μ£Όμ˜ν•΄μ•Ό ν•œλ‹€. 

-- 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 정희락_

ν‹°μŠ€ν† λ¦¬νˆ΄λ°”