신기한 연구소

안녕하세요. 신기한 연구소입니다.

DML(Data Manipulation language)의 범주에 속한다는 TCL(Transaction Control Language)에 대해 알아봅니다.

DML의 범주에 속하지 않는다는 의견도 있지만 그보다 TCL에 대해 잘 정리하는 게 중요한 듯합니다.

오라클 기반에서 설명하겠습니다.

기업에서 내부 이체를 하는 경우를 생각해봅니다.

A라는 계좌의 돈을 B 계좌로 이체하는 경우입니다.

A에서 출금한 뒤 B로 입금이 완료되었습니다.

기업의 자금 시스템에서 해당 이체에 대한 결과를 수신했는데..

A 계좌에서 출금된 기록을 입력한 뒤 시스템 장애나 또는 B 계좌에 입금 처리하는 프로그램 오류로 인해서 B 계좌의 입금내역을 처리하지 못하게 된다면 큰 문제가 발생할 수 있습니다.

A 계좌에서는 돈이 나갔다고 하는데 B 계좌에서는 입금내역이 없는 것이죠.

물론 기업 내 자금 시스템 문제라면 실물 금액은 잘 이체되고 통장에는 잘 정리가 되어 있기에 나중에 대사 하는 과정에서 잘 정리하고 오류를 수정 및 개선하면 됩니다.

그렇지만 은행 시스템이라면 정말 심각한 문제이지요.

그래서 이렇게 하나 이상의 프로세스가 서로 밀접하게 연결되어서 일괄 처리할 수 있도록 논리적 그룹 또는 집합을 구성하는데 이를 트랜잭션(Transaction)이라고 합니다.

위의 경우를 보면 A 계좌의 출금 내역 갱신과 B 계좌의 입금내역 갱신은 서로 밀접하게 연결된 프로세스로 하나만 잘 되면 안 되고 두 개의 프로세스가 같이 실행되거나 취소돼야 데이터의 일관성을 유지할 수 있기에 하나의 논리적 그룹으로 묶어서 처리해야 합니다.

트랜젝션은 데이터의 변화에 대한 처리를 지원하기에 Insert, Update, Delete가 대상이 됩니다.

트랜젝션(Transaction)은 commit, rollback, savepoint 세 가지로 구성됩니다.

세가지 구성에 대해 알아봅니다.

COMMIT;

먼저 위 예제를 확인했을 경우 

A 계좌에 출금 내역 입력, B계좌의 입금내역 입력으로 

두 개의 insert문이 일괄 실행됩니다.

commit문이 실행되기 전은 두 개의 insert문 실행으로 메모리에 임시로 데이터가 입력된 상태입니다.

모든 사용자가 볼 수 있는 실제 입력이 완료된 상황이 아니고 작업자만 확인할 수 있는 상태입니다.

또한 commit 전이기에 해당 행은 락이 걸린 상태라 다른 사용자가 접근해서 변경할 수 없는 상태입니다.

오류가 없거나 데이터 입력이 확신이 되면 commit;문을 실행해서 실제로 데이터베이스에 입력됩니다.

[예시]

UPDATE ACCOUNT

SET REMAIN = 50000

WHERE ACCOUNT_NO = '100-100-100000';

1건 수정완료...

COMMIT; 

커밋완료...

ROLLBACK;

두 개의 입력문을 실행했는데..

입력문을 보니 오류가 있습니다.

그래서 다시 작업을 해서 입력문을 실행해야 한다면 

현재 메모리에 임시 반영된 입력 데이터를 지우고 락을 제거해야 합니다.

그때 Rollback;을 사용합니다.

Rollback;를 실행하면 두 개의 insert을 실행하기 전 상태로 돌아갑니다.

단, commit;을 하고 rollback;을 하는 건 의미가 없습니다.

commit; 전에 사용할 수 있습니다.

commit; 전에 실행한 모든 insert, update, delete문에 대해 원복 한다는 점을 기억하세요.

[예시]

DELETE FROM ACCOUNT

WHERE ACCOUNT_NO = '100-100-100000';

1건 삭제완료...

ROLLBACK; 

롤백완료...

SAVEPOINT;

실전에서 사용한 적은 없습니다만..

한 번에 처리해야 할 프로세스가 많은 상황에서 중간 부분까지 취소를 하고 오류 수정 후 다시 실행할 필요가 있는 경우에 사용하면 편리하겠더군요.

어떤 경우가 있을지 생각해봤는데요.

하나의 테이블로 관리되던 재고 자료를 기본정보와 부가정보로 나눠서 입력하는 경우를 생각해봅니다.

데이터양이 많기에 한번 실행하면 

기본정보가 6시간 부가정보가 4시간 정도로 

총 10시간 정도 걸린다고 가정해봅니다.

일괄처리라 sql문을 쭉 실행했는데 부가정보 중간 부분에 오류가 발생합니다.

헉~

rollback 하고 다시 수정 후 진행하려면 엄청난 시간이 소요되겠지요.

그때 중간에 savepoint를 활용해서 rollback를 해당 savepoint까지만 진행하고 오류 수정 후 다시 진행할 수 있기에

효율적으로 업무를 진행할 수 있게 됩니다.

[예시]

--기본정보 입력

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00001", "홍순이");

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00002", "강돌이");

...

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("99985", "공이돌");  --99985건 입력

SAVEPOINT SV01;

--부가정보 입력

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("00001", 1, "20030202");

...

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("99985", 99985, "20100309"); --99985건 입력

COMMIT;를 하면 전체 입력 완료~

ROLLBACK;을 하면 전체 입력 초기화~

ROLLBACK TO SV01;을 하면 제일 마지막 INSERT문에서 위로 올라가 SAVEPOINT SV01; 바로 전까지 ROLLBACK를 실행합니다.

SAVEPOINT는 중간중간 필요한 부분에 추가해서 넣을 수 있으며 하단부터 위로 ROLLBACK TO SAVEPOINT를 진행하면서 데이터를 확인 및 처리할 수 있습니다.

잘못된 정보나 수정이 필요하면 댓글 주세요.

감사합니다.

안녕하세요. 신기한 연구소입니다.

SQL 문장의 종류에 보면 데이터 정의어(DDL)가 있습니다.

해당 용어에 대해 간단히 정리해보겠습니다.

오라클 기준으로 설명할께요.

데이터 정의어(DDL)는 테이블에 관련된 명령어입니다.

테이블(TABLE)

정리되지 않은 데이터들을 특정 주제와 목적으로 정리해서 만든 집합 개념이라 할 수 있습니다.

행과 열이 존재하는 2차원적 구조이며 흔히 엑셀을 생각하면 쉽게 이해할 수 있습니다.

열(컬럼, 속성)은 더 이상 나눌 수 없는 원자 값을 가질 수 있는 속성을 말합니다.

행은 키가 되는 데이터 그리고 관련된 정보들이 한 줄로 구성되어 있습니다.

기본키(Primary key)

테이블에 있는 각 행의 데이터를 명확히 구분 지어 특정할 수 있는 한 개 이상의 컬럼을 말합니다.

우리가 회사의 직원을 구분할 때나 국민으로 사람을 구분할 때 사용할 수 있는데..

회사면 직원코드나 사번일 거고 국민은 주민번호가 기본키가 될 수 있습니다.

이름은 중복(동명)될 수 있지만 사번, 주민번호는 중복될 수 없으니깐요.

기본키가 되면 자동으로 DBMS에서 UNIQUE INDEX를 생성하면서 동시에 NOT NULL도 지정됩니다.

외부키(Foreign key)

다른 테이블의 기본키를 가지고 있는 겁니다.

직원 정보 테이블에 부서 코드를 넣을 수 있는데 부서 테이블의 키를 가지고 있는 경우 직원 정보 테이블에서 해당 부서 코드를 외부키로 구성합니다.

CREATE TABLE

테이블 생성

새로운 테이블을 생성하는 명령어입니다. 릴레이션을 생성한다고 표현하기도 합니다.

기본 문법은

CREATE TABLE 테이블 명 (

   COULMN1 DATATYPE [DEFALUT TYPE],  -- 컬럼명, 데이터 타입, 속성 설정

   COULMN2 DATATYPE [DEFALUT TYPE],

   ...

   CONSTRAINT [제약조건],

   ...

);

입니다.

사원 정보 테이블을 샘플로 만들어 본다면 다음과 같습니다.

CREATE TABLE EMP (

   EMP_ID CHAR(5) NOT NULL,

   EMP_NM VARCHAR(10) NOT NULL,

   DEPT_ID CHAR(5) NOT NULL,

   BIRTH_DT CHAR(8)

   CONSTRAINT EMP_PK PRIMARY KEY(EMP_ID),  --EMP_ID를 기본키로 설정

   CONSTRAINT EMP_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPT(DEPT_ID) 

        --DEPT테이블의 DEPT_ID를  외래키로 설정

);

컬럼명, 데이터타입, 속성을 필요한 만큼 정의한 뒤 제약조건을 넣어주면 됩니다.

위 구문을 보면 CONSTRAINT가 있는데 제약조건이라고 합니다.

제약조건(CONSTRAINT)

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, NOT NULL, CHECK를 설정합니다.

ALTER TABLE

테이블 수정

COLUMN이나 CONSTRAINT를 추가, 삭제할 경우에 사용합니다.

ALTER TABLE EMP

ADD (PHONE VARCHAR2(13) NULL DEFAULT "000-0000-0000");

PHONE 컬럼을 추가하는데 타입은 VARCHAR2(13)이고 NULL 허용에 기본값은 "000-0000-0000"입니다.

ALTER TABLE EMP

MODIFY(PHONE VARCHAR2(13) NOT NULL DEFALUT "XXX-XXXX-XXXX");

PHONE 컬럼을 수정합니다. NULL에서 NOT NULL로 DEFAULT 값도 "XXX-XXXX-XXXX"로 변경했습니다.

ALTER TABLE EMP

DROP COLUNM PHONE;

PHONE컬럼만 삭제합니다.

ALTER TABLE EMP

RENAME COLUMN PHONE TO CELL_PHONE;

PHONE 컬럼명을 CELL_PHONE으로 변경합니다.

ALTER TABLE EMP

DROP CONSTRAINT EMP_FK;

EMP 테이블의 FK 제약조건을 삭제합니다.

ALTER TABLE EMP

ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(DEPT_ID);

EMP테이블에 FK(DEPT_ID) 제약조건을 추가합니다.

RENAME  테이블명 수정

RENAME EMP TO SAWON;

EMP 테이블 명을 SAWON으로 수정합니다.

DROP TABLE

테이블의 모든 데이터와 테이블 자체를 삭제

DROP TABLE EMP [CASCADE | RESTRICT];

DROP 명령을 사용하면 해당 테이블의 존재 자체가 사라진다고 보면 됩니다.

TRUNCATE TABLE

테이블의 모든 행만 삭제하고 테이블 구조는 살아있음.

TRUNCATE TABLE EMP;   

주의할 점은 시스템 부하가 DELETE에 비해 적은데 복구가 불가능하기에 유의해서 사용해야 합니다.

기타

NULL은 '', "", 0, 조회값없음 등과 전혀 다른 값입니다. 아직 할당이 안되었다로 보면 됩니다.

DEFAULT는 INSERT로 데이터를 입력할 때 아무런 값도 입력하지 않아도 기본적으로 셋팅할 수 있는 명령어입니다. DEFAULT "Y"로 하면 값을 입력하지 않을 시 해당 필드는 "Y"가 자동 입력됩니다.

개발하면서 도움이 되길 바랍니다.

잘못된 정보가 있으면 댓글 주세요. 감사합니다.