TRANSACTION 관리
* 트랜잭션 개요
- 관계형 데이터베이스에서 실행되는 여러 개의 SQL명령문을
하나의 논리적 작업 단위로 처리하는 개념
- DML(U,D,I)의 집합
- CREATE..
- COMMIT : 트랜잭션의 정상적인 종료
- ROLLBACK : 트랜잭션의 전체 취소
* 명시적인 트랜잭션 제어 명령문
COMMIT : 트랜잭션내의 모든 SQL 명령문에 위해 변경된 작업 내용ㅇ르 디스크에 영구적으로
저장하고 트랜잭션을 종료
ROLLBACK : 트랜잭션내의 모듬 SQL 명령문에 위해 변경된 작업 내용ㅇ르 전부 취소하고
트랜잭션을 종료
* 기존 데이터에 변경이 가해지면 그 전까지 SQL 명령문들을 한 트랜잭션으로 처리된다.
------------COMMIT;
-- DELETE
UPDATE
UPDATE
.... * 저장하면 트랜잭션이 분리 된다.
INSERT
UPDATE
------------ROLLBACK;
COMMIT
* COMMIT 개요
- 하나의 트랜잭션에서 실행되는 모든 SQL 명령문의 처리 결과가 하드디스크에 안전하게 보장되는 것을 보장
- 처리 결과를 디스크에 영구적으로 저장
- 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제
- 서로 다른 트랜잭션을 구분하는 기준
- COMMIT 명령문 실행하기 전에 하나의 트랜잭션 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록 방지하여 일관성 유지 - 읽기 일관성
예제) 54, 총무부, 부산 추가
SQL> INSERT INTO DEPT
2 VALUES(54,'총무부','부산');
1 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
ROLLBACK
ROLLBACK 개요
- 하나의 트랜잭션에서 실행된 SQL 명령문의 처리결과를 취소
- CPU,메모리 같은 해당 트랜잭션에 할당된 자원을 해제,
- 트랜잭션을 강제 종료
예제)
QL> UPDATE DEPT
2 SET LOC='대구'
3 ;
9 행이 갱신되었습니다.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 대구
20 RESEARCH 대구
30 SALES 대구
40 OPERATIONS 대구
50 인사부 대구
51 회계부 대구
52 영업부 대구
53 자재부 대구
54 총무부 대구
9 개의 행이 선택되었습니다.
SQL> ROLLBACK;
롤백이 완료되었습니다.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 인사부 서울
51 회계부 인천
52 영업부 대전
53 자재부 대전
54 총무부 부산
9 개의 행이 선택되었습니다.
SQL>
예제) 55,'추가1'
56,'추가2'
56,'추가2'
57,'추가3'를 저장 전으로 수정
SAVEPOINT A;
58,'점A이후'
58,'점A이후'를 점이후로 수정
ROLLBACK TO A;
SQL> INSERT INTO DEPT
2 VALUES(&U1,'&U2','&U3');
u1의 값을 입력하십시오: 55
u2의 값을 입력하십시오: 추가1
u3의 값을 입력하십시오: NULL
구 2: VALUES(&U1,'&U2','&U3')
신 2: VALUES(55,'추가1','NULL')
1 개의 행이 만들어졌습니다.
SQL> /
u1의 값을 입력하십시오: 56
u2의 값을 입력하십시오: 추가2
u3의 값을 입력하십시오: NULL
구 2: VALUES(&U1,'&U2','&U3')
신 2: VALUES(56,'추가2','NULL')
1 개의 행이 만들어졌습니다.
SQL> /
u1의 값을 입력하십시오: 57
u2의 값을 입력하십시오:
u3의 값을 입력하십시오:
구 2: VALUES(&U1,'&U2','&U3')
신 2: VALUES(57,'','')
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 인사부 서울
51 회계부 인천
52 영업부 대전
53 자재부 대전
54 총무부 부산
55 추가1 NULL
56 추가2 NULL
DEPTNO DNAME LOC
---------- -------------- -------------
57
12 개의 행이 선택되었습니다.
SQL> UPDATE DEPT
2 SET DNAME= '추가3'
3 WHERE = DEPTNO = 57;
WHERE = DEPTNO = 57
*
3행에 오류:
ORA-00936: 누락된 표현식
SQL> UPDATE DEPT
2 SET DNAME= '추가3'
3 WHERE DEPTNO = 57;
1 행이 갱신되었습니다.
SQL> SAVEPOINT A
2 ;
저장점이 생성되었습니다.
SQL> INSERT INTO DEPT
2 VALUES(58,'점A이후')
3 ;
INSERT INTO DEPT
*
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다
SQL> INSERT INTO DEPT
2 VALUES(58,'점A이후',NULL);
1 개의 행이 만들어졌습니다.
SQL> VALUES(58,'점A이후',NULL);
SP2-0734: "VALUES(58,..."(으)로 시작되는 알 수 없는 명
SQL> UPDATE DEPT
2 SET DNAEM='점이후'
3
SQL> UPDATE DEPT
2 SET DNAME='점이후'
3 WHERE DEPTNO = 58;
1 행이 갱신되었습니다.
SQL> ROLLBACK TO A;
SAVEPOINT
문법)
SAVEPOINT 포인트명;
ROLLBACK TO 포인트명;
SAVEPOINT : 트랜잭션의 한 지점표시
- 아주 긴~~작업을 수행하다 한번의 실수로 그동안의 모든 작업을 취소될 경우에 대비하여 일정시점마다 SAVAPOINT를 찍어두면 일정부분문 취소를 해줄 수 있다.
TABLE-LEVEL LOCK; WHERE 조건없이 U,D,I
테이블 전체행에 영향을 줄때
DEPT 테이블 테이더 전체 삭제
ROCK : 읽기 일관성 유지
ROW-LEVER ROCK :조건 있는 U,D,I
TABLE-LEVEL ROCK : 조건 없는 U,D
숙제) 트랜잭션 개념 이기하고 정리하기
SUBQURY
개요
-하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL 명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법
-서브쿼리의 필요성
예 : 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색
- 처리방식
1. 교수 테이블에서 ‘전은지’ 교수의 직급 검색 SQL 명령문 실행
2. 교수 테이블의 직급 칼럼에서 1 에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행
1, 2 SQL 명령문 처리과정을 통합하여 하나의 SQL 명령문으로 처리
- 하나의 쿼리문의 절에 들어가는 또 다른 쿼리문
- 실행순서 : 서브쿼리 -> 메인쿼리
--단일행 서브쿼리
--다중행 서브쿼리
--다중컬럼 서브쿼리
문제1) 7499 사원과 동일한 업무를 하는 사원들의
사원명, 사원번호, 업무를 출력
1번
SQL> SELECT JOB
2 FROM EMP
3 WHERE EMPNO=7499
4 ;
JOB
---------
SALESMAN
* 단일행 서브 쿼리
SQL> SELECT ENAME, EMPNO, JOB
2 FROM EMP
3 WHERE JOB='SALESMAN';
ENAME EMPNO JOB
---------- ---------- ---------
ALLEN 7499 SALESMAN
WARD 7521 SALESMAN
MARTIN 7654 SALESMAN
TURNER 7844 SALESMAN
2번
SQL> SELECT ENAME, EMPNO, JOB
2 FROM EMP
3 WHERE JOB = (SELECT JOB
4 FROM EMP
5 WHERE EMPNO=7499);
ENAME EMPNO JOB
---------- ---------- ---------
ALLEN 7499 SALESMAN
WARD 7521 SALESMAN
MARTIN 7654 SALESMAN
TURNER 7844 SALESMAN
SQL>
문제2) SCOTT 이 하는 일과 동일업무를 하는 사원의
사원명, 사원번호, 급여, 부서번호를 출력
SQL> SELECT ENAME, EMPNO, SAL, DEPTNO
2 FROM EMP
3 WHERE JOB=(SELECT JOB
4 FROM EMP
5 WHERE ENAME='SCOTT');
ENAME EMPNO SAL DEPTNO
---------- ---------- ---------- ----------
SCOTT 7788 3000 20
FORD 7902 3000 20
문제3) SCOTT이 받는 급여보다 많이 받는 사원의
사원이름, 급여, 커미션, 업무를 출력
SQL> SELECT ENAME, SAL, COMM, JOB
2 FROM EMP
3 WHERE SAL > (SELECT SAL
4 FROM EMP
5 WHERE ENAME='SCOTT');
ENAME SAL COMM JOB
---------- ---------- ---------- ---------
KING 5000 PRESIDENT
문제4) SCOTT이 일하는 부서에 근무하는 사원들의
ENAME, SALM, COMM, JOB 출력
SQL> SELECT ENAME, SAL, COMM, JOB
2 FROM EMP
3 WHERE DEPTNO = (SELECT DEPTNO
4 FROM EMP
5 WHERE ENAME='SCOTT');
ENAME SAL COMM JOB
---------- ---------- ---------- ---------
SMITH 800 CLERK
JONES 2975 MANAGER
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
FORD 3000 ANALYST
다중행 서브 쿼리
-IN ( = 의미와 비슷) 만족하는것
-ANY (어떤 어느것 어느한) or 최소한 하나이상은 만족
-ALL 모두 반드시 만족
-EXISTS 보여주거나 아예 안보여 주는것
문제1) 부서번호가 20번인 사원과 동일한 급여를 받는 사원들의
사원명, 급여, 커미션, 부서번호 출력
* IN
SQL> SELECT ENAME, SAL, COMM, DEPTNO
2 FROM EMP
3 WHERE SAL = (SELECT SAL
4 FROM EMP
5* WHERE DEPTNO = 20)
SQL> ;
SQL> /
WHERE SAL = (SELECT SAL
*
3행에 오류:
ORA-01427: 단일 행 부속 질의(SUBQUERY)에 2개 이상의 행이 리턴되었습니다
SQL>SELECT ENAME, SAL, COMM, DEPTNO
2 FROM EMP
3 WHERE SAL IN(SELECT SAL
4 FROM EMP
5* WHERE DEPTNO = 20);
ENAME SAL COMM DEPTNO
---------- ---------- ---------- ----------
SMITH 800 20
ADAMS 1100 20
JONES 2975 20
SCOTT 3000 20
FORD 3000 20
정영선 3000 40
이성민 3000 40
7 개의 행이 선택되었습니다.
* ANY (OR)
1 SELECT ENAME, SAL, COMM, DEPTNO
2 FROM EMP
3 WHERE SAL > ANY(SELECT SAL
4 FROM EMP
5* WHERE DEPTNO = 20)
SQL> ;
SQL> /
ENAME SAL COMM DEPTNO
---------- ---------- ---------- ----------
ALLEN 1600 300 30
WARD 1250 500 30
JONES 2975 20
MARTIN 1250 1400 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
권덕용 2900 40
표희태 2900 40
정영선 3000 40
이성민 3000 40
17 개의 행이 선택되었습니다.
* ALL (AND)
1 SELECT ENAME, SAL, COMM, DEPTNO
2 FROM EMP
3 WHERE SAL > ALL(SELECT SAL
4 FROM EMP
5* WHERE DEPTNO = 20)
SQL> ;
SQL> /
ENAME SAL COMM DEPTNO
---------- ---------- ---------- ----------
KING 5000 10
문제2) 30번 부서에 근무하는 어떠한 사원보다 일찍 입사한 사원의
이름과 급여와 입사일을 출력
1 select ename, sal, hiredate
2 from emp
3 where hiredate<all(select hiredate
4 from emp
5 where deptno = 30)
ENAME SAL HIREDATE
---------- ---------- --------
SMITH 800 80/12/17
SQL> select ename, sal, hiredate
from emp
where hiredate<any(select hiredate
from emp
where deptno = 30);