[Oracle/SQL] 7. DML | INSERT | UPDATE | DELETE | MERGE

목차

1. DML
2. INSERT
   2-1. INSERT INTO~VALUES
   2-2. INSERT~서브쿼리
   2-3. INSERT ALL
3. UPDATE
   3-1. UPDATE~SET~WHERE
   3-2. UPDATE~서브쿼리
   3-3. 제약 조건에 위배되지 않아야 UPDATE
4. DELETE
   4-1. DELETE FROM~WHERE
   4-2. 제약 조건에 위배되지 않아야 DELETE
   4-3. DELETE(DML)-TRUNCATE(DDL) 비교
5. MERGE
6. 사용 예제

---

학습점검

✅ DML의 사용 목적에 대해 이해할 수 있다.

DML(Data Manipulation Language)는 데이터 조작 언어로, 테이블 행에 데이터를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는 작업을 일컫는다. 여기에 SELECT까지 포함시키곤 한다.

✅ INSERT문을 이용하여 테이블에 데이터를 삽입할 수 있다.

INSERT INTO 테이블명 (컬럼명, 컬럼명...) VALUES (데이터, 데이터...) 기본식은 이렇게 쓸 수 있다. 그밖에도 VALUES 자리에 서브쿼리를 이용한 작성이 가능하다.

✅ INSERT ALL을 이용하여 여러 테이블에 데이터를 삽입할 수 있다.

INSERT ALL은 서브쿼리 조건절이 같은 경우에 한하여 데이터를 한꺼번에 삽입할 수 있게 만든다. / INSERT ALL INTO VALUES (컬럼명...) INTO VALUES (컬럼명...) 서브쿼리 처럼 작성할 수 있다. / INSERT ALL WHEN THEN INTO 테이블명 VALUES (컬럼명...) WHEN THEN INTO 테이블명 VALUES(컬럼명...) 서브쿼리 처럼 쓰일 수도 있다.

✅ MERGE를 이용하여 두 개의 테이블을 병합할 수 있다.

MERGE INTO 병합될테이블명 USING 병합할테이블명 ON (컬럼명 = 컬럼명) WHEN MATCHED THEN UPDATE SET... WHEN NOT MATCHED INSERT () VALUES ()

✅ UPDATE문을 이용하여 테이블의 데이터를 갱신할 수 있다.

✅ INSERT, UPDATE시 서브쿼리를 이용할 수 있다.

✅ DELETE문을 이용하여 테이블의 데이터를 삭제할 수 있다.

✅ TRUNCATE를 이용하여 테이블 데이터를 절삭할 수 있다.

 

---

 

1. DML

❗ DML(Data Manipulation Language) : 데이터 조작 언어
테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하거나 조회(SELECT)하는 구문이다.

 

• DML(INSERT-UPDATE-DELETE) 구문들은 항상 트랜잭션(transaction) 단위로 수행된다.
• 따라서 TCL(Transaction Control Language)인 COMMIT/ROLLBACK 작업을 필요로 한다.
• DML 구문에서는 COMMIT이 수행되어야 다른 프로그램에서도 데이터 조회할 수가 있다. INSERT, UPDATE 등 작업만 하고 COMMIT을 작성하지 않으면 외부에서 이 데이터를 읽지 못한다.
• 반면 구조를 다루는 DDL(CREATE-ALTER-DROP) 구문은 AUTO COMMIT이라고 여길 수 있다.

 

---

 

2. INSERT

• 새로운 행을 추가하는 구문이다.
• INSERT가 실행되면, 테이블 행 개수가 증가한다.

-- 테이블의 일부 컬럼을 INSERT할 때
✅ INSERT INTO 테이블명 (컬럼명, 컬럼명...) VALUES (데이터, 데이터...);
-- 테이블의 모든 컬럼을 INSERT할 때 *컬럼명 생략 가능*
✅ INSERT INTO 테이블명 VALUES (데이터, 데이터...);

• 테이블의 모든 컬럼을 INSERT할 때, 데이터는 해당 컬럼의 순서 및 개수에 맞춰 작성되어야 한다. 컬럼 수는 10개인데 9개 또는 11개의 값이 입력될 수는 없다.
• 또, 모든 컬럼을 INSERT할 때에도 컬럼명을 기술하는 것이 의미 파악에 더 좋다. 테이블 구조를 별도로 확인해야 하는 번거로움이 따르기 때문이다.

 

2-1. INSERT INTO~VALUES

• INSERT 구문 기본 표현식이다.

INSERT
  INTO EMPLOYEE E
(
  E.EMP_ID, E.EMP_NAME, E.EMP_NO, E.EMAIL, E.PHONE
, E.DEPT_CODE, E.JOB_CODE, E.SAL_LEVEL, E.SALARY, E.BONUS
, E.MANAGER_ID, E.HIRE_DATE, E.ENT_DATE, E.ENT_YN
)
VALUES
(
  900, '김디비', '991231-1234567', 'kimdb@reminder.com', '01000010001'
, 'D1', 'J7', 'S3', 4300000, 0.2
, '200', SYSDATE, NULL, DEFAULT
);

• 모든 컬럼에 대해 데이터를 추가한 예시이다.
• ENT_DATE 컬럼이 NULLABLE하므로 NULL값 설정할 수 있다.
• ENT_YN 컬럼 DEFAULT 값이 'N'으로 설정돼 있으므로 DEFAULT라고 작성할 수 있다.

 

2-2. INSERT~서브쿼리

• VALUES 대신 서브쿼리를 이용할 수 있다.

INSERT
  INTO EMP_01 A -- 별칭
(
  A.EMP_ID
, A.EMP_NAME
, A.DEPT_TITLE
)
(
  SELECT E.EMP_ID
　　　  , E.EMP_NAME
　　　  , D.DEPT_TITLE
　FROM EMPLOYEE E
　LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
);

• 이때 VALUES 키워드는 포함되지 않고, 데이터를 나열하는 자리에 서브쿼리문을 쓴다.

 

2-3. INSERT ALL

• INSERT 시에 사용되는 서브쿼리가 같은 경우 두 개 이상의 테이블에 INSERT ALL을 이용해 한 번에 데이터를 삽입할 수 있다.
• 단, 이때 각 서브쿼리의 조건절이 같아야 한다.

❗ WHERE 1 = 0;
WHERE절을 무조건 FALSE로 만드는 조건이다. 서브쿼리를 이용한 CREATE 구문에서 위와 같은 조건을 명시하면 결과적으로 컬럼명, 컬럼의 데이터 타입만 복사된다.
데이터는 하나도 가져오지 않는다.

CREATE TABLE EMP_DEPT_D1
AS
SELECT E.EMP_ID
　　　, EMP_NAME
　　　, E.DEPT_CODE
　　　, E.HIRE_DATE
 FROM EMPLOYEE E
WHERE 1 = 0;

 

INSERT ALL
  INTO EMP_DEPT_D1 -- 첫 번째 테이블
VALUES
(
  EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
)
  INTO EMP_MANAGER -- 두 번째 테이블
VALUES
(
  EMP_ID
, EMP_NAME
, MANAGER_ID
)
SELECT E.EMP_ID -- 첫 번째, 두 번째 테이블에서 사용되는 모든 컬럼 작성
　　　, E.EMP_NAME
　　　, E.DEPT_CODE
　　　, E.HIRE_DATE
　　　, E.MANAGER_ID
 FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1';

• SELECT절에서는 사용되는 모든 컬럼을 나열한다.
• 결과적으로 첫 번째 테이블에 4개, 두 번째 테이블에 4개 행이 추가돼 8개가 삽입되었다고 안내된다.

INSERT ALL
  WHEN HIRE_DATE < '2000/01/01'
  THEN 
  INTO EMP_OLD
VALUES
(
  EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
  WHEN HIRE_DATE >= '2000/01/01'
  THEN
  INTO EMP_NEW
VALUES
(
  EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.HIRE_DATE
　　　, E.SALARY
 FROM EMPLOYEE E;

• 입사일 기준으로 2000년 1월 1일 이전 입사자는 EMP_OLD 테이블에, 반대는 EMP_NEW 테이블에 반영하는 예시이다.
• 이때 결과로서 24개가 삽입되었다는 안내 메시지가 나온다. OLD 테이블에 8행, NEW 테이블에 16행이 추가된 것이다.

 

---

 

3. UPDATE

• 테이블에 기록된 컬럼값을 수정하는 구문이다.
• 테이블 전체 행 개수는 변화가 없다.

✅ UPDATE 테이블명 SET 컬럼명 = 바꿀값, 컬럼명 = 바꿀값, ... WHERE 컬럼명 비교연산자 비교값;

WHERE절 생략 가능하나, WHERE절 조건이 명시되지 않으면 모든 행에 대해 UPDATE가 치러진다.

 

3-1. UPDATE~SET~WHERE

UPDATE
　　　  DEPT_COPY DC
     SET DC.DEPT_TITLE = '전략기획팀'
WHERE DC.DEPT_ID = 'D9';

 

3-2. UPDATE~서브쿼리

단일행 서브쿼리 SET

✅ UPDATE 테이블명 SET 컬럼명 = (서브쿼리) WHERE 조건;

UPDATE
　　　  EMP_SALARY ES
     SET ES.SALARY = (SELECT E.SALARY
　　　  　　　  　　　 FROM EMPLOYEE E
　　　  　　　  　     WHERE E.EMP_NAME = '박맑음'
　　　  　　　  　　 )
        , ES.BONUS = (SELECT E.SALARY -- SET절에 여러 개 나열 가능
　　　  　　　  　　  FROM EMPLOYEE E
　　　  　　　  　   WHERE E.EMP_NAME = '박맑음'
　　　  　　　  　   )
WHERE ES.EMP_NAME = '김흐림';

• SET절에 쉼표(,)로 구분해 여러 개 나열 가능하다.

다중열 서브쿼리 SET()

✅ UPDATE 테이블명 SET (컬럼명, 컬럼명) = (서브쿼리) WHERE 조건;

UPDATE
　　　  EMP_SALARY ES
     SET (ES.SALARY, ES.BONUS) = (SELECT E.SALARY
　　　  　　　  　　　  　　　  　　　   , E.BONUS
　　　  　　　  　　　  　　　      FROM EMPLOYEE E
　　　  　　　  　　　  　　　    WHERE E.EMP_NAME = '박맑음'
　　　  　　　  　　　  　　　     )
WHERE ES.EMP_NAME IN ('오천둥', '연안개', '소소낙');

• SET절에 소괄호 ()와 함께 컬럼명을 나열해 다중열 적용할 수 있다.

다중행 서브쿼리 WHERE

✅ UPDATE 테이블명 SET 컬럼명 = 바꿀값 WHERE 컬럼명 비교연산자 (서브쿼리)

UPDATE
　　　  EMP_SALARY ES
     SET ES.BONUS = 0.5
WHERE ES.EMP_ID IN (SELECT E.EMP_ID
　　　  　　　  　　　 FROM EMPLOYEE E
　　　  　　　  　　　  JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
　　　  　　　  　　　  JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
　　　  　　　  　　 WHERE L.LOCAL_NAME LIKE 'ASIA%'
　　　  　　　  　　 );

 

3-3. 제약 조건에 위배되지 않아야 UPDATE

❗ UPDATE시 변경 값은 해당 컬럼에 대한 제약 조건에 위배되지 않아야 한다.
즉 제약 조건에 위배되지 않는 선에서 UPDATE 할 수 있다.

-- NOT NULL 제약 조건 위배
SET E.EMP_NAME = NULL
NULL로 ("C##EMPLOYEE"."EMPLOYEE"."EMP_NAME")을 업데이트할 수 없습니다

-- UNIQUE 제약 조건 위배됨
SET EMP_NO = '(이미 존재하는 값)'
무결성 제약 조건(C##EMPLOYEE.SYS_C008775)에 위배됩니다

-- FOREIGN KEY 제약 조건 위배
SET E.DEPT_CODE = '65'
무결성 제약조건(C##EMPLOYEE.SYS_C008769)이 위배되었습니다- 부모 키가 없습니다

• REFERENCES 하고 있는 PRIMARY KEY 컬럼에 '65'라는 데이터가 존재하지 않기에 FOREIGN KEY로서도 사용할 수 없다는 뜻이다. 

 

---

 

4. DELETE

• 테이블 행을 삭제하는 구문이다.
• DELETE 실행하면 테이블 행의 개수가 줄어든다.

✅ DELETE FROM 테이블명 WHERE 조건

UPDATE와 마찬가지로, DELETE 구문에서 WEHRE절 조건을 설정하지 않으면 모든 행에 대해 DELETE 적용된다.

 

4-1. DELETE FROM~WHERE

DELETE
  FROM EMPLOYEE E
WHERE E.EMP_NAME = '정구름';

 

4-2. 제약 조건에 위배되지 않아야 DELETE

-- FOREIGN KEY 제약 조건 위배
DELETE
  FROM DEPARTMENT D
WHERE D.DEPT_ID = 'D1';
무결성 제약조건(C##EMPLOYEE.SYS_C008769)이 위배되었습니다- 자식 레코드가 발견되었습니다

• 특히 FOREIGN KEY 제약 조건이 설정돼 있는 경우, 참조되고 있는 값에 대해서는 삭제할 수 없다.

WHERE D.DEPT_ID = 'D3';

• 단, FOREIGN KEY 제약 조건이 설정되어 있어도 실상 참조되고 있지 않는 값에 대해서는 삭제가 가능하다. 그 값을 참조하고 있는 자식 레코드가 없을 때 삭제할 수 있다는 뜻이다.

-- 제약 조건 비활성화
ALTER TABLE EMPLOYEE
DISABLE CONSTRAINT FK_DEPT_ID CASCADE;
-- 제약 조건 활성화
ALTER TABLE EMPLOYEE
ENABLE CONSTRAINT FK_DEPT_ID CASCADE;

• 필요에 따라 제약 조건을 비활성화 할 수 있다.

 

4-3. DELETE(DML)-TRUNCATE(DDL) 비교

• TRUNCATE는 DDL(데이터 정의 언어, Data Definition Language)에 속하는 키워드이다. DELETE는 DML...
• ROLLBACK을 통한 복구가 불가하다. DML은 COMMIT, ROLLBACK과 같은 TCL 사용을 겸하는 것이 가능하지만, TRUNCATE는 구조적인 데 대한 생성/수정/삭제를 다루는 DDL로서 TCL 사용 불가하다.
• 테이블의 전체 행 삭제를 위해 사용된다.
• 수행 속도 면에서는 TRUNCATE > DELETE이다. TRUNCATE가 더 빠르다.

TRUNCATE TABLE EMP_SALARY;
Table EMP_SALARY이(가) 잘렸습니다.

• TRUNCATE는 안내 문구조차 '잘렸다'고 출력된다. 가위로 자르듯 모든 데이터가 잘려나가고 컬럼 헤더 같은 테이블 구조만 남아있는 모습을 확인할 수 있다.
• 주의할 것은 ROLLBACK을 통한 복구가 불가하는 것이다!

 

---

 

5. MERGE

• 구조가 같은 두 개의 테이블을 하나로 병합하는 기능을 수행한다.
• 테이블에서 지정하는 조건의 값이 존재하면 WHEN MATCHED THEN UPDATE
• 조건의 값이 없으면 WHEN NOT MATCHED THEN INSERT 처리한다.

MERGE
  INTO EMP_M01 M1
USING EMP_M02 M2
    ON (M1.EMP_ID = M2.EMP_ID)
WHEN MATCHED THEN
UPDATE
     SET M1.EMP_NAME = M2.EMP_NAME
　　　 , M1.EMP_NO = M2.EMP_NO
　　　 , M1.EMAIL = M2.EMAIL
　　　 , M1.PHONE = M2.PHONE
　　　 , M1.DEPT_CODE = M2.DEPT_CODE
　　　 , M1.JOB_CODE = M2.JOB_CODE
　　　 , M1.SAL_LEVEL = M2.SAL_LEVEL
　　　 , M1.SALARY = M2.SALARY
　　　 , M1.BONUS = M2.BONUS
　　　 , M1.MANAGER_ID = M2.MANAGER_ID
　　　 , M1.HIRE_DATE = M2.HIRE_DATE
　　　 , M1.ENT_DATE = M2.ENT_DATE
　　　 , M1.ENT_YN = M2.ENT_YN
WHEN NOT MATCHED THEN
INSERT
(
  M1.EMP_ID, M1.EMP_NAME, M1.EMP_NO, M1.EMAIL, M1.PHONE
, M1.DEPT_CODE, M1.JOB_CODE, M1.SAL_LEVEL, M1.SALARY, M1.BONUS
, M1.MANAGER_ID, M1.HIRE_DATE, M1.ENT_DATE, M1.ENT_YN
)
VALUES
(
  M2.EMP_ID, M2.EMP_NAME, M2.EMP_NO, M2.EMAIL, M2.PHONE
, M2.DEPT_CODE, M2.JOB_CODE, M2.SAL_LEVEL, M2.SALARY, M2.BONUS
, M2.MANAGER_ID, M2.HIRE_DATE, M2.ENT_DATE, M2.ENT_YN
);

• EMP_M02 테이블에서 J4 소속 직원들의 M2.SALARY 값이 모두 0이라고 가정한다.
• 이때 MERGE 결과로 EMP_M01 테이블을 조회하면, J4 직원들의 SALARY가 0으로 병합돼 있음을 확인할 수 있다.

 

---

 

6. 사용 예제

▶WHERE 1 = 0; 명시해야 데이터 복사 없이 컬럼명과 컬럼의 데이터 타입만 가져올 수 있다.

-- EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을 ❎✅
-- 조회해서 사번, 이름, 소속부서, 입사일을 삽입하고,
-- EMP_MANAGER 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
-- 조회해서 사번, 이름, 관리자 사번을 삽입
-- 단, 서브쿼리 이용해 CREATE TABLE 하고, INSERT ALL 구문으로 작성한다.
CREATE TABLE EMP_DEPT_D1
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.DEPT_CODE
　　　, E.HIRE_DATE
 FROMEMPLOYEE E; -- FROM절에서 끝나면 안 되고, 밑에 WHERE절 명시해야 한다.
 FROM EMPLOYEE E
WHERE 1 = 0; -- 컬럼명, 컬럼의 데이터 타입만 복사! 데이터 X!

CREATE TABLE EMP_MANAGER
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.MANAGER_ID
 FROM EMPLOYEE E
WHERE 1 = 0;

INSERT ALL
  INTO EMP_DEPT_D1
VALUES
(
  EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
)
  INTO EMP_MANAGER
VALUES
(
  EMP_ID
, EMP_NAME
, MANAGER_ID
)
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.DEPT_CODE
　　　, E.HIRE_DATE
　　　, E.MANAGER_ID
 FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1';

 

▶INSERT ALL 예제이다. 서브쿼리 조건절이 같아야만 사용할 수 있는 기능으로, 한 번에 데이터 삽입을 가능케 한다.

-- EMPLOYEE 테이블에서 입사일 기준으로 2000년 1월 1일 이전에 입사한 ✅
-- 사원의 사번, 이름, 입사일, 급여를 조회하여
-- EMP_OLD 테이블에 삽입하고
-- 그 이후에 입사한 사원은 EMP_NEW 테이블에 삽입
CREATE TABLE EMP_OLD
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.HIRE_DATE
　　　, E.SALARY
 FROM EMPLOYEE E
WHERE 1 = 0;

CREATE TABLE EMP_NEW
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.HIRE_DATE
　　　, E.SALARY
 FROM EMPLOYEE E
WHERE 1 = 0;

INSERT ALL
  WHEN HIRE_DATE < '2000/01/01'
  THEN
  INTO EMP_OLD
VALUES
(
 EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
  WHEN HIRE_DATE >= '2000/01/01' -- 해당 날짜에 입사했을 수 있으니 비교연산자는 >=
  THEN
  INTO EMP_NEW
VALUES
(
 EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.HIRE_DATE
　　　, E.SALARY
 FROM EMPLOYEE E;

 

▶서브쿼리 이용한 UPDATE문 작성 예제이다. SET절에 조건을 여러 개 나열할 수 있는데, 이때 키워드를 다시 쓰지 않고 쉼표(,)로 구분한다.

-- 김흐림 사원의 급여와 보너스율을 박맑음 사원과 동일하게 변경해 주기로 했다.
-- EMP_SALARY 테이블에 이를 반영하는 UPDATE문을 작성 ✅
UPDATE
　　　  EMP_SALARY ES
     SET ES.SALARY = (SELECT E1.SALARY
　　　　　　　　　　  FROM EMPLOYEE E1
　　　　　　　　　　WHERE E1.EMP_NAME = '박맑음'
　　　　　　　　　　)
         , ES.BONUS = (SELECT E2.BONUS -- SET 키워드 다시 쓰지 않고 쉼표(,)로 구분
　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　　WHERE E2.EMP_NAME = '박맑음'
　　　　　　　　　　)
WHERE ES.EMP_NAME = '김흐림';

 

-- 오천둥, 연안개, 소소낙 사원의 급여와 보너스를
-- 박맑음 사원의 급여와 보너스와 같게 변경하는 UPDATE문 작성 ✅
UPDATE
　　　  EMP_SALARY ES
     SET (ES.SALARY, ES.BONUS) = (SELECT E.SALARY, E.BONUS
　　　　　　　　　　　　　　  　  FROM EMPLOYEE E
　　　　　　　　　　　　　　　  WHERE E.EMP_NAME = '박맑음'
　　　　　　　　　　　　　　　　)
WHERE ES.EMP_NAME IN ('오천둥', '연안개', '소소낙');

 

-- EMP_SALARY 테이블에서 아시아 근무 지역에 근무하는 직원의
-- 보너스를 0.5로 변경 ✅
UPDATE 
　　　  EMP_SALARY ES
     SET ES.BONUS = 0.5
WHERE ES.EMP_ID IN (SELECT E.EMP_ID
　　　　　　　　　　  FROM EMPLOYEE E
　　　　　　　　　　  JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
　　　　　　　　　　  JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
　　　　　　　　　　 )
WHERE L.LOCAL_NAME LIKE 'ASIA%';