[Oracle(SQL)] DML (INSERT, UPDATE, DELETE, TRUNCATE, MERGE)

DML(Data Manipulation Language)

- INSERT, UPDATE, DELETE, SELECT

- 데이터 조작 언어, 테이블에 값을 삽입하거나, 수정하거나, 삭제하거나, 조회하는 언어

 

INSERT

- 새로운 행을 추가하는 구문

- 테이블의 행 갯수가 증가한다

- 테이블의 일부 컬럼에 INSERT할 때 표현식

INSERT INTO 테이블명 (컬럼명, 컬럼명, ..) VALUES (데이터, 데이터, ..);

- 테이블의 모든 컬럼에 INSERT할 때

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', '장채현', '901123-2080503', 'jang_ch@greedy.com', '01055569512'
, 'D1', 'J7', 'S3', 4300000, 0.2
, '200', SYSDATE, NULL, DEFAULT
);

 

SELECT해보면 기존 EMPLOYEE 테이블에 '장채현' 정보가 추가된 것 확인

SELECT
       E.*
  FROM EMPLOYEE E
 WHERE EMP_NAME = '장채현';

 

INSERT시에 VALUES 대신 서브쿼리를 이용할 수 있다.

CREATE TABLE EMP_01(
  EMP_ID NUMBER,
  EMP_NAME VARCHAR2(30),
  DEPT_TITLE VARCHAR2(20)
);

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)  --이 결과값을 EMP_01테이블에 넣는 것
);

 

INSERT ALL 

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

 

CREATE TABLE EMP_DEPT_D1
AS
SELECT E.EMP_ID
     , E.EMP_NAME
     , E.DEPT_CODE
     , E.HIRE_DATE
  FROM EMPLOYEE E
 WHERE 1 = 0; --절대 성립할수 없는 조건

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

=>

EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
조회해서 사번, 이름, 소속부서, 입사일을 삽입하고,
EMP_MANAGER 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
조회해서 사번, 이름, 관리자 사번을 삽입하세요

 

EMP_DEPT_D1 테이블에 부서코드 D1인 직원 삽입(INSERT INTO)

INSERT
  INTO EMP_DEPT_D1 A
(
  A.EMP_ID
, A.EMP_NAME
, A.DEPT_CODE
, A.HIRE_DATE
)
(
  SELECT E.EMP_ID
       , E.EMP_NAME
       , E.DEPT_CODE
       , E.HIRE_DATE
    FROM EMPLOYEE E
   WHERE E.DEPT_CODE = 'D1' --EMPLOYEE테이블의 D1인 직원들을 EMP_DEPT_D1에 삽입한다
);

EMP_MANAGER 테이블에 부서코드 D1인 직원 조회해서 관리자사번 삽입(INSERT INTO)

INSERT
  INTO EMP_MANAGER A
(
  A.EMP_ID
, A.EMP_NAME
, A.MANAGER_ID
)
(
  SELECT E.EMP_ID
       , E.EMP_NAME
       , E.MANAGER_ID
    FROM EMPLOYEE E
   WHERE E.DEPT_CODE = 'D1' --EMPLOYEE테이블의 D1인 직원들을 EMP_MANAGER에 삽입한다
);

 

조회해보고 EMP_DEPT_D1, EMP_MANAGER 모든 행 삭제

DELETE
  FROM EMP_DEPT_D1; --테이블에 있는 모든 행이 삭제됨
  
DELETE
  FROM EMP_MANAGER;

 

INSERT ALL로 삽입(위에서 따로따로 INSERT해준 것과 동일한 값 출력)

INSERT ALL
  INTO EMP_DEPT_D1 --INTO 테이블명
VALUES
(
  EMP_ID         --컬럼들 나열
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
)
  INTO EMP_MANAGER --INTO 테이블명
VALUES
(
  EMP_ID         --컬럼들
, EMP_NAME
, MANAGER_ID
)
SELECT E.EMP_ID  --사용되는 모든 컬럼들 SELECT
     , E.EMP_NAME
     , E.DEPT_CODE
     , E.HIRE_DATE
     , E.MANAGER_ID
  FROM EMPLOYEE E
 WHERE E.DEPT_CODE = 'D1';

 

EMPLOYEE 테이블에서 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 사번, 이름, 입사일, 급여를 조회하여
EMP_OLD 테이블에 삽입하고 그 이후에 입사한 사원은 EMP_NEW 테이블에 삽입하세요.

 

EMP_OLD 테이블 생성

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

EMP_NEW 테이블 생성

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

 

EMP_OLD, EMP_NEW 테이블에 조건 설정 후 삽입 (한 문장)

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

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

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

 

DEPT_COPY 테이블 생성

CREATE TABLE DEPT_COPY
AS
SELECT D.*
  FROM DEPARTMENT D;

 

D9 부서코드를 전략기획팀으로 변경

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

 

UPDATE시에도 서브쿼리를 사용할 수 있다.

UPDATE 테이블명
SET 컬럼명 = (서브쿼리)

 

CREATE TABLE EMP_SALARY
AS
SELECT E.EMP_ID
     , E.EMP_NAME
     , E.DEPT_CODE
     , E.SALARY
     , E.BONUS
  FROM EMPLOYEE E;

 

최우식 사원의 급여와 보너스율을 김다미 사원과 동일하게 변경해준다.

(SET절이 모두 중복됨)

UPDATE
       EMP_SALARY ES
   SET ES.SALARY = (SELECT E1.SALARY
                      FROM EMPLOYEE E1
                     WHERE E1.EMP_NAME = '김다미'
                    )
     , ES.BONUS = (SELECT E2.BONUS
                     FROM EMPLOYEE E2
                    WHERE E2.EMP_NAME = '김다미'
                   )
 WHERE ES.EMP_NAME = '최우식';

 

다중열 서브쿼리를 이용한 UPDATE문
최웅, 국연수, 김지웅, 엔제이 사원의 급여와 보너스를
김다미 사원의 급여와 보너스와 같게 변경하는 UPDATE문 작성

UPDATE
       EMP_SALARY ES
   SET (ES.SALARY, ES.BONUS) = (SELECT E1.SALARY, E1.BONUS
                                  FROM EMPLOYEE E1
                                 WHERE E1.EMP_NAME = '김다미'
                                )
 WHERE ES.EMP_NAME IN ('최웅', '국연수', '김지웅', '엔제이');

 

다중행 서브쿼리를 이용한 UPDATE
EMP_SALARY 테이블에서 아시아 근무 지역에 근무하는 직원의
보너스를 0.5로 변경하세요

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

 

UPDATE시 변경 값은 해당 컬럼에 대한 제약 조건에 위배되지 않아야 함

- FOREIGN KEY 제약 조건 위배됨 (부모 키가 없습니다)

UPDATE
       EMPLOYEE E
   SET E.DEPT_CODE = '65'
 WHERE E.DEPT_CODE = 'D6';

 

- NOT NULL 제약 조건 위배됨

UPDATE 
       EMPLOYEE E
   SET E.EMP_NAME = NULL
 WHERE E.EMP_ID = '200';

 

- UNIQUE 제약 조건 위배됨

UPDATE
       EMPLOYEE E
   SET E.EMP_NO = '870427-2232123' --기존에 등록된 주민번호 가져옴
 WHERE E.EMP_ID = '200';

 

DELETE

- 테이블의 행을 삭제하는 구문이다.
- 테이블의 행의 갯수가 줄어든다.
- 만약 WHERE 조건을 설정하지 않으면 모든 행이 다 삭제된다.

DELETE FROM 테이블명 WHERE 조건설정

 

COMMIT 후 EMPLOYEE 테이블 모든 행 삭제 (WHERE조건 설정X)

ROLLBACK 후 조회해보면 다시 살아난다.

COMMIT;

DELETE
  FROM EMPLOYEE E;
  
SELECT
       E.*
  FROM EMPLOYEE E;
  
ROLLBACK;

 

WHERE조건 설정 DELETE

DELETE
  FROM EMPLOYEE E
 WHERE E.EMP_NAME = '장채현';

 

- FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있는 값에 대해서는 삭제할 수 없다.

DELETE
  FROM DEPARTMENT D
 WHERE D.DEPT_ID = 'D1';

 

- FOREIGN KEY 제약조건이 설정되어 있어도 참조되고 있지 않는 값에 대해서는 삭제 가능

DELETE
  FROM DEPARTMENT D
 WHERE D.DEPT_ID = 'D3';

 

TRUNCATE

- 테이블의 전체 행을 삭제할 시 사용한다.
- DELETE보다 수행 속도가 더 빠르다.
- ROLLBACK을 통해 복구할 수 없다.

 

TRUNCATE TABLE EMP_SALARY;

ROLLBACK해도 값이 돌아오지 않는다.

SELECT
       ES.*
  FROM EMP_SALARY ES;
  
ROLLBACK;

SELECT
       ES.*
  FROM EMP_SALARY ES;

 

MERGE

- 구조가 같은 두 개의 테이블을 하나로 합치는 기능을 한다.
- 테이블에서 지정하는 조건의 값이 존재하면 UPDATE
- 조건의 값이 없으면 INSERT됨

 

EMP_M01 테이블 생성

CREATE TABLE EMP_M01
AS
SELECT E.*
  FROM EMPLOYEE E;

 

EMP_M02 테이블 생성

CREATE TABLE EMP_M02
AS
SELECT E.*
  FROM EMPLOYEE E
 WHERE E.JOB_CODE = 'J4';

 

EMP_M02에 행 추가

INSERT
  INTO EMP_M02 A
(
  A.EMP_ID, A.EMP_NAME, A.EMP_NO, A.EMAIL, A.PHONE
, A.DEPT_CODE, A.JOB_CODE, A.SAL_LEVEL, A.SALARY, A.BONUS
, A.MANAGER_ID, A.HIRE_DATE, A.ENT_DATE, A.ENT_YN
)
VALUES
(
  999, '유관순', '000101-4567890', 'yoo@greedy.com', '01012345678'
, 'D9', 'J4', 'S1', 9000000, 0.5
, NULL, SYSDATE, NULL, DEFAULT
);

 

MERGE
 INTO EMP_M01 M1
USING EMP_M02 M2
   ON (M1.EMP_ID = M2.EMP_ID) --두 테이블의 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
);