2020년 11월 05일

TCL(Transacion Control Language)

트랜잭션 제어(처리) 언어로 COMMIT, ROLLBACK, SAVEPOINT가 있다.

Transaction

데이터베이스의 논리적 연산 단위로 데이터 변경 사항(DML)을 묶어 하나의 트랜잭션에 담아 처리한다.

COMMIT

메모리 버퍼(트랜잭션)에 임시 저장된 데이터를 데이터베이스에 반영하는 명령어.

ROLLBACK

트랜잭션에 저장된 데이터 변경 사항(DML)을 삭제하고 마지막 COMMIT 상태로 돌아가는 명령어.

SAVEPOINT

트랜잭션에 저장 지점을 지정하여 ROLLBACK 시 전체 작업을 ROLLBACK 하는 것이 아닌 현 시점부터 지정한 SAVEPOINT까지만 ROLLBACK하는 명령어.

실습 코드

SELECT * FROM MEMBER;

-- 현재 트랜잭션을 DB에 반영
COMMIT;

-- MEMBER 테이블에서 '홍길동'삭제
DELETE FROM MEMBER
WHERE MEMBER_NAME = '홍길동';

-- 삭제 확인 (화면상으로는 삭제된걸로 보이지만 아직 DB에 반영되지 않음)
SELECT * FROM MEMBER;

-- '홍길동'이 삭제된 상태를 SAVEPOINT 지정
SAVEPOINT SP1;

-- '박철수'삭제
DELETE FROM MEMBER
WHERE MEMBER_NAME = '박철수';

-- '박철수' 삭제 확인
SELECT * FROM MEMBER;


-- SAVEPOINT SP1로 롤백
ROLLBACK TO SP1;
 

-- 롤백 확인
SELECT * FROM MEMBER;

-- '김영희' 삭제
DELETE FROM MEMBER
WHERE MEMBER_NAME = '김영희';


-- '김영희' 삭제 확인
SELECT * FROM MEMBER;

-- 마지막 COMMIT 상태로 돌아가기 == ROLLBACK
ROLLBACK;

-- 롤백 확인
SELECT * FROM MEMBER;

DDL(Data Definition Language)

데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다.

ALTER

테이블에 정의된 내용을 수정할 때 사용하는 데이터 정의어로 컬럼의 추가/삭제, 제약조건의 추가/삭제, 컬럼의 자료형 변경, DEFAULT 값 변경, 테이블 명/컬럼 명/제약 조건 명 변경 등을 할 수 있다.

/* 
[테이블에 대한 작성법]
ALTER TABLE 테이블명 수정할 내용;

--> 수정할 내용
-- 컬럼 추가 / 삭제
-- 제약 조건 추가/삭제
-- 컬럼의 자료형 변경
-- 컬럼의 DEFAULT 값 변경
-- 컬럼명, 제약조건명, 테이블명 변경
*/

-- 1. 컬럼 추가, 수정, 삭제
SELECT * FROM DEPT_COPY;

-- 1) 컬럼 추가(ADD)
ALTER TABLE DEPT_COPY 
ADD (CNAME VARCHAR2(20)) ;
-- Table DEPT_COPY이(가) 변경되었습니다.

SELECT * FROM DEPT_COPY;

-- 컬럼 추가 시 DEFAULT 값 지정
ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(40) DEFAULT '한국');

SELECT * FROM DEPT_COPY;



-- 2) 컬럼 수정(MODIFY)
ALTER TABLE DEPT_COPY
MODIFY DEPT_ID CHAR(3)
MODIFY DEPT_TITLE VARCHAR2(30)
MODIFY LOCATION_ID VARCHAR2(10)
MODIFY LNAME DEFAULT '미국';


-- 컬럼 수정 시 주의사항
----> 크기를 수정하려는  컬럼에 변경 예정 크기보다 큰 데이터가 있으면 수정되지 않음
SELECT LENGTHB(DEPT_TITLE)
FROM DEPT_COPY
WHERE DEPT_TITLE = '마케팅부';

ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(10);
-- ORA-01441: cannot decrease column length because some value is too big



-- 3) 컬럼 삭제 (DROP  COLUMN 컬럼명)
-- 컬럼값이 존재해도 컬럼은 삭제된다.
-- 삭제된 컬럼은 복구되지 않음. (DDL은 바로 DB에 반영된다.)
-- 모든 컬럼을 삭제할 수는 없음 (최소 한 개 이상의 컬럼이 존재해야 테이블이다.)

CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPT_COPY;


ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;

SELECT * FROM DEPT_COPY2;

ALTER TABLE DEPT_COPY2
DROP COLUMN LOCATION_ID;

ALTER TABLE DEPT_COPY2
DROP COLUMN CNAME;

ALTER TABLE DEPT_COPY2
DROP COLUMN LNAME;

-- 마지막 남은 컬럼 삭제
ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_TITLE;
-- ORA-12983: cannot drop all columns in a table


SELECT * FROM DEPT_COPY2;

-- 컬럼 삭제 시 주의사항
 --> 컬럼 삭제 시 FK 제약조건이 설정된 컬럼은 삭제되지 않음
 
 
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 /* 2. 제약 조건 추가, 삭제
    
    1) 제약 조건 추가(ADD, MODIFY(NOT NULL 추가 시 ))
    
    [작성법]
    ALTER TABLE 테이블명
    ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
    
    
    (NOT NULL의 경우)
    ALTER TABLE 테이블명
    MODIFY 컬럼명 CONSTRAINT  제약조건명 NOT NULL;
*/

ALTER TABLE DEPT_COPY
ADD CONSTRAINT DC_ID_PK PRIMARY KEY(DEPT_ID);


ALTER TABLE DEPT_COPY
ADD CONSTRAINT DC_TITLE_UK UNIQUE(DEPT_TITLE)
MODIFY LNAME CONSTRAINT DC_LNAME_NN NOT NULL;
--> SQL DEVELOPER 에러



-- 기존 테이블에 FK 제약 조건 추가하기

-- EMPLOYEE - DEPARTMENT FK 추가
ALTER TABLE EMPLOYEE 
ADD CONSTRAINT EMP_DCODE_FK FOREIGN KEY(DEPT_CODE)
REFERENCES DEPARTMENT; 

-- EMPOYEE - JOB
ALTER TABLE EMPLOYEE 
ADD CONSTRAINT EMP_JCODE_FK FOREIGN KEY(JOB_CODE)
REFERENCES JOB; 


-- EMPLOYEE - SAL_GRADE
ALTER TABLE EMPLOYEE 
ADD CONSTRAINT EMP_SL_FK FOREIGN KEY(SAL_LEVEL)
REFERENCES SAL_GRADE; 


-- DEPARTMENT - LOCATION
ALTER TABLE DEPARTMENT
ADD CONSTRAINT DEPT_LOID_FK FOREIGN KEY(LOCATION_ID)
REFERENCES LOCATION; 



-- LOCATION - NATIONAL
ALTER TABLE LOCATION
ADD CONSTRAINT LO_NCODE_FK FOREIGN KEY(NATIONAL_CODE)
REFERENCES NATIONAL; 

-- 2) 제약 조건 삭제(DROP)
ALTER TABLE DEPT_COPY
DROP CONSTRAINT DC_TITLE_UK;
-- Table DEPT_COPY이(가) 변경되었습니다.

-- NOT NULL 제약조건 삭제 시 MODIFY 사용
ALTER TABLE DEPT_COPY
MODIFY LNAME NULL;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 3. 컬럼, 제약조건, 테이블, 이름 변경 (RENAME)

-- 1) 컬럼명 변경
-- DEPT_COPY 테이블의 DEPT_TITLE --> DEPT_NAME 변경
ALTER TABLE DEPT_COPY
RENAME COLUMN DEPT_TITLE TO DEPT_NAME;


SELECT * FROM DEPT_COPY;


-- 2) 제약 조건명 변경
ALTER TABLE DEPT_COPY
RENAME CONSTRAINT DC_ID_PK TO DCOPY_DID_PK;


ALTER TABLE DEPT_COPY
RENAME CONSTRAINT SYS_C007101 TO DCOPY_DID_NN;



-- 3) 테이블명 변경
ALTER TABLE DEPT_COPY
RENAME TO DCOPY;

SELECT * FROM DEPT_COPY;  -- 존재하지 않음

SELECT * FROM DCOPY;

DROP

데이터베이스 객체를 삭제하는 구문이다.

-- 4. 테이블 삭제(DROP TABLE)
-- DCOPY 복사
CREATE TABLE DCOPY2
AS SELECT * FROM DCOPY;

SELECT * FROM DCOPY2;

-- DCOPY2에 PK 추가
ALTER TABLE DCOPY2
ADD CONSTRAINT DCOPY2_DID_PK PRIMARY KEY(DEPT_ID);


-- EMPLOYEE 복사
CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_COPY;

-- EMP_COPY와 DCOPY2 FK 설정
ALTER TABLE EMP_COPY
ADD CONSTRAINT ECOPY_DCODE_FK FOREIGN KEY(DEPT_CODE)
REFERENCES DCOPY2;


-- FK 제약조건이 없는(관계가 형성되지 않은) 테이블 삭제
DROP TABLE DCOPY; 

SELECT * FROM DCOPY;  --> 복구 불 !!!!!!! 가 !!!!!!!!!!!!!!!!!!!!!!


--  관계가 형성되어 있는 테이블 삭제
DROP TABLE DCOPY2 CASCADE CONSTRAINT;  
-- ORA-02449: unique/primary keys in table referenced by foreign keys

-- CASCADE CONSTRAINT : EMP_COPY에 있는 FK 제약조건 삭제