(36일차) 12월 14일

TCL (Transaction Control Language) : 트랜잭션 처리어

- 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어

 · COMMIT : 변경된 내용을 영구 저장하기(하드디스크에 저장)  

 · ROLLBACK : 변경되기 이전 상태로 되돌리기  

 · SAVEPOINT : 중간 저장지점 만들기   

  

 

  1. ROLLBACK

  - INSERT, UPDATE, DELETE 등 테이블에서 수정된 행들을 수정되기 이전 상태로 원래 상태로 복귀
  - 사용목적 : 1개의 transaction안에 있는 여러 SQL문 중 1개라도 실행 중 에러가 발생하면 이미 실행된 다른 SQL문들을 원래                                      초기상태로 정상 복귀하기 위해 사용

 

  2. COMMIT 

 - INSERT, UPDATE, DELETE 등 테이블에서 수정된 행들의 데이터 값을 확정
 - COMMIT 명령어를 실행한 후에 ROLLBACK을 하더라도 최초 상태로 복귀하지 않음

   (CREATE TABLE을 수행한 경우에도 내부적으로 COMMIT 명령어를 실행하여 ROLLBACK을 하여도 원래 상태로 복귀 못함)

 - 사용목적 : 하나의 transaction 내에 포함된 모든 SQL문들이 정상적으로 완료되면 ROLLBACK 되지 못하도록 최종 확정(승인)

-- 1. DELETE는 ROLLBACK 가능 : DML의 명령어로 ROLLBACK에 의해서 원상복귀 가능
DELETE FROM dept03; 
 
-- 2. TRUNCATE는 ROLLBACK 불가능 : CREATE와 같은 DDL의 명령어로 내부적으로 COMMIT 명령어를 무조건 실행함
TRUNCATE TABLE dept03;

-- 3. TRUNCATE가 DELETE보다 수행 속도가 빨라 TRUNCATE 사용 권장
--   (DELETE가 속도가 느린 이유는 나중에 ROLLBACK을 대비해서 최초 데이터들을 별도로 SAVE하여 관리하기 때문)

 

 

  3. SAVEPOINT 

-- SAVEPOINT 예제
DELETE FROM dept01 WHERE DEPTNO=30;
SAVEPOINT C1; -- C1 전에 수행결과를 임시 저장 (1차 COMMIT)

DELETE FROM dept01 WHERE DEPTNO=20;
SAVEPOINT C2;

DELETE FROM dept01 WHERE DEPTNO=10;

ROLLBACK TO C2;  -- SAVEPOINT C2까지 원상복구(ROLLBACK)
ROLLBACK TO C1;  -- SAVEPOINT C1까지 원상복구(ROLLBACK)
ROLLBACK;        -- COMMIT이후 최초단계까지 원상복구(ROLLBACK)

 

 

 

13장 데이터 무결성을 위한 제약 조건

 

ORACLE에서 무결성(INTEGRITY) 제약(CONSTRAINTS) 조건

 1. NOT NULL : NULL을 허용X (C)

 2. UNIQUE : 중복된 값을 허용X (U) 

 3. PRIMARY KEY : NULL과 중복된 값을 허용X (P)

 4. FOREIGN KEY : 테이블의 값의 참조를 허용O (R)

 5. CHECK : 설정한 값만 허용O (C)

 

 

CONSTRAINT NAME 지정 방법

-- CONSTRAINT + 이름 (예 : CONSTRAINT EMP04_EMPNO_UK) 
DROP TABLE EMP04;
CREATE TABLE EMP04 (
    EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,
    ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

 

 

DATABASE의 3대 무결성 제약조건

 

 1. DOMAIN 무결성 제약조건

  - NOT NULL, UNIQUE, CHECK

-- 1. NOT NULL 설정
DROP TABLE EMP01;
CREATE TABLE EMP01(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);


-- 2. UNIQUE 설정 (NULL은 정해지지 않은 수로 여러번 INSERT해도 중복 체크하지 않음)
DROP TABLE EMP03;
CREATE TABLE EMP03 (
    EMPNO NUMBER(4) UNIQUE,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

DESC EMP03; -- UNIQUE 확인 불가능
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;  -- UNIQUE 확인 가능 (제약 보기)


-- 3. CHECK 설정
DROP TABLE EMP07;
CREATE TABLE EMP07(
    EMPNO NUMBER(4) CONSTRAINT EMP07_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10) CONSTRAINT EMP07_ENAME_NN NOT NULL,
    SAL NUMBER(7, 2) CONSTRAINT EMP07_SAL_CK CHECK (SAL BETWEEN 500 AND 5000),
    GENDER VARCHAR2(1) CONSTRAINT EMP07_GENDER_CK CHECK (GENDER IN('M', 'F'))
);

 

 

 2. 개체 무결성 제약조건

  - PRIMARY KEY 

   · PRIMARY KEY를 지정하지 않은 경우 INSERT, UPDATE 등을 통해 중복된 행을 추가할 가능성이 높아 테이블의 데이터 신뢰도가 낮아짐

-- 1. PRIMARY KEY(UNIQUE + NOT NULL) 설정
DROP TABLE EMP05;
CREATE TABLE EMP05(
    EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

 

 

 

 3. 참조 무결성 제약조건

  - FOREIGN KEY

-- FOREIGN KEY 설정 : REFERENCES DEPT(DEPTNO)
DROP TABLE EMP06;
CREATE TABLE EMP06(
    EMPNO NUMBER(4) CONSTRAINT EMP06_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10) CONSTRAINT EMP06_ENAME_NN NOT NULL,
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2) CONSTRAINT EMP06_DEPTNO_FK REFERENCES DEPT(DEPTNO)
);

 

 

DEFAULT 설정하기

-- DEFAULT 설정
-- INSERT문을 사용하여 행을 생성할 때 속성에 대한 값을 명시적으로 주지 않으면 DEFAULT로 설정된 값으로 생성됨
DROP TABLE DEPT01;
CREATE TABLE DEPT01(
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13) DEFAULT 'SEOUL'
);

 

 

테이블 레벨 방식으로 CONSTRAINT 조건 지정하기

-- 1. 테이블 레벨 방식으로 CONSTRAINT 조건 지정하기
-- 컬럼(속성) 레벨에서만 지정 가능한 CONSTRAINT 조건 : NOT NULL, DEFAULT
-- 컬럼(속성), 테이블 레벨에 지정 가능한 CONSTRAINT 조건 : PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
CREATE TABLE EMP03(
    EMPNO NUMBER(4) CONSTRAINT EMP03_ENAME_NN NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    DEPTNO NUMBER(4),
    -- TABLE LEVEL CONSTRAINT 지정
    CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY(EMPNO),
    CONSTRAINT EMP03_JOB_UK UNIQUE(JOB),
    CONSTRAINT EMP03_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);


-- 2. 복합키를 기본키로 지정 (TABLE LEVEL로 CONSTRAINT 지정)
CREATE TABLE MEMBER01(
    NAME VARCHAR2(10),
    HPHONE VARCHAR2(16),
    ADDRESS VARCHAR2(30),
    -- 복합키를 TABLE LEVEL로 CONSTRAINT 지정
    CONSTRAINT MEMBER01_COMBO_PK PRIMARY KEY(NAME, HPHONE)
);


-- 3. TABLE 생성 후 테이블 레벨(TABLE LEVEL)에서 CONSTRAINT 조건 추가하기
DROP TABLE EMP01;
CREATE TABLE EMP01 (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    DEPTNO NUMBER(4)
);

ALTER TABLE EMP01  
ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);


-- 4. TABLE 생성 후 컬럼 레벨(COLUMN LEVEL)에서 CONSTRAINT 조건 추가하기
ALTER TABLE EMP01
MODIFY ENAME CONSTRAINT EMP01_ENAME_NN NOT NULL;


-- TABLE에 설정된 CONSTRAINT 조건 제거하기 (DROP CONSTRAINT + 이름)
ALTER TABLE EMP05
DROP CONSTRAINT EMP05_EMPNO_PK;

 

 

---

 

 

6. Transaction.sql (Scott) : 완료

 

7. 데이터무결성제약조건.sql (Scott) : 완료