국비 7일차 DML(INSERT, UPDATE, DELETE, MERGE) TCL(COMMIT, ROLLBACK)

2021.01.13 국비교육 7일차

[TOC]

DML

INSERT

• 테이블에 데이터를 입력하기 위한 명령어

  -> 새로운 레코드 추가

• INSERT INTO table (column, column, column) VALUES(값, 값, 값, 값)

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (90, '인사과', '서울');
-- 데이터 삽입

INSERT INTO DEPT ( LOC, DNAME, DEPTNO)
VALUES('서울', '인사과', 70);
--칼럼에 해당되는 데이터 잘 넣어주면 INSERT ERROR 없음

INSERT INTO DEPT
VALUES(80, '인사과', '서울');
-- COLUMN 명시 안하더라도 VALUES 순서 맞으면 상관 X

• NULL 값 입력
  • 묵시적 방법 : COLUMN, VALUE 생략하면됨 (제약조건이 NOT NULL이 아닌경우)
  • 명시적 방법 : NULL이나 빈문자열 ' ' 사용하면 됨

INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(91, '인사과');
-- 묵시적

INSERT INTO DEPT
VALUES(92, '인사과', NULL);

INSERT INTO DEPT
VALUES(92, '인사과', '');
-- 명시적

• 특수한 형태

INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(9000, USER, '연구원', 7839, SYSDATE, 5000, NULL, 90);

--HIREDATE 자리에 SYSDATE를 자주 사용(회원가입)
--integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found 오류
--> PK에 값이 없음

INSERT INTO EMP
VALUES(9001, '홍길동', 'MANAGER', 7839, '2000/01/01', 2000, NULL, 30);

• 다중 행 입력

--1. 임시 행 생성
CREATE TABLE copy_emp
AS
SELECT EMPNO, ENAME FROM EMP
WHERE 1=2; -- 조건이 맞지 않기 때문에 값이 없는 칼럼만 생성

--2. 데이터 복사
INSERT INTO copy_emp(EMPNO, ENAME)
SELECT EMPNO, ENAME FROM EMP; -- EMP테이블에서 값 복사

무조건 INSERT ALL

• 다중 테이블에 다중 데이터 저장

CREATE TABLE SAL_HISTORY
AS
SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE 1 =2;

CREATE TABLE MGR_HISTORY
AS
SELECT EMPNO,MGR, SAL
FROM EMP
WHERE 1 =2;

INSERT ALL
    INTO SAL_HISTORY VALUES(EMPNO, HIREDATE, SAL)
    INTO MGR_HISTORY VALUES(EMPNO, MGR, SAL)
    SELECT EMPNO, HIREDATE, MGR, SAL FROM EMP;

조건 INSERT ALL

CREATE TABLE SAL_HISTORY
AS
SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE 1 =2;

CREATE TABLE MGR_HISTORY
AS
SELECT EMPNO,MGR, SAL
FROM EMP
WHERE 1 =2;

INSERT ALL
    WHEN SAL < 2500 THEN
                INTO SAL_HISTORY VALUES(EMPNO, HIREDATE, SAL)
    WHEN SAL > 2500 THEN
                INTO MGR_HISTORY VALUES(EMPNO, MGR, SAL)
    SELECT EMPNO, HIREDATE, MGR, SAL
    FROM EMP;

SELECT * FROM SAL_HISTORY;
SELECT * FROM MGR_HISTORY;

CREATE TABLE MYEMP_HIRE2
AS
SELECT EMPNO, ENAME, HIREDATE, SAL
FROM EMP
WHERE 1=2;

CREATE TABLE MYEMP_MGR2
AS
SELECT EMPNO, ENAME, MGR
FROM EMP
WHERE 1=2;

INSERT ALL
    WHEN SAL > 3000 THEN
        INTO MYEMP_HIRE2 VALUES (EMPNO, ENAME, HIREDATE, SAL)
    WHEN MGR = 7698 THEN
        INTO MYEMP_MGR2 VALUES (EMPNO, ENAME, MGR)
SELECT EMPNO, ENAME, HIREDATE, MGR, SAL
FROM EMP;

SELECT * FROM myemp_hire2;
SELECT * FROM myemp_mgr2;

조건 INSERT FIRST

• 조건문 처럼 차례대로 해당되는거 삽입

CREATE TABLE TEST_HISTROY
AS
SELECT EMPNO, SAL FROM EMP
WHERE 1=2;;

INSERT FIRST 
    WHEN SAL = 800 THEN
        INTO SAL_HISTORY VALUES(EMPNO, HIREDATE, SAL)
    WHEN SAL < 2500 THEN
        INTO MGR_HISTORY VALUES(EMPNO, MGR, SAL)
    ELSE
        INTO TEST_HISTORY VALUES(EMPNO, SAL)
SELECT EMPNO, HIREDATE, MGR, SAL FROM EMP;

SELECT * FROM SAL_HISTORY;
SELECT * FROM MGR_HISTORY;
SELECT * FROM TEST_HISTORY;

UPDATE

• 테이블에 저장된 행들을 변경

UPDATE DEPT
SET DNAME = '경리과', LOC='부산'
WHERE DEPTNO = 90;

CREATE TABLE COPY_DEPT
AS
SELECT * FROM DEPT;

UPDATE COPY_DEPT
SET LOC = '서울'
WHERE DEPTNO = 40;
--COPY DEPT에서 40번 부서의 지역을 서울로 변경

UPDATE COPY_DEPT
SET DNAME = '경리과'
WHERE DNAME LIKE '인사%';
--부서명이 인사로 시작하는 부서의 이름을 경리과로 수정

UPDATE COPY_DEPT
SET DNAME = '영업부', LOC = '서울'
WHERE DEPTNO = 10;
-- 10번부서의 이름을 영업부, 지역을 서울로 수정

UPDATE EMP
SET JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7900),
    SAL = (SELECT SAL FROM EMP WHERE EMPNO = 7844)
WHERE EMPNO = 9001;

UPDATE COPY_DEPT
SET DNAME = (SELECT DNAME FROM DEPT WHERE DEPTNO = 10),
    LOC = (SELECT LOC FROM DEPT WHERE DEPTNO=20)
WHERE DEPTNO = 60;
--3. 60번 부서의부서이름을 DEPT테이블의 10번부서의 이름, 부서지역을 DEPT테이블의 20번 부서의 지역으로 수정

DELETE

• 테이블에 저장된 행 삭제

DELETE FROM COPY_DEPT
WHERE DEPTNO = 90;

DELETE FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM COPY_DEPT WHERE DNAME = '경리과');
--EMP에서 회원 삭제 - 부서번호가 경리과의 부서번호인 회원(DEPT 테이블의경리과 부서번호 이용)

DELETE FROM COPY_DEPT
WHERE LOC = (SELECT LOC FROM COPY_DEPT WHERE DEPTNO =30);
--COPY_DEPT에서 30번 부서와 동일한 지역의 부서정보 삭제

MERGE

• 구조가 같은 2개의 테이블을 비교하여 하나의 테이블로 합치기

CREATE TABLE pt_01
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량 NUMBER,
  금액 NUMBER);

  CREATE TABLE pt_02
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량 NUMBER,
  금액 NUMBER);

  CREATE TABLE p_total
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량 NUMBER,
  금액 NUMBER);

INSERT INTO pt_01 VALUES('20150101', '1000', 10, 500);
INSERT INTO pt_01 VALUES('20150102', '1001', 10, 400);
INSERT INTO pt_01 VALUES('20150103', '1002', 10, 300);


INSERT INTO pt_02 VALUES('20150101', '1003', 5, 500);
INSERT INTO pt_02 VALUES('20150102', '1004', 5, 400);
INSERT INTO pt_02 VALUES('20150103', '1005', 5, 300);
COMMIT;


MERGE INTO p_total total
USING pt_01 p01
ON (total.판매번호 = p01.판매번호)
WHEN MATCHED THEN
    UPDATE SET total.제품번호 = p01.제품번호
WHEN NOT MATCHED THEN
    INSERT VALUES (p01.판매번호, p01.제품번호, p01.수량, p01.금액);

MERGE INTO p_total total
USING pt_02 p02
ON (total.판매번호 = p02.판매번호)
WHEN MATCHED THEN
    UPDATE SET total.제품번호 = p02.제품번호
WHEN NOT MATCHED THEN
    INSERT VALUES (p02.판매번호, p02.제품번호, p02.수량, p02.금액);    

TRANSACTION

• 트랜잭션은 데이터베이스 작업의 논리적인 단위
• 데이터베이스 조작을 가리킴
• 트랜잭션의 대상은 DML문(Manipulation)
• ALL or NOTHING

COMMIT / ROLLBACK 장단점

• 데이터의 일관성을 보장
• 데이터의 변경사항을 영구 반영하기 전에 미리 볼 수 있음
• 논리적으로 연관된 작업을 그룹화 할수 있다.

COMMIT

• 모든 데이터 변경사항을 데이터베이스에 영구히 반영
• 변경전의 데이터는 모두 잃게 됨
• 모든 사용자들이 트랜잭션 종료 후의 결과를 확인 할 수 있음
• COMMIT 후에 잠금해소 및 변경 가능

ROLLBACK

• 모든 데이터 변경 취소
• 변경 전 데이터 복원
• 트랜잭션 종료 후의 결과 확인

읽기 일관성

• 사용자들에게 가장 최근에 커밋된 데이터를 보여주는 것
• 데이터를 검색하는 사용자들과 변경하는 사용자들 사이에 일관적인 관점을 제공
• 즉, 다른 사용자들은 변경중인 데이터 못 봄