[Oracle] 트랜잭션과 그룹쿼리

5. 트랜잭션과 그룹쿼리

5.1 트랜잭션(transaction)

　5.1.1 정의

　　5.1.1.1 분리되어서는 안되는 (논리적인) 작업단위

　　5.1.1.2 데이터베이스 내에서 한꺼번에 수행되어야 할 일련의 연산들

　5.1.2 트랜잭션은 한꺼번에 완료가 되거나 한꺼번에 취소가 되어야 함(원자성)

　5.1.3 TCL(transaction control language: 트랜잭션 제어어)

　　DML문이 실행되어 DB에 저장되거나(commit) 되돌리기(rollback) 위해서 실행해야 하는 SQL문

　　5.1.3.1 commit : SQL문의 결과를 영구적으로 DB에 반영하는 SQL문

　　5.1.3.2 rollback : SQL문의 결과를 이전 transaction의 끝부분까지 취소하는 SQL문

　　5.1.3.3 savepoint : transaction의 한 지점을 표시하는 임시 저장점

　5.1.4 예제

-- 테이블 스페이스 생성

CREATE TABLE dep (

id              VARCHAR2(10)    PRIMARY KEY,

name        VARCHAR2(15)    NOT NULL,

location    VARCHAR2(50)

);

INSERT INTO dep VALUES ('10', '영업부', '서울 강남구');

SAVEPOINT a;

INSERT INTO dep VALUES ('20', '회계부', '부산 동래구');

SAVEPOINT b;

INSERT INTO dep VALUES ('30', '개발부', '인천 계양구');

SELECT * FROM dep;

ROLLBACK TO a;

SELECT * FROM dep;

COMMIT;

SELECT * FROM dep;

　5.1.5 실수로 커밋한 자료의 복구 방법

　　sqlplus에서 실행 가능 (toad에서는 안됨)

　　undo_retention : delete, update 후에 COMMIT을 했을 때부터 속성값의 시간(초)까지는 오라클에서 임시로 저장한 데이터로 복구할 수 있음

　　DEFAULT 속성값은 '900'으로 900/60초 = 15분

　　COMMIT 후 15분 이내에는 데이터를 복구할 수 있음

　　그 시간을 25분(1500초)으로 늘리려면

* 복구 방법

- 데이터를 삭제하고 커밋한 경우

create table test

as select * from book_table;

DELETE FROM TEST WHERE id = 1;

COMMIT;

-- 데이터 복구

SELECT * FROM TEST AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15'

MINUTE) WHERE ID = 1;

삭제하고 커밋한지 15분안의 데이터를 찾을 수 있음

INSERT INTO TEST SELECT * FROM TEST AS OF

TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE) WHERE ID = 1;

5.2 그룹 쿼리

　5.2.1 그룹핑(그룹을 짓는다) : group by 절을 사용

(문제) emp 테이블에서 부서코드 단위로 부서코드 조회하기

select deptno, count(*), sum(sal), avg(sal) from emp

group by deptno;    -- 집계 기준

(문제) 학과별로 교수들의 평균 급여를 출력하시오.

select deptno, avg(pay)

from professor

group by deptno;

(문제) 학과별, 직급별로 교수들의 평균 급여를 출력하시오.

select deptno, position, avg(pay)

from professor

group by deptno, position;

　5.2.2 group by절과 having절

　　5.2.2.1 group by절

　　　a. 특정 컬럼값이나 표현식을 단위로 집계성 데이터를 보기 위해서 집계함수와 함께 사용

　　　b. select절에 오는 컬럼 또는 값은 모두 group by절에 명시해야 함

　　　c. group by 절을 하나 이상의 컬럼 또는 값을 가짐

　　　d. order by 절은 group by 절 다음에 위치함

　　　　(주의) order by 절에 오는 컬럼 또는 값은 group by절에 명시한 값 이외의 값은 사용할 수 없음

　　　e. where 절은 group by절 앞에 위치함

　　5.2.2.2 having절

　　　a. where 조건 이외의 집계함수의 결과로 조건을 주고자 할 때 사용

　　　　group by절과 함께 사용함

　　　b. 일반적으로 집계함수나 상수가 사용된 조건을 명시하나 group by절에 명시된 컬럼도 조건으로 사용 가능함

　　　c. where절과 having절은 동시에 사용 가능

　　　(문제) 교수의 평균 급여 450 이상인 학과와 평균급여를 출력하시오.

　　　　select dname, avg(pay)

　　　　from professor p, department d

　　　　where p.deptno=d.deptno

　　　　group by dname

　　　　having avg(pay) >= 450;

5.3 실습문제

　5.3.1 교수 중에서 급여총액(급여+보너스)이 가장 높은 교수와 가장 낮은 교수, 급여총액의 평균금액을 출력하시오.

　　bonus가 null인 경우에는 nvl() 함수를 사용하여 대체값을 지정

　　ex) nvl(pay+bonus, pay)

　5.3.2 student 테이블의 birthday 컬럼을 사용하여 월별로 태어난 인원수를 출력하시오.

　　생년월일 컬럼에서 월을 선택하기 위해서는 to_char(birthday, 'MM') 함수를 사용한다.