DML: INSERT·UPDATE·DELETE·MERGE

핵심 요약

DML은 테이블의 행을 삽입·수정·삭제한다. 실행할 명령뿐 아니라 대상 행, 영향 행 수, 변경을 확정하는 시점까지 함께 판단해야 한다.

INSERT·UPDATE·DELETE의 기본 목적은 SQL 공통이지만 MERGE 세부 문법과 TRUNCATE의 트랜잭션 효과는 DBMS별로 다르다. 이 단원의 비교와 예제는 Oracle의 일반 테이블을 기준으로 한다.

학습 목표

• INSERT·UPDATE·DELETE의 대상 행과 영향을 예측한다.
• MERGE의 일치·불일치 분기와 소스 키의 유일성을 확인한다.
• DELETE와 TRUNCATE 뒤의 COMMIT·ROLLBACK 결과를 구분한다.

본문

개념 지도

대상 행 식별→INSERT·UPDATE·DELETE·MERGE→영향 행 확인→COMMIT·ROLLBACK

INSERT·UPDATE·DELETE의 대상

INSERT는 새 행을 만들고, UPDATE는 기존 행의 값을 바꾸며, DELETE는 조건에 맞는 행을 제거한다.

INSERT INTO tbl (id, amt, degree)
VALUES (1, 100, 'A');

UPDATE account
SET balance = balance - 10000
WHERE account_no = 'A001';

INSERT에서는 열 목록과 VALUES의 값을 순서대로 대응시킨다. 열 목록에서 생략한 열에는 기본값 또는 NULL이 들어가므로 NOT NULL 제약조건도 확인해야 한다. 열 목록 자체를 생략하면 테이블이 요구하는 열 순서와 값의 수를 맞춰야 한다.

UPDATE의 SET 식은 각 대상 행의 변경 전 값을 기준으로 계산한다. UPDATE와 DELETE에서 WHERE를 생략하면 테이블의 모든 행이 대상이다. 조건이 없다는 이유로 문법 오류가 발생하는 것은 아니다.

MERGE의 ON과 처리 분기

MERGE는 소스 행과 대상 행을 비교해 일치하면 UPDATE, 일치하는 대상 행이 없으면 INSERT하는 작업을 한 문장으로 표현한다.

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN
  INSERT (id, val) VALUES (s.id, s.val);

대상에는 두 행, 소스에는 세 행이 있다고 하자. 각 테이블의 id는 중복되지 않는다.

위 MERGE의 결과는 다음과 같이 계산한다.

ON은 값이 바뀌었는지가 아니라 어떤 소스 행을 어떤 대상 행과 연결할지 결정한다. 위 예제의 ON에는 val이 없으므로 val의 차이는 일치 여부에 영향을 주지 않는다.

UPDATE 절에 WHERE s.val >= 20을 추가하면 id 1은 일치한 상태에서 갱신만 건너뛴다. 이때 기존 val 10이 유지되며 INSERT 분기로 넘어가지 않는다.

최신 소스 한 건으로 MERGE

같은 대상 행을 한 MERGE 안에서 여러 번 갱신할 수 없다. 같은 부서 코드의 변경 이력이 여러 건이면 먼저 적용할 소스 한 건을 정한다. 다음 예제는 change_dt에 변경 시각이 모두 저장돼 있다고 가정한다.

MERGE INTO dept d
USING (
  SELECT dept_code, dept_name, manager
  FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (
             PARTITION BY dept_code
             ORDER BY change_dt DESC
           ) rn
    FROM tmp_dept t
  )
  WHERE rn = 1
) s
ON (d.dept_code = s.dept_code)
WHEN MATCHED THEN
  UPDATE SET d.manager = s.manager
WHEN NOT MATCHED THEN
  INSERT (dept_code, dept_name, manager)
  VALUES (s.dept_code, s.dept_name, s.manager);

같은 부서 안에서 change_dt까지 같으면 어느 행이 선택되는지 모호해질 수 있다. 이 경우 업무 규칙에 맞는 고유한 순서 기준을 ORDER BY에 추가한다. MERGE 문법과 함께 소스 키의 유일성도 확인해야 한다.

영향 행 수와 트랜잭션

DML이 오류 없이 끝났어도 기대한 행이 변경됐는지 확인해야 한다.

UPDATE orders
SET status = 'EXPIRED'
WHERE status = 'READY'
  AND expire_at < SYSTIMESTAMP;

대상 행은 READY 상태이면서 만료 시각이 지난 주문이다. 조건을 만족하는 주문이 없으면 영향 행 수는 0이며, 그 자체로 SQL 오류를 뜻하지 않는다. 한 행만 변경하려던 작업에서 여러 행이 변경됐다면 조건과 키를 다시 확인해야 한다.

실행 전에는 같은 조건의 SELECT로 대상 키와 건수를 살펴보고, 실행 후에는 애플리케이션의 영향 행 수 또는 PL/SQL의 SQL%ROWCOUNT를 확인한다. SQL%ROWCOUNT는 가장 최근 SQL을 대상으로 하므로 DML 직후 변수에 보관한다.

WHERE가 없는 UPDATE나 DELETE의 범위를 미리 확인할 때는 대상 테이블 전체를 조회한다.

SELECT COUNT(*)
FROM orders;

조회와 변경 사이에 다른 세션이 자료를 바꿀 수 있으므로 실제 영향 행 수도 확인해야 한다. 자동 커밋이 꺼져 있고 중간에 변경을 확정하는 명령이 없다면, DML의 변경을 COMMIT으로 확정하거나 ROLLBACK으로 취소할 수 있다.

DELETE와 TRUNCATE의 차이

Oracle의 일반 테이블에서는 두 명령 모두 테이블 정의를 남기지만 트랜잭션 효과가 다르다.

DELETE FROM orders; 뒤에 COMMIT하면 삭제가 확정되므로 이후 ROLLBACK으로 원래 행을 되살릴 수 없다. 테이블 정의 자체를 제거하려면 DROP TABLE을 사용한다.

앞서 바꾼 다른 테이블도 확인한다

다음 두 예제는 서로 독립적이다. 각각 아래의 커밋된 자료에서 시작하며, 자동 커밋은 꺼져 있고 모든 명령은 같은 세션에서 실행한다고 하자.

• dml_balance에는 id = 1, amount = 100인 행이 한 개 있다.
• dml_work에는 행이 한 개 있다.

예제 A: DELETE 후 ROLLBACK

UPDATE dml_balance
SET amount = 80
WHERE id = 1;

DELETE FROM dml_work;

ROLLBACK;

두 변경이 같은 트랜잭션에 있으므로 UPDATE와 DELETE가 함께 취소된다.

예제 B: TRUNCATE 후 ROLLBACK

UPDATE dml_balance
SET amount = 80
WHERE id = 1;

TRUNCATE TABLE dml_work;

ROLLBACK;

TRUNCATE를 실행하기 전에 앞선 UPDATE가 커밋된다. 마지막 ROLLBACK은 이미 확정된 UPDATE와 TRUNCATE를 취소하지 못한다.

TRUNCATE로 비운 테이블만 보지 말고, 같은 세션에서 앞서 실행한 DML도 확인해야 한다.

SELECT 결과를 INSERT하기

다른 테이블이나 서브쿼리에서 읽은 결과를 INSERT할 수도 있다.

INSERT INTO order_archive
SELECT *
FROM orders
WHERE order_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12);

이 예제는 두 테이블의 열 순서와 자료형이 대응한다고 가정한다. 구조가 다르거나 일부 열만 옮길 때는 INSERT의 열 목록과 SELECT의 열 목록을 명시한다.

집합 DML은 반복 호출을 줄일 수 있지만 한 트랜잭션이 커질 수 있다. 많은 행을 처리할 때는 Undo·Redo, 잠금 유지 시간, 실패 시 복구 범위를 함께 검토한다.

오류 처리

대량 적재에서 일부 행의 오류를 별도로 기록해야 한다면 DML Error Logging을 검토할 수 있다. 업무가 전부 성공하거나 전부 취소돼야 한다면 부분 성공을 허용할 수 있는지 먼저 정한다.

일반적인 DML 실행 오류로 한 문장이 실패하면 그 문장이 만든 변경이 취소된다. 같은 트랜잭션에서 앞서 성공한 다른 문장까지 자동으로 취소되는 것은 아니므로, 업무 전체의 성공 여부에 따라 COMMIT 또는 ROLLBACK을 결정한다.