DML

상위 문서: SQL

Data Manipulation Language; 데이터 조작어

정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어
데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공합니다.

종류	역할
SELECT	데이터를 조회하는 역할을 합니다.
INSERT	데이터를 삽입하는 역할을 합니다.
UPDATE	데이터를 수정하는 역할을 합니다.
DELETE	데이터를 삭제하는 역할을 합니다.

SELECT를 DML에 넣는가

Oracle SQL Language Reference는 CALL, DELETE, EXPLAIN PLAN, INSERT, LOCK TABLE, MERGE, SELECT, UPDATE를 DML로 분류한다. 즉 Oracle 분류에서는 SELECT와 MERGE도 DML이다.
일부 교재는 SELECT를 데이터 질의어(DQL, Data Query Language)로 따로 떼어 설명한다. 분류 방식의 차이일 뿐 문장의 동작은 같다.
DML로 바꾼 데이터는 TCL의 COMMIT으로 확정하거나 ROLLBACK으로 취소한다. Oracle은 DML 뒤에 COMMIT을 명시해야 하고, SQL Server는 기본이 자동 커밋이다.

문법 요약

예제 테이블은 상품(코드, 이름, 가격)이며 코드가 기본 키이다. 자세한 문법은 SQL INSERT 구문, SQL UPDATE 구문, SQL DELETE 구문 문서를 참고한다.

INSERT

INSERT INTO 상품 VALUES ('P1', '연필', 500);          -- 칼럼 목록 생략: 테이블의 모든 칼럼에 정의 순서대로 값을 준다
INSERT INTO 상품 (코드, 이름) VALUES ('P2', '공책');  -- 빠진 칼럼은 DEFAULT 값, 없으면 NULL
INSERT INTO 상품 VALUES ('P3', '지우개', 300);
SELECT * FROM 상품 ORDER BY 코드;

코드	이름	가격
P1	연필	500
P2	공책	NULL
P3	지우개	300

칼럼 목록을 생략하면 값의 개수와 순서가 테이블 정의와 정확히 맞아야 한다.
여러 행을 한 번에 넣을 때는 INSERT INTO 테이블 SELECT ...를 쓴다. 이때 VALUES 절은 쓰지 않는다.

INSERT INTO 싼상품 SELECT 코드, 가격 FROM 상품 WHERE 가격 < 1000;

VALUES (...), (...)처럼 VALUES 뒤에 여러 행을 나열하는 문법은 SQL Server, MySQL, PostgreSQL에서 쓸 수 있다. Oracle은 19c까지 지원하지 않고 23ai부터 지원한다.
Oracle의 INSERT ALL은 서브쿼리 결과를 조건에 따라 여러 테이블에 나누어 넣는다.

INSERT ALL
  WHEN 가격 >= 700 THEN INTO 비싼상품 VALUES (코드, 가격)
  WHEN 가격 < 700  THEN INTO 싼상품   VALUES (코드, 가격)
SELECT 코드, 가격 FROM 상품;

UPDATE

UPDATE 상품 SET 가격 = 600 WHERE 코드 = 'P1';
UPDATE 상품 SET 가격 = (SELECT MAX(가격) FROM 입고) WHERE 코드 = 'P2';  -- 서브쿼리로 SET

SET 절의 서브쿼리는 한 행 한 값만 돌려주어야 한다. 여러 행을 돌려주면 ORA-01427 오류가 나고, 결과가 없으면 NULL이 들어간다.
WHERE 절이 없으면 모든 행이 바뀐다.

DELETE

DELETE FROM 상품 WHERE 코드 = 'P3';
DELETE FROM 상품;   -- WHERE가 없으면 모든 행 삭제, 테이블 구조는 남는다

Oracle은 DELETE 상품 WHERE ...처럼 FROM을 생략할 수 있다.

MERGE

대상 테이블과 원본 테이블을 ON 조건으로 비교해, 맞는 행이 있으면 UPDATE하고 없으면 INSERT한다. Oracle은 WHEN MATCHED THEN UPDATE 뒤에 DELETE WHERE를 붙여, 갱신된 행 가운데 조건에 맞는 행을 지울 수 있다.

실행 전 상품(대상)

코드	이름	가격
P1	연필	500
P2	공책	NULL
P3	지우개	300

입고(원본)

코드	이름	가격
P1	연필	600
P3	지우개	0
P4	자	1000

MERGE INTO 상품 T
USING 입고 S
ON (T.코드 = S.코드)
WHEN MATCHED THEN
  UPDATE SET T.가격 = S.가격
  DELETE WHERE T.가격 = 0
WHEN NOT MATCHED THEN
  INSERT (코드, 이름, 가격) VALUES (S.코드, S.이름, S.가격);
-- 3행 병합됨

실행 후 상품

코드	이름	가격	처리
P1	연필	600	MATCHED → UPDATE
P2	공책	NULL	원본에 없어 변화 없음
P4	자	1000	NOT MATCHED → INSERT

P3은 가격이 0으로 갱신된 뒤 DELETE WHERE 조건에 걸려 삭제되었다. DELETE WHERE는 갱신 후의 값으로 판단하며, 이번 MERGE에서 갱신된 행만 대상이다.
대상 테이블의 같은 행을 한 MERGE 안에서 여러 번 갱신할 수 없다. 원본에 같은 키가 두 번 나와 대상의 한 행을 두 번 갱신하게 되면 ORA-30926 오류가 난다.
ON 절에 쓴 칼럼은 UPDATE SET 절에서 바꿀 수 없다.

DELETE, TRUNCATE, DROP 비교

항목	DELETE	TRUNCATE TABLE	DROP TABLE
분류	DML	DDL	DDL
롤백	가능(COMMIT 전)	Oracle은 불가(자동 커밋). SQL Server는 트랜잭션 안에서 가능	Oracle은 불가(자동 커밋)
테이블 구조	남는다	남는다	없어진다
저장 공간	해제하지 않는다	해제한다(Oracle 기본값 DROP STORAGE)	해제한다
트리거	DELETE 트리거가 동작한다	DELETE 트리거가 동작하지 않는다	해당 없음(트리거도 함께 삭제)
WHERE 절	쓸 수 있다	쓸 수 없다(전체 삭제)	쓸 수 없다
속도	행마다 처리해 느리다	빠르다	빠르다

실습에서 행 단위 AFTER DELETE 트리거를 건 테이블에 DELETE로 1행을 지우면 로그가 1건 남았고, 이어서 TRUNCATE로 나머지 행을 모두 지워도 로그는 늘지 않았다.
Oracle에서는 활성화된 외래 키가 참조하는 부모 테이블을 TRUNCATE할 수 없다(자기 참조는 예외).
Oracle에서 DROP TABLE한 테이블은 휴지통(Recycle Bin, 기본 설정에서 켜져 있음)에 들어가 FLASHBACK TABLE로 되살릴 수 있다. PURGE를 붙이면 바로 지운다. TRUNCATE한 데이터는 FLASHBACK TABLE로도 되살릴 수 없다.

시험 포인트

DELETE는 DML이라 COMMIT 전에 ROLLBACK할 수 있지만, Oracle에서 TRUNCATE와 DROP은 DDL이라 자동 커밋되어 롤백할 수 없다.
TRUNCATE는 테이블 구조는 남기고 데이터와 저장 공간만 정리한다. DROP은 구조까지 없앤다.
INSERT에서 칼럼 목록을 생략하면 모든 칼럼 값을 정의 순서대로 주어야 하고, 칼럼을 지정했을 때 빠진 칼럼은 DEFAULT 또는 NULL이 된다.
MERGE의 WHEN MATCHED는 UPDATE(와 Oracle의 DELETE WHERE), WHEN NOT MATCHED는 INSERT를 수행한다. 결과 테이블의 행 수를 묻는 문제가 나온다.
Oracle 분류에서 SELECT와 MERGE는 DML에 속한다.

같이 보기

참고 문헌