DML

DML(Data Manipulation Language, 데이터 조작어)은 테이블의 행을 넣고(INSERT), 바꾸고(UPDATE), 지웁니다(DELETE). MERGE는 짝이 있는 행은 바꾸고 없는 행은 넣습니다. 테이블의 구조는 그대로 둡니다.

신입 사원이 들어오면 행을 넣고, 월급이 오르면 값을 바꾸고, 퇴사하면 행을 지웁니다. 행을 읽는 SELECT도 DML로 묶지만, 이 레슨은 행을 바꾸는 네 문장을 봅니다. 그림의 문장은 한 세션(session — 데이터베이스에 접속해 있는 연결 하나)에서 차례로 실행해, 단계마다 앞 단계의 결과에서 이어집니다.

MERGE INTO EMP EUSING (…) SON (E.EMPNO = S.EMPNO)WHEN MATCHED THEN  UPDATE SET E.SAL = S.SALWHEN NOT MATCHED THEN  INSERT (EMPNO, ENAME,    SAL, DEPTNO)  VALUES (S.EMPNO, S.ENAME,    S.SAL, S.DEPTNO);

SELECT EMPNO,  ENAME,  SAL,  DEPTNOFROM EMP;

1INSERT로 행 넣기

열 목록과 VALUES의 값은 적은 순서대로 짝을 짓고, 목록의 순서는 테이블의 열 순서와 달라도 됩니다. 목록에서 뺀 열에는 NULL이 들어가는데, 기본값(DEFAULT)을 정해 둔 열이면 그 값이 들어갑니다. 기본값이 없는 NOT NULL 열을 빼면 ORA-01400 오류로 막힙니다. 열 목록을 아예 빼면 모든 열의 값을 테이블의 열 순서대로 줘야 하고, 값이 모자라면 ORA-00947 오류입니다. VALUES 대신 서브쿼리를 쓰면(INSERT INTO 테이블 SELECT …) 그 결과 행을 한 번에 넣습니다.

2UPDATE와 DELETE

UPDATE는 SET에 바꿀 열과 새 값을 쓰고, 새 값에는 SAL + 50처럼 지금 값을 쓴 식도 됩니다. DELETE는 행을 통째로 지워서 열 이름을 쓰지 않고, DELETE EMP WHERE …처럼 FROM을 빼도 됩니다(Oracle·SQL Server 모두). 두 문장 모두 WHERE로 행을 고르고, 조건은 SELECT의 WHERE와 같은 규칙으로 따집니다.

행을 모두 지우는 TRUNCATE, 테이블을 없애는 DROP과 DELETE의 차이는 DDL 레슨에서 봅니다.

3MERGE로 바꾸거나 넣기

MERGE INTO 뒤에 바꿀 대상을, USING 뒤에 원본을 쓰고, ON 조건으로 원본 행마다 대상에서 짝을 찾습니다. Oracle은 ON 조건을 괄호로 감싸야 합니다(빼면 ORA-00969).

두 절 가운데 하나만 써도 되고, 원본에 없는 대상의 행은 건드리지 않습니다. ON에 쓴 열은 짝을 찾는 기준이라 UPDATE SET에서 바꿀 수 없습니다(ORA-38104). 여기까지 바꾼 행은 아직 확정 전이라 ROLLBACK으로 되돌릴 수 있습니다(TCL 레슨에서 자세히).

예제

예제는 저마다 처음 데이터에서 시작하고, 끝의 SELECT로 바뀐 결과를 봅니다. 그림의 INSERT에서 목록에 없는 MGR도 NULL입니다.

INSERT INTO EMP
  (EMPNO, ENAME, SAL)
VALUES (1007, '윤서아', 360);
SELECT *
FROM EMP
WHERE EMPNO = 1007;

열 목록을 빼면 DEPT의 열 순서(DEPTNO, DNAME, LOC)대로 값을 씁니다.

INSERT INTO DEPT
VALUES (50, '총무', '대전');
SELECT *
FROM DEPT
WHERE DEPTNO = 50;

그림의 UPDATE와 DELETE를 처음 데이터에 차례로 실행합니다.

UPDATE EMP
SET SAL = SAL + 50
WHERE DEPTNO = 20;
DELETE FROM EMP
WHERE SAL < 350;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
ORDER BY EMPNO;

그림의 MERGE입니다. 원본 S는 두 SELECT를 UNION ALL로 이어 만든 두 행짜리 표입니다.

MERGE INTO EMP E
USING (
  SELECT 1004 AS EMPNO,
    '최유나' AS ENAME,
    480 AS SAL,
    30 AS DEPTNO
  FROM DUAL
  UNION ALL
  SELECT 1008,
    '임도현',
    390,
    30
  FROM DUAL
) S
ON (E.EMPNO = S.EMPNO)
WHEN MATCHED THEN
  UPDATE SET E.SAL = S.SAL
WHEN NOT MATCHED THEN
  INSERT (EMPNO, ENAME,
    SAL, DEPTNO)
  VALUES (S.EMPNO, S.ENAME,
    S.SAL, S.DEPTNO);
SELECT EMPNO,
  ENAME,
  SAL,
  DEPTNO
FROM EMP
WHERE DEPTNO = 30
ORDER BY EMPNO;

시험 포인트

• INSERT에서 열 목록을 빼면 모든 열의 값을 테이블의 열 순서대로 줘야 합니다. 모자라면 ORA-00947입니다.
• 열 목록에서 뺀 열은 NULL이나 기본값(DEFAULT)이 되고, 기본값이 없는 NOT NULL 열을 빼면 ORA-01400입니다.
• UPDATE·DELETE에 WHERE가 없으면 모든 행이 바뀌거나 지워집니다. WHERE 열 = NULL은 오류 없이 한 행도 고르지 않습니다.
• DELETE는 FROM을 빼도 됩니다(Oracle·SQL Server 모두).
• MERGE는 WHEN MATCHED·WHEN NOT MATCHED 가운데 하나만 써도 되고, ON에 쓴 열은 UPDATE SET에서 바꿀 수 없습니다.

확인 문제

1. DEPT에 다음 INSERT 셋을 차례로 실행합니다. DNAME은 NOT NULL입니다. 마지막 SELECT의 결과는 무엇이고, 막히는 문장은 왜 막힐까요?

INSERT INTO DEPT
  (DNAME, DEPTNO)
VALUES ('홍보', 60);
INSERT INTO DEPT
VALUES (70, '법무');
INSERT INTO DEPT
  (DEPTNO, LOC)
VALUES (80, '대구');
SELECT *
FROM DEPT
WHERE DEPTNO >= 50;

2. 다음 두 문장을 차례로 실행하면 EMP의 행 수와 SAL 합계는 얼마일까요? 처음 합계는 2530입니다.

UPDATE EMP
SET SAL = SAL + 50;
DELETE FROM EMP
WHERE DEPTNO = NULL;
SELECT COUNT(*) AS CNT,
  SUM(SAL) AS TOTAL
FROM EMP;

3. WHEN NOT MATCHED를 쓰지 않은 다음 MERGE를 실행하면, EMP의 행 수와 SAL 합계는 얼마가 될까요? 1009는 왜 들어가지 않을까요?

MERGE INTO EMP E
USING (
  SELECT 1001 AS EMPNO,
    '김민준' AS ENAME,
    550 AS SAL,
    10 AS DEPTNO
  FROM DUAL
  UNION ALL
  SELECT 1009,
    '한유진',
    330,
    40
  FROM DUAL
) S
ON (E.EMPNO = S.EMPNO)
WHEN MATCHED THEN
  UPDATE SET E.SAL = S.SAL;
SELECT COUNT(*) AS CNT,
  SUM(SAL) AS TOTAL
FROM EMP;