Day 3 - 데이터 조작어 DML (INSERT·UPDATE·DELETE·MERGE)

실습 전에 — 복사본에서 바꾼다

DML 예제는 원본 EMP·DEPT 를 바꾸지 않는다. 블록마다 CREATE TABLE … AS SELECT 로 복사본을 만들고, 끝에서 DROP TABLE … PURGE 로 지운다.

• 그래서 블록마다 첫 줄은 Table created., 마지막 줄은 Table dropped. 이다.
• 출력은 SQL*Plus 의 피드백을 켜고(SET FEEDBACK ON) 받았다. 1 row created., 3 rows updated. 처럼 처리한 행 수가 찍힌다. 문장 사이 빈 줄은 생략했다.
• 복사 문법(CTAS)과 PURGE 는 Day 5 에서 다룬다.

INSERT — 행 넣기

INSERT INTO 테이블 [(열1, 열2, …)] VALUES (값1, 값2, …);
INSERT INTO 테이블 [(열1, 열2, …)] SELECT …;

CREATE TABLE T_DEPT AS SELECT * FROM DEPT;
INSERT INTO T_DEPT (DEPTNO, DNAME, LOC) VALUES (50, 'EDUCATION', 'SEOUL');
INSERT INTO T_DEPT VALUES (60, 'IT', NULL);
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (70, 'HR');
SELECT * FROM T_DEPT ORDER BY DEPTNO;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
1 row created.
1 row created.
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 EDUCATION      SEOUL
        60 IT
        70 HR
7 rows selected.
Table dropped.

• 문자와 날짜 값은 작은따옴표로 감싼다. 숫자는 감싸지 않는다.
• 빠진 열에 NOT NULL 제약이 있고 DEFAULT 가 없으면 ORA-01400(cannot insert NULL) 오류가 난다.

값의 개수와 DEFAULT

열에 DEFAULT 를 정해 두면 값을 주지 않은 INSERT 에 그 값이 들어간다.

CREATE TABLE T_DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) DEFAULT 'SEOUL');
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (50, 'EDUCATION');
INSERT INTO T_DEPT VALUES (60, 'IT', DEFAULT);
INSERT INTO T_DEPT VALUES (70, 'HR', NULL);
INSERT INTO T_DEPT VALUES (80, 'SUPPORT');
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (90, 'SALES', 'BUSAN');
SELECT * FROM T_DEPT ORDER BY DEPTNO;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
1 row created.
1 row created.
INSERT INTO T_DEPT VALUES (80, 'SUPPORT')
            *
ERROR at line 1:
ORA-00947: not enough values
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (90, 'SALES', 'BUSAN')
            *
ERROR at line 1:
ORA-00913: too many values
    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 EDUCATION      SEOUL
        60 IT             SEOUL
        70 HR
3 rows selected.
Table dropped.

• 50 은 LOC 를 빼서, 60 은 DEFAULT 키워드로 기본값 SEOUL 을 받았다.
• 70 은 NULL 을 직접 넣었다. 값을 명시하면 DEFAULT 는 쓰이지 않는다.
• 80 은 열 목록 없이 값 둘만 줘서 ORA-00947(not enough values), 90 은 열 둘에 값 셋을 줘서 ORA-00913(too many values)이다. 오류 난 행은 들어가지 않았다.

INSERT … SELECT

VALUES 대신 SELECT 를 쓰면 조회 결과 전체가 한 번에 들어간다. VALUES 와 괄호를 쓰지 않는다.

CREATE TABLE EMP_T AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE 1 = 2;
INSERT INTO EMP_T SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;
INSERT INTO EMP_T (EMPNO, ENAME) SELECT EMPNO, ENAME FROM EMP WHERE JOB = 'ANALYST';
SELECT * FROM EMP_T ORDER BY EMPNO;
DROP TABLE EMP_T PURGE;

Table created.
3 rows created.
2 rows created.
     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7788 SCOTT
      7839 KING             5000
      7902 FORD
      7934 MILLER           1300
5 rows selected.
Table dropped.

• WHERE 1 = 2 는 항상 거짓이라 구조만 복사하고 행은 0개인 빈 테이블을 만든다.
• 한 문장으로 10번 부서 3행이 들어갔다. 피드백도 3 rows created. 다.
• SELECT 의 열 수와 순서는 INSERT 대상 열과 맞아야 한다. 두 번째 INSERT 는 SAL 을 빼서 SCOTT·FORD 의 SAL 이 NULL 이다.

UPDATE — 값 고치기

UPDATE 테이블 SET 열1 = 값1 [, 열2 = 값2 …] [WHERE 조건];

CREATE TABLE EMP_T AS SELECT * FROM EMP;
UPDATE EMP_T SET SAL = SAL * 1.1, COMM = 100 WHERE DEPTNO = 10;
UPDATE EMP_T SET SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20) WHERE ENAME = 'SMITH';
UPDATE EMP_T SET (JOB, SAL) = (SELECT JOB, SAL FROM EMP WHERE ENAME = 'SCOTT') WHERE ENAME = 'ADAMS';
UPDATE EMP_T SET COMM = 0 WHERE ENAME = 'NOBODY';
SELECT ENAME, JOB, SAL, COMM FROM EMP_T WHERE DEPTNO IN (10, 20) ORDER BY DEPTNO, ENAME;
UPDATE EMP_T SET COMM = NULL;
DROP TABLE EMP_T PURGE;

Table created.
3 rows updated.
1 row updated.
1 row updated.
0 rows updated.
ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
CLARK      MANAGER         2695        100
KING       PRESIDENT       5500        100
MILLER     CLERK           1430        100
ADAMS      ANALYST         3000
FORD       ANALYST         3000
JONES      MANAGER         2975
SCOTT      ANALYST         3000
SMITH      CLERK           3000
8 rows selected.
14 rows updated.
Table dropped.

• 여러 열은 SET 뒤에 쉼표로 잇는다. SAL = SAL * 1.1 의 오른쪽 SAL 은 바꾸기 전 값이다.
• 스칼라 서브쿼리로 값을 정할 수 있다. SMITH 의 급여가 20번 부서 최고 급여 3000 이 됐다.
• SET (JOB, SAL) = (SELECT JOB, SAL …) 처럼 괄호로 묶으면 서브쿼리 한 번으로 여러 열을 바꾼다.
• 맞는 행이 없으면 오류가 아니라 0 rows updated. 다.
• WHERE 를 빼면 모든 행이 바뀐다. 마지막 UPDATE 는 14행 전부의 COMM 을 NULL 로 만들었다.

DELETE · TRUNCATE · DROP

DELETE FROM 테이블 [WHERE 조건]; 은 조건에 맞는 행을 지운다. Oracle 에서는 FROM 을 생략해도 된다.

CREATE TABLE EMP_T AS SELECT * FROM EMP;
DELETE FROM EMP_T WHERE DEPTNO = 30;
SELECT COUNT(*) FROM EMP_T;
ROLLBACK;
SELECT COUNT(*) FROM EMP_T;
DELETE EMP_T;
TRUNCATE TABLE EMP_T;
ROLLBACK;
SELECT COUNT(*) FROM EMP_T;
DROP TABLE EMP_T PURGE;
SELECT COUNT(*) FROM EMP_T;

Table created.
6 rows deleted.
  COUNT(*)
----------
         8
1 row selected.
Rollback complete.
  COUNT(*)
----------
        14
1 row selected.
14 rows deleted.
Table truncated.
Rollback complete.
  COUNT(*)
----------
         0
1 row selected.
Table dropped.
SELECT COUNT(*) FROM EMP_T
                     *
ERROR at line 1:
ORA-00942: table or view "SCOTT"."EMP_T" does not exist

• DELETE 로 지운 6행은 ROLLBACK 으로 돌아왔다. 아직 COMMIT 하지 않은 변경이기 때문이다.
• TRUNCATE 는 DDL 이다. 실행하는 순간 앞의 DELETE EMP_T 까지 함께 확정되어 ROLLBACK 해도 0행이다.
• DROP 뒤에는 테이블 자체가 없어 ORA-00942 다. 오류 문구의 "SCOTT"."EMP_T" 표기는 23ai 형식이고, 이전 버전은 table or view does not exist 만 보여 준다.

• SQL Server 에서는 BEGIN TRAN 안에서 실행한 TRUNCATE 를 ROLLBACK 으로 되돌릴 수 있다. 위 표의 "불가"는 Oracle 기준이다.

⚠️ 함정: "모든 행을 지우되 되돌릴 수 있어야 한다"면 TRUNCATE 가 아니라 WHERE 없는 DELETE 다. "구조는 남기고 빠르게 비운다"면 TRUNCATE, "테이블이 필요 없다"면 DROP 이다.

MERGE — 있으면 고치고 없으면 넣기

원본 데이터를 대상 테이블에 반영할 때, 이미 있는 행은 UPDATE, 없는 행은 INSERT 해야 할 때가 있다. 두 문장을 나눠 쓰는 대신 MERGE 한 문장으로 처리한다.

MERGE INTO 대상 테이블 별칭
USING 원본(테이블·뷰·서브쿼리) 별칭
ON (대상과 원본을 잇는 조건)
WHEN MATCHED THEN
  UPDATE SET 열 = 값 [, …]
WHEN NOT MATCHED THEN
  INSERT (열, …) VALUES (값, …);

T_BONUS 에는 10번 부서 CLARK·KING·MILLER 가 보너스 0 으로 있다. MANAGER·PRESIDENT 4명(JONES·BLAKE·CLARK·KING)을 원본으로 MERGE 한다.

CREATE TABLE T_BONUS AS SELECT EMPNO, ENAME, SAL AS BONUS FROM EMP WHERE DEPTNO = 10;
UPDATE T_BONUS SET BONUS = 0;
MERGE INTO T_BONUS B USING (SELECT EMPNO, ENAME, SAL FROM EMP WHERE JOB IN ('MANAGER', 'PRESIDENT')) E ON (B.EMPNO = E.EMPNO) WHEN MATCHED THEN UPDATE SET B.BONUS = E.SAL * 0.1 WHEN NOT MATCHED THEN INSERT (EMPNO, ENAME, BONUS) VALUES (E.EMPNO, E.ENAME, E.SAL * 0.05);
SELECT * FROM T_BONUS ORDER BY EMPNO;
DROP TABLE T_BONUS PURGE;

Table created.
3 rows updated.
4 rows merged.
     EMPNO ENAME           BONUS
---------- ---------- ----------
      7566 JONES          148.75
      7698 BLAKE           142.5
      7782 CLARK             245
      7839 KING              500
      7934 MILLER              0
5 rows selected.
Table dropped.

• 피드백 4 rows merged. 는 UPDATE 2행과 INSERT 2행을 합친 수다.
• MERGE 는 원본 기준으로 돈다. 대상에만 있는 MILLER 는 지워지지도 바뀌지도 않는다.
• UPDATE·INSERT 문장 안에는 테이블 이름을 다시 쓰지 않는다. UPDATE SET …, INSERT (…) VALUES (…) 로 바로 이어진다.

MERGE 의 규칙

CREATE TABLE T_BONUS AS SELECT EMPNO, ENAME, SAL AS BONUS FROM EMP WHERE DEPTNO = 10;
MERGE INTO T_BONUS B USING (SELECT EMPNO, SAL FROM EMP WHERE DEPTNO = 10) E ON (B.EMPNO = E.EMPNO) WHEN MATCHED THEN UPDATE SET B.EMPNO = E.EMPNO;
MERGE INTO T_BONUS B USING (SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 20) E ON (B.EMPNO = E.EMPNO) WHEN NOT MATCHED THEN INSERT (EMPNO, ENAME, BONUS) VALUES (E.EMPNO, E.ENAME, 0);
SELECT COUNT(*) FROM T_BONUS;
DROP TABLE T_BONUS PURGE;

Table created.
MERGE INTO T_BONUS B USING (SELECT EMPNO, SAL FROM EMP WHERE DEPTNO = 10) E ON (B.EMPNO = E.EMPNO) WHEN MATCHED THEN UPDATE SET B.EMPNO = E.EMPNO
                                                                                *
ERROR at line 1:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "B"."EMPNO"
5 rows merged.
  COUNT(*)
----------
         8
1 row selected.
Table dropped.

• ON 절에 쓴 열은 UPDATE SET 에서 바꿀 수 없다(ORA-38104). 연결 기준이 바뀌면 매칭이 흔들리기 때문이다.
• WHEN MATCHED 와 WHEN NOT MATCHED 중 하나만 써도 된다. 두 번째 MERGE 는 INSERT 절만 써서 20번 부서 5명을 넣었다. Oracle 9i 에서는 둘 다 필요했고 10g 부터 하나만 쓸 수 있다.
• 그래서 COUNT 는 기존 3행에 5행을 더한 8 이다.
• 10g 부터 UPDATE SET … WHERE 조건, DELETE WHERE 조건 을 MATCHED 절에 덧붙일 수 있다.
• 원본 두 행이 대상 한 행과 맞으면 같은 행을 두 번 고치게 되어 ORA-30926 이 난다. 원본은 ON 열 기준으로 한 행씩이어야 한다.

내일은 DML 의 변경을 확정하거나 되돌리는 TCL(COMMIT·ROLLBACK·SAVEPOINT)과 트랜잭션을 다룬다.

📖 용어

• DML : 행의 내용을 바꾸는 문장. INSERT·UPDATE·DELETE·MERGE 가 있다.
• 열 목록 : INSERT 에서 테이블 이름 뒤 괄호에 적는 열 이름들. 값을 넣을 열과 순서를 정한다.
• DEFAULT : 값을 주지 않은 열에 대신 들어갈 기본값. VALUES 안에 키워드로 직접 쓸 수도 있다.
• INSERT … SELECT : 조회 결과의 모든 행을 한 문장으로 넣는 INSERT. VALUES 를 쓰지 않는다.
• TRUNCATE : 테이블의 모든 행을 지우고 공간을 돌려주는 DDL. 구조는 남고 ROLLBACK 은 안 된다.
• MERGE : 원본과 대상을 ON 조건으로 비교해 맞으면 UPDATE, 없으면 INSERT 하는 문장.
• ORA-00947 / ORA-00913 : INSERT 의 값이 열보다 모자랄 때 / 남을 때의 오류.