[ORACLE] 10일차 : 트랜잭션, ORACLE 12C

 

트랜잭션이란?
데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는
한번에 모두 수행되어야 할 일련의 연산들을 의미한다.
트랜잭션은 데이터베이스 시스템에서 병행제어 및 회복 작업시 처리되는 작업의 논리적 단위
사용자가 시스템에 대한 서비스 요구시 시스템이 응답하기 위한 상태 변환 과정의 작업단위

 

 

 

트랜잭션 특징

1. 원자성 
   1. 트랜잭션은 한 개 이상의 동작을 논리적으로 한 개의 작업 단위로 묶음
   2. 원자성은 트랜잭션 범위에 있는 모든 동작이 모두 실행되거나 또는 모두 실행이 취소됨을 보장
   3. 모든 동작이 성공적으로 실행되면 트랜잭션은 성공
   4. 하나라도 실패하면 트랜잭션은 실패하고 모든 과정을 롤백
2. 일관성
   1. 트랜잭션이 종료되면, 시스템은 비즈니에서 기대하는 상태가 됨.
   2. 서적 구매 트랜잭션이 성공적으로 실행되면 결제 내역, 구매 내역 잔고 정보가 비즈지스에 맞게 저장되고 변경됨
3. 고립성
   1. 트랜잭션은 다른 트랙잭션과 독립적으로 실행되어야 하며, 서로 다른 트랜잭션이 동일한 데이터에 동시에 접근 할 경우 알맞게 동시 접근을 제어
   2. 동시 접근 제어는 설정한 격리 레벨에 따라 달라짐.
4. 지속성
   1. 트랜잭션이 완료되면, 그 결과는 지속적으로 유지되어야 함.
   2. 현재의 어플리케이션이 변경되거나 없어지더라도 데이터는 유지됨
   3. 일반적으로 데이터베이스나 다른 물리적인 저장소를 통해서 트랜잭션 결과가 저장

 

COMMIT 과 ROLLBACK

 

트랜잭션 제어어는 COMMIT ROLLBACK,SAVEPOINT가 있다. 오라클은 INSERT, UPDATE,DELETE 명령을 실행하면 자동으로 트랜잭션이 완료된 상태가 아니므로 COMMIT 또는 ROLLBACK 명령을 이용하여 트랜잭션을 완료 해야 한다.

 

명칭	설명
COMMIT	트랜잭션이 완료( INSERT, UPDATE,DELETE 완료)된 상태로 데이터베이스에 저장된다
ROLLBACK	트랜잭션이 취소( INSERT, UPDATE,DELETE 취소)된 상태로 데이터베이스에 저장되지 않는다
SAVEPOINT	트랜잭션 내의 한 시점을 표시한다. ROLLBACK TO savepoint 명령어로 표시 지점까지 ROLLBACK하는데 쓰인다.

 

 

CREATE TABLE emp2 AS SELECT * FROM emp; -- 테이블 생성

DELETE FROM emp2 WHERE empNo = '1001';
DELETE FROM emp2 WHERE empNo = '1002';
SELECT * FROM emp2;

SAVEPOINT a; -- 트랜잭션의 한 시점을 설정
DELETE FROM emp2 WHERE empNo = '1003';
SELECT * FROM emp2;

ROLLBACK TO a; -- 마지막에 DELETE 한것만 ROLLBACK
SELECT * FROM emp2 ORDER BY EMPNO;

 

 

 

---

 

12C부터 추가된 새로운 기능 

 

 

Top-N

쿼리는 순서가 지정된 데이터 집합에서 반환되는 행 수를 제한하는 방법을 제공한다. 세트에서 상위 또는 하위 "N"개의 행 수를 리턴 하거나 데이터를 통해 페이징 할 때 매우 유용하다.

 

-- 급여 내림차순 정렬하여 처음부터 3개의 행만 출력
SELECT *
FROM emp
ORDER BY sal DESC
FETCH FIRST 3 ROWS ONLY;

--급여 내림차순 정렬하여 5개를 건너뛰고 다음부터 3개의 행만 출력
SELECT *
FROM emp
ORDER BY sal DESC
OFFSET 5 ROWS FETCH FIRST 3 ROWS ONLY;

-- 급여 내림차순 정렬하여 상위 10%
SELECT *
FROM emp
ORDER BY sal 
FETCH FIRST 10 PERCENT ROWS ONLY;

 

 

INVISIBLE column

12C부터는 테이블에서 INVISIBLE column(보이지 않는 컬럼) 생성이 가능하다. INVISIBLE column은 다음 작업을 처리할 때 컬럼이 보이지 않는다. 가상 컬럼을 INVISIBLE 컬럼으로 만들 수 있다.

 

 

CREATE TABLE test8(
        rum NUMBER PRIMARY KEY,
        name VARCHAR2(30) NOT NULL,
        tel VARCHAR2(30) INVISIBLE
        );
        
SELECT * FROM test8;--안보임
SELECT * FROM col WHERE tname = 'TEST8'; --안보임
SELECT * FROM cols WHERE table_name = 'TEST8'; -- 보임
--INVISIBLE 컬럼이 보이지만 INVISIBLE 컬럼인지는 확인 불가 

SELECT * FROM user_tab_cols WHERE table_name = 'TEST8'; -- 보임
--HIDDEN_COLUMN 컬럼 항목에서 INVISIBLE 확인 가능

INSERT INTO test8 VALUES(1,'a'); -- 가능 tel 이 INVISIBLE이므로 가능
INSERT INTO test8 VALUES(2,'b','nn'); -- 에러

 

 

IDENTITY column

 

자동으로 숫자가 증가되는 IDENTITY column을 지원한다. 내부적으로 시퀀스를 이용하여 관리한다. IDENTITY column은 테이블마다 하나의 IDENTITY column만 가질 수 있다. 

 

CREATE TABLE test7(
        num NUMBER GENERATED AS IDENTITY PRIMARY KEY,
        subject VARCHAR2(100) NOT NULL
        );
        
INSERT INTO test7(subject)VALUES('a');
INSERT INTO test7(num,subject)VALUES(s,'b');--에러

 

 

DEFAULT 

• DEFAULT 값으로 시퀀스의 NETVAL사용

CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값으로 시퀀스의 CURRVAL,NETVAL를 사용 할 수 있다. DEFAULT값으로 시퀀스의 NETVAL 있는 테이블에서 데이터를 INSERT후 ROLLBACK 하면 시퀀스 값은 계속 증가한다. 

 

• NULL값을 위한 DEFAULT값 지정

NULL이 입력될 경우 NULL대신 입력될 값을 DEFAULT로 지정하는 기능이 추가 되었다. CREATE TABLE, ALTER TABLE명령의 DEFAULT 값을 설정할 때 ON NULL 옵션을 추가하면 NULL값이 입력되는 경우 DEFAULT 값이 추가된다.