[Oracle(SQL)] TCL(Transaction Control Language), DDL(ALTER, DROP)

TIL/Oracle(SQL)

[Oracle(SQL)] TCL(Transaction Control Language), DDL(ALTER, DROP)

yndev 2022. 1. 25. 18:33

TCL(Transaction Control Language)

- 트랜잭션 제어 언어

- COMMIT과 ROLLBACK이 있다.

트랜잭션이란?

- 한꺼번에 수행되어야 할 최소의 작업 단위를 말한다.
- 논리적 작업 단위(Logical Unit of Work : LUW)
- 하나의 트랜잭션으로 이루어진 작업은 반드시 한꺼번에 완료(COMMIT)되어야 하며,

그렇지 않은 경우에는 한꺼번에 취소(ROLLBACK)되어야 함

COMMIT

- 트랜잭션 작업이 정상 완료되면 변경 내용을 영구히 저장

ROLLBACK

- 트랜잭션 작업을 취소하고 최근 COMMIT한 시점으로 이동

SAVEPOINT

- 현재 트랜잭션 작업 시점에 이름을 정해줌
- 하나의 트랜잭션 안에 구역을 나눔

ROLLBACK TO 세이브포인트명

- 트랜잭션 작업을 취소하고 SAVEPOINT 시점으로 이동

CREATE TABLE TBL_USER(
  USERNO NUMBER UNIQUE,
  ID VARCHAR2(20) PRIMARY KEY,
  PASSWORD CHAR(20) NOT NULL
);

...3, test1, pass1 까지 총 3행의 값 저장 후 COMMIT

INSERT
  INTO TBL_USER A
(
  A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
  1
, 'test1'
, 'pass1'
);
.
.
.
COMMIT;

COMMIT 후 4번째 행 추가

INSERT
  INTO TBL_USER A
(
  A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
  4
, 'test4'
, 'pass4'
);

ROLLBACK 후 값을 확인해보면 3까지 저장한 결과가 출력됨

(4행을 추가하기 전에 COMMIT해줬기 때문)

ROLLBACK;

SELECT
       UT.*
  FROM TBL_USER UT;

4번 행 다시 삽입 해주고 SAVEPOINT 입력

INSERT
  INTO TBL_USER A
(
  A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
  4
, 'test4'
, 'pass4'
);

SAVEPOINT SP1;

이후 5번 행 값넣어준 후 조회하면 5번 행까지 값 출력됨

INSERT
  INTO TBL_USER A
(
  A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
  5
, 'test5'
, 'pass5'
);

SELECT
       UT.*
  FROM TBL_USER UT;

SP1(SAVEPOINT)로 ROLLBACK 후 조회해보면 4번행까지 출력됨(SAVEPOINT 지정 된 시점으로)

ROLLBACK TO SP1;

SELECT
       UT.*
  FROM TBL_USER UT;

ROLLBACK 후 다시 출력하면 3번행까지 출력(COMMIT한 시점)

ROLLBACK;

SELECT
       UT.*
  FROM TBL_USER UT;

DDL(Data Definition Language)

ALTER

- 객체를 수정하는 구문

테이블 객체 수정 : ALTER TABLE 테이블명 수정내용;

- 컬럼 추가/삭제/변경, 제약조건 추가/삭제/변경
- 테이블명 변경, 제약조건 이름 변경

컬럼 추가 ADD

ALTER TABLE DEPT_COPY 
ADD (LNAME VARCHAR2(20));

컬럼 삭제 DROP

ALTER TABLE DEPT_COPY
DROP COLUMN LNAME;

컬럼 생성 시 DEFAULT값 지정 (CNAME 컬럼 추가 후 DEFAULT값으로 한국 설정)

ALTER TABLE DEPT_COPY
ADD (CNAME VARCHAR2(20) DEFAULT '한국');

컬럼에 제약조건 추가

(NOT NULL빼고 나머지 제약조건은 COPY되지않음)

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

ALTER TABLE DEPT_COPY2
ADD CONSTRAINT PK_DEPT_ID2 PRIMARY KEY(DEPT_ID);

ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UN_DEPT_TITLE2 UNIQUE(DEPT_TITLE);

ALTER TABLE DEPT_COPY2
MODIFY DEPT_TITLE CONSTRAINT NN_LID NOT NULL;

컬럼 자료형 수정 MODIFY

ALTER TABLE DEPT_COPY2
MODIFY DEPT_ID CHAR(3)
MODIFY DEPT_TITLE VARCHAR2(30)
MODIFY LOCATION_ID VARCHAR2(2);

컬럼의 크기를 줄이는 경우에는 변경하려는 크기를 초과하는 값이 없을 때만 변경할 수 있다.

(초과해서 DEPT_TITLE 자료형 변경 불가)

ALTER TABLE DEPT_COPY2
MODIFY DEPT_TITLE VARCHAR2(10);

DEFAULT 값 변경
(기존에 한국으로 DEFAULT값을 설정해놔서 변경X)

ALTER TABLE DEPT_COPY
MODIFY CNAME DEFAULT '미국';

새로 값을 설정한 곳에는 미국으로 설정된다.

INSERT
  INTO DEPT_COPY
VALUES
(
  'D0'
, '생산부'
, 'L2'
, DEFAULT
);

컬럼 삭제 DROP

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_TITLE;

ALTER TABLE DEPT_COPY2
DROP COLUMN LOCATION_ID;

테이블에 최소 한 개 이상의 컬럼이 남아있어야 하므로 마지막 남은 열은 삭제불가
(모든 열을 삭제할 수 없음, 오류발생)

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;

CREATE TABLE TB1(
  PK NUMBER PRIMARY KEY, --PK는 FK의 부모키
  FK NUMBER REFERENCES TB1,
  COL1 NUMBER,
  CHECK (PK > 0 AND COL1 > 0)
);

컬럼 삭제 시 참조하고 있는 컬럼이 있다면 삭제를 못한다.

(PK는 FK의 부모키이기 때문에 삭제할 수 없음)

ALTER TABLE TB1
DROP COLUMN PK;

제약조건도 함께 삭제한다면 삭제할 수 있다.

ALTER TABLE TB1
DROP COLUMN PK CASCADE CONSTRAINTS;

컬럼 삭제

DROP CLUMN 삭제할 컬럼명 또는 DROP(삭제할 컬럼명)

- 데이터가 기록되어 있어도 삭제됨
- 삭제된 컬럼은 복구가 불가능
- 테이블에는 최소 한 개 이상의 컬럼이 존재해야 함 : 모든 컬럼 삭제 불가능

ALTER TABLE DEPT_COPY
DROP (CNAME);

CREATE TABLE CONST_EMP(
  ENAME VARCHAR2(20) NOT NULL,
  ENO VARCHAR2(15) NOT NULL,
  MARRIAGE CHAR(1) DEFAULT 'N',
  EID CHAR(3),
  EMAIL VARCHAR2(30),
  JID CHAR(2),
  MID CHAR(3),
  DID CHAR(2),
  -- 테이블 레벨로 제약조건 설정
  CONSTRAINT CK_MARRIAGE CHECK(MARRIAGE IN('Y', 'N')),
  CONSTRAINT PK_EID PRIMARY KEY(EID),
  CONSTRAINT UN_ENO UNIQUE(ENO),
  CONSTRAINT UN_EMAIL UNIQUE(EMAIL),
  CONSTRAINT FK_JID FOREIGN KEY(JID) REFERENCES JOB(JOB_CODE) ON DELETE SET NULL,
  CONSTRAINT FK_MID FOREIGN KEY(MID) REFERENCES CONST_EMP ON DELETE SET NULL,
  CONSTRAINT FK_DID FOREIGN KEY(DID) REFERENCES DEPARTMENT ON DELETE CASCADE
);

제약조건 삭제

- 제약조건 1개 삭제시

ALTER TABLE CONST_EMP
DROP CONSTRAINT CK_MARRIAGE;

- 제약조건 여러개 삭제시

ALTER TABLE CONST_EMP
DROP CONSTRAINT FK_DID
DROP CONSTRAINT FK_JID
DROP CONSTRAINT FK_MID;

- NOT NULL 제약 조건 삭제 시 MODIFY 사용

ALTER TABLE CONST_EMP
MODIFY (ENAME NULL, ENO NULL);

RENAME

컬럼 이름 변경

CREATE TABLE DEPT_COPY3
AS SELECT * FROM DEPARTMENT;

ALTER TABLE DEPT_COPY3
RENAME COLUMN DEPT_ID TO DEPT_CODE;

제약조건 이름 변경

ALTER TABLE DEPT_COPY3
ADD CONSTRAINT PK_DEPT_CODE3 PRIMARY KEY(DEPT_CODE);

ALTER TABLE DEPT_COPY3
RENAME CONSTRAINT PK_DEPT_CODE3 TO PK_DCODE;

테이블 이름 변경

ALTER TABLE DEPT_COPY3
RENAME TO DEPT_TEST;

테이블 삭제

DROP TABLE DEPT_TEST CASCADE CONSTRAINTS;