[ORACLE] Transaction - COMMIT, SAVEPOINT, ROLLBACK, VIEW

트랜젝션(Transaction)

-DB에서 데이터를 처리하는 하나의 논리적인 작업 단위

COMMIT ; 저장되지 않은 모든 데이터를 데이터베이스에 저장하고, 현재의 트랜젝션을 종료하는 명령. 즉, 정상처리되어 확정 Transaction(INSERT, UPDATE, DELETE) 작업내용 DB저장

SAVEPOINT ; 현재까지의 트랜젝션을 특정 이름으로 지정 -->COMMIT명령시 작업이 완료되어 DB확정되고 SAVEPOINT 사라짐 -- ROLLBACK시 사라짐

ROLLBACK ; 저장되지 않은 모든 데이터 변경 사항을 취소하고 현재의 트랜잭션을 끝내라는 명령 Transaction(INSERT, UPDATE, DELETE) 작업내용 취소

자동COMMIT과 자동ROLLBACK이 되는 경우 SQL*PLUS 정상종료는 자동COMMIT, 비정상종료는 자동ROLLBACK DDL과 DCL 명령문이 수행된 경우 자동COMMIT

트랜젝션 조건 (ACID)

1. 원자성(Atomicity) : 모두 다 정상적으로 완료되거나 모두 다 처리가 되지 않아야 한다. ex) 은행입출금

2. 일관성(Consistency) : 트랜젝션 어느 곳에서든 어느 시점에든 동일하게 데이터가 유지되어야 한다.

3. 독립성(Isolation=고립성) : 하나의 트랜젝션이 실행되면 다른 작업은 해당 트랜젝션 중간에 끼어들 수 없어야 한다. 트랜젝션은 다른 트랜젝션과 독립적으로 실행되어야 한다.

4. 영속성(Durabliity=지속성) : 작업이 완료(commit)된 트랜젝션은 시스템에 에러가 발생되어도 영구적으로 데이터에 저장이 되어야 한다.

 

서브쿼리(SUB QUERY)
- 하나의 SQL문 안에 포함된 또 하나의 SELECT문을 의미함 - 서브쿼리를 포함하고 있는 쿼리를 메인 쿼리, 포함된 또 하나의 쿼리를 서브쿼리라 한다. - 서브 쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호로 묶어야 한다. (예외 : INSERT ALL INTO문의 SELECT절, CREATE TABLE 복제 문의 SELECT 절 등) - 서브 쿼리는 메인 쿼리가 실행되기 이전에 한 번만 실행 - 단일행 서브쿼리, 다중행 서브쿼리의 SELECT절 안의 컬럼은 한 개여야만 한다.	--예외1) 테이블 구조만 복사 CREATE TABLE DEPT01 AS SELECT * FROM DEPT WHERE 1=0; --예외2) 동일 테이블 구조에 데이터 복사 추가(서브쿼리 이용) INSERT ALL INTO DEPT01 SELECT * FROM DEPT; --예외3) INSERT ALL INTO EMP4 (ENO, ENM, DNO) VALUES (EMPNO, ENAME, DEPTNO) SELECT EMPNO, ENAME, DEPTNO FROM EMP;
단일행 서브쿼리 (SINGLE ROW) - 오직 하나의 ROW만을 반환 - 단일행 비교연산자(=, >, >=, <, <=, <>) 를 사용 --GROUP BY 함수 대신	--사원 이름이 'JONES'인 사원의 부서명 출력 SELECT DNAME FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'JONES'); SELECT DNAME FROM DEPT WHERE DEPTNO = 20; --SMITH와 같은 부서에서 근무하는 사원의 사번, 이름, 업무, 입사일자, 상위관리자 정보를 출력하세요. SELECT EMPNO, ENAME, JOB, HIREDATE, MGR FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME ='SMITH') AND ENAME <>'SMITH' ; SELECT * FROM EMP WHERE DEPTNO >= (SELECT DEPTNO FROM DEPT WHERE DEPTNO=10) ; --평균 급여보다 더 많은 급여를 받는 사원을 급여많이 받는순 SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(NVL(SAL,0)) FROM EMP ) ORDER BY SAL DESC ; SELECT * FROM EMP WHERE DEPTNO IN (10, 20, 30) ; SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DEPTNO <>40) ;
다중행 서브쿼리 - 서브쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용 - 다중행 연산자 (IN, ANY, ALL)를 사용	--'BLAKE'의 직책을 확인하여 해당 직책을 가진 사람들의 부서번호를 가져와서 그 부서에서 근무하는 사람들의 이름과 고용일을 출력 SELECT ENAME, HIREDATE FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'BLAKE'));

 

연산자
ALL 연산자 컬럼명 < ALL (100, 200, 300) --최소값(100)보다 작다. (소소) 컬럼명 > ALL (100, 200, 300) --최대값(300)보다 크다. (대대)	--부서번호가 30번인 사원들의 급여 중 가장 큰 값(2850보다 많은 급여를 받는 사람의 이름, 급여를 출력 -- 즉, > ALL은 모두 다 큰가 라고 묻는 것이므로 최대값보다 크면 참이 된다. SELECT ENAME, SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO =30); --부서번호가 30번인 사원들의 급여 중 가장 작은 값(950)보다 적은 급여를 받는 사람의 이름, 급여를 출력 --즉, < ALL(소소)케이스는 최소값보다 적으면 참이 된다. SELECT ENAME, SAL FROM EMP WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO=30); --부서별 최고 평균 급여(NULL제외)는 2916.66이므로 급여가 평균 넘는 사원 출력 SELECT * FROM EMP WHERE SAL > ALL(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);
ANY 연산자 컬럼명 < ANY (100, 200, 300) -- 최대값(300)보다 작다. (소대) 컬럼명 > ANY (100, 200, 300) -- 최소값(100)보다 크다. (대소)	--부서번호가 30번인 사원들의 급여 중 가장 작은값(950)보다 많은 급여를 받는 사원의 이름, 급여를 출력 --즉, > ANY 케이스는 최소값보다 크면 참이 된다. SELECT ENAME, SAL FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30) ORDER BY SAL ; --부서번호가 30번인 사원들의 급여 중 가장 큰 값(2850)보다 적은 급여를 받는 사원의 이름, 급여를 출력 --즉, > ANY 케이스는 최소값보다 크면 참이 된다. SELECT ENAME, SAL FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30) ORDER BY SAL DESC ; --사무원이 받는 최고 급여 중 최고 급여인 1300보다 적게받으면서 사무원이 아닌 사원의 정보를 출력 SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE JOB ='CLERK') AND JOB !='CLERK';

 

VIEW (가상테이블)
- 테이블과는 달리 데이터 저장공간이 없다. - 뷰는 단지 쿼리문을 저장하고 있는 객체이다. - 뷰는 실제 자료를 갖지는 않지만 뷰를 통해 테이블의 데이터를 관리할 수 있다. - 뷰는 실제 데이터가 저장되는 공간x이 아니므로 DML(INSERT, UPDATE, DELETE)을 통한 작업불가. (단, 단순뷰 제외) - 하나의 테이블에 뷰의 개수는 제한x이 없다. **뷰이름 권장사항 : view_테이블명_테이블명2 테이블명_view 문법(형식=SYNTAX) CREATE VIEW 뷰이름 AS SELECT 컬럼명, ....| *  FROM 테이블명 WHERE 조건; - VIEW생성시 컬럼별칭을 지정하게 되면 추후 출력시 기존 컬럼명대신 별칭을 입력해야함 - VIEW을 원본으로 테이블 생성이 가능. --생성된 VIEW테이블과 동일명의 VIEW, TABLE 생성 불가 --[REPLACE]기존 생성된 자료값이 새롭게 재정의됨.	--[CREATE VIEW] CREATE VIEW VIEW_EMP01 AS SELECT EMPNO 사번, ENAME 이름, SAL 연봉, DEPTNO 부서번호 FROM EMP01 WHERE DEPTNO = 10; --[별칭VIEW] SELECT * FROM VIEW_EMP01 WHERE 사번 = 7782; --[INSERT] INSERT INTO VIEW_EMP01 (사번,부서번호) VALUES (7777,10); COMMIT; --[DELETE] 자료 제거 DELETE FROM VIEW_EMP01 WHERE 사번 = 7777; --[DROP VIEW] VIEW 제거 DROP VIEW VIEW_EMP01; --생성된 VIEW테이블과 동일명의 VIEW, TABLE 생성 불가 CREATE TABLE VIEW_EMP_DEPT AS SELECT * FROM EMP; --[REPLACE]기존 생성된 자료값이 새롭게 재정의됨. CREATE OR REPLACE VIEW VIEW_EMP_DEPT AS SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 30;
성격에 따른 가능 범위
단순뷰(SIMPLE VIEW) - VIEW를 생성할 서브쿼리에 조건없이 1개의 테이블로 만들어지는 간단한 VIEW. - 하나의 TABLE에서 뽑아오며 함수를 사용 안 한다. - DML(INSERT, UPDATE, DELETE) 사용 가능
복합뷰(COMPLEX VIEW) - 여러 개의 테이블이 JOIN되어 생성되는 것. - 하나이상의 TABLE에서 뽑아오며 함수(GROUP BY HAVING 등과 같은 함수)를 사용한다. - DML x(INSERT, UPDATE, DELETE) 사용 불가능
인라인뷰 (INLINE VIEW) - 여러 번 사용할 필요 없이 1회만 필요한 VIEW일 경우, SQL명령의 FROM절에 VIEW의 서브쿼리 부분을 바로 적어주면 되며 이런 일회용 VIEW를 말함.	SELECT EMPNO, ENAME, DEPTNO, (SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO) DNAME FROM EMP ;