Oracle_TCL_INSERT/ UPDATE/ DELDTE

/*
    [ TCL : TRANSACTION CONTROL LANGAGE ]
              트랜잭션     제어     언어

    * TRANSACTION
    - 데이터베이스의 논리적 연산단위
    - 데이터의 변경사항(DML)들을 하나의 트랜잭션으로 묶어서 관리
      COMMIT(확정)하기 까지의 변경사항들을 하나의 트랜잭션에 담게됨
    - 트랜잭션의 대상이 되는 SQL: INSERT, UPDATE, DELDTE(
    
    TRANSACTION의 4가지 속성
    ACID
    
    1. Atomicity (원자성): 트랜잭션 내의 모든 작업이 수행되거나, 전혀 수행되지 않아야 하는 원칙 (결제 중 오류 발생 시 초기화 되는 것)
    2. Consistency (일관성): 트랜잭션이 성공적으로 완료 된 후에도 데이터베이스는 유효한 상태를 유지해야한다는 원칙 (재고없을때 주문해도 -1이 될 수 없음)
    3. Isolateion (고립성): 동시에 실행되는 여러 트랜잭션이 상호간에 영향을 끼치지 않도록 하는 원칙 (이미 선택된 좌석입니다~)
    4. Durablity (지속성): 트랜잭션이 성공적으로 수행되면, 시스템의 문제가 발생하더라도 영구적으로 저장되어야 하는 원칙
    
    COMMIT(트랜잭션을 종류 처리 후 확정), ROLLBACK(트랜잭션 취소), SAVEPOINT(임시저장점 잡기)
*/

SELECT * FROM EMPLOYEE_COPY WHERE EMP_ID = 222;

-- 사번이 222번인 사원 삭제
DELETE
  FROM
       EMPLOYEE
 WHERE
       EMP_ID = 222;

-- 사번이 221번인 사원 삭제
DELETE
  FROM
       EMPLOYEE
 WHERE
       EMP_ID = 221;
       
SELECT * FROM EMPLOYEE_COPY;

ROLLBACK;
--------------------------------------------------------------------------------

SELECT * FROM EMPLOYEE_COPY;  -- 샐랙트는 트랜잭션과는 아무 연관이 없음
-- 사번이 222번인 사원 삭제

DELETE FROM EMPLOYEE_COPY WHERE EMP_ID = 222;   -- 트랜잭션 생성

UPDATE EMPLOYEE_COPY SET EMP_NAME = '홍홍홍' WHERE EMP_NAME = '홍길동';

SELECT * FROM EMPLOYEE_COPY;

COMMIT;
ROLLBACK;
--------------------------------------------------------------------------------

-- 사번이 217, 216, 214 삭제
DELETE
  FROM
       EMPLOYEE_COPY
 WHERE
       EMP_ID IN (217,216, 214);
       

-- 3개 행 이(가) 삭제된 시점에 SAVEPOINT 지정
SAVEPOINT DELETE3ROWS;

-- 사원명이 홍홍홍인 사원의 이름을 홍길동으로 갱신
UPDATE 
       EMPLOYEE_COPY
   SET
       EMP_NAME = '홍길동'
 WHERE
       EMP_NAME = '홍홍홍';

ROLLBACK TO DELETE3ROWS;  -- 세이브포인트로 ROLLBACK
SELECT * FROM EMPLOYEE_COPY;

ROLLBACK;

--------------------------------------------------------------------------------
COMMIT;
-- 사번이 218번인 사원 삭제
DELETE
  FROM
       EMPLOYEE_COPY
 WHERE
       EMP_ID = 218;

SELECT * FROM EMPLOYEE_COPY;

CREATE TABLE HAHA(
    HID NUMBER
);

ROLLBACK;

SELECT * FROM EMPLOYEE_COPY;        -- 테이블만들고 ROLLBACK하면 못돌아옴

/*
    DDL 구문 (CREATE, ALTER, DROP)을 수행하는 순간
    기존에 트랜잭션에 있는 모든 작업사항들을 무조건 COMMIT해서 실제 DB에 반영시킨 후에 DDL을 수행
    → DDL수행전 트랜잭션이 만들어져있다면 COMMIT/ROLLBACK하고나서 DDL을 수행해야함
*/