2019. 4. 16. 09:55ㆍDateBase
-----------------------------트랜잭션(Transaction)-----------------------------
트랜잭션(Transaction)이란
- 데이터의 일관성을 보장하기 위해서 사용한다.
- Insert, Update, Delete 구문만 트랙잭션처리의 대상이다.
- 한개 이상의 단일작업(DB Access 작업)을 논리적 작업 그룸으로 묶어서 처리한다.
단일 작업이란(DB Access 작업)
- DAO에서의 기능을 C.R.U.D기능이라 하는데 이것을 한번의 DB Access작업을 하는것을 단일작업이라 한다.
트랜잭션의 시작과 종료
- 트랜잭션의 시작
첫번째 DML(INSERT, UPDATE, DELETE 구문) SQL이 실행될 때 자동으로 시작된다.
- 종료
COMMIT 또는 ROLLBACK 구문이 실행되면 자동으로 종료된다.
시스템이 고장난 경우
COMMIT ROLLBACK
- COMMIT은 해당 트랜잭셩내에서 실행된 DML작업(데이터변경작업)을 영구적으로 저장한다.
- ROLLBACK은 해당 트랜잭션내에서 실행된 DML작업의 DB 반영을 전부 취소한다.
트랜적션의 특징(ACID)
- 원자성 (Atomicity)
트랜잭션내의 작업이 부분적으로 성공하거나 실패하는 것을 허용하지 않는 것
All or Nothing으로 동작
- 일관성 (Consistency)
트랜잭션내의 작업이 성공적으로 완료되었다면 언제나 일관성 있는 데이터 상태로 유지되는 것
* 무결성제약조건을 위반하는 (일관성 있는 데이터상태가 아닌 것) 트랜잭션의 실행될 수 없다.
- 고립성 (Isolation)
트랜잭션 수행시 다른 트랜잭션의 연산이 영향을 미치지 못하도록 하는 것
* 트랜잭션이 종료(완료) 되기 전의 중간상태의 데이터를 외부 트랜잭션이 볼 수 없음
- 지속성 (Durability)
성공적으로 수행된 트랜잭션의 결과는 데이터베이스에 영구적으로 반영된다.
예시) 트랜잭션 예시
-------------------------------------오라클 데이터베이스 객체-------------------------
오라클 데이터베이스 객체
시퀀스
- 일련번호 생성기
테이블
- 데이터의 저장소
뷰
- 가상의 테이블이다.
- 하나 이상의 테이블을 기반으로 하는 가상의 테이블이고, 자체적인
데이터를 포함하고 있지는 않지만, 테이블과 마찬가지로 뷰를
대상으로 CRUD가 가능하다.
* 대부분 뷰를 대상으로는 SELECT만 사용한다.
- 뷰를 사용하는 이유
* 복잡한 SQL문을 쉽게 작성할 수 있다.
* 데이터 엑세스를 제한할 수 있다.
* 동일한 데이터로부터 다양한 결과를 얻을 수 있다.
인덱스
- 데이터의 색인정보를 가지고 있다.
<인라인 뷰 (Inline View)>
- 메인쿼리의 from절에 작성된 서브쿼리를 말한다.
* select문의 실행결과가 가상의 테이블로 취급된다.
* View는 오라클의 데이터베이스 객체지만, 인라인뷰는 오라클의 데이터베이스 객체가 아니다.
* 메인쿼리가 실행되는 동안 잠깐 만들어지는 가상의 테이블이다.
- Top-N 분석 쿼리에 자주 사용된다.
- 인라인뷰도 조인에 참여할 수 있다.
- 테이블에서 조건에 맞는 최상위 데이터 n개 또는 최하휘 데이터 n개를 조회할 때 사용된다.
- 회사에 급여를 가장 많이 받는 직원 3명
- 가장 최근에 입사한 직원 5명
- 한 달 동안 가장 많이 팔린 제품 10가
- select 컬럼명, 컬럼명
from (select 컬럼명, 컬럼명
from 테이블
order by Top-N 분석대상 컬럼명)
where rownum <= N
* rownum은 서브쿼리 (인라인뷰에서 반환되는 각 행에서 1부터 시작하는 순번을 할당한다.)
예시) rownum 사용 예시
select rownum, department_id, department_name
from departments;
분석함수
- 분석대상 컬럼의 값을 기준으로 순번 혹은 순위를 부여하는 함수이다.
* row_number() : 순번을 부여한다.
* rank() : 순위를 부여한다.
* dese_rank()
- Top-N 분석을 수행할 수 있다.
- 특정 범위에 속한 행을 찾을 때 유리하다.
- 페이징처리에 유용하게 사용되는 함수다.
- 사용방법
select 분석함수 over (order by 정렬대상컬럼명 정렬방향) 별칭, 컬럼명, 컬럼명
from 테이블명
* 정렬대상 컬럼의 값을 기준으로 행을 정렬한 다음 순번 혹은 순위를 부여한다.
<서브쿼리, 인라인 뷰, 스칼라 서브쿼리>
select ~
from ~
where 컬럼 = (select ~
from ~
where ~) <---- 서브쿼리
select ~
from (select ~
from ~
where ~ ) <---- 인라인 뷰
select ~, (select ~ from ~ where ~) <---- 스칼라 서브쿼리
from ~
where ~
//////////////////////////////////////view (뷰)
<뷰 생성하기>
create view 뷰이름
as SELECT ~~
FROM ~~
WHERE ~~
예시 ) employees, departments, job_grades 테이블을 조인한 뷰 테이블을 만드시오
create view emp_info_view
AS
select e.employee_id ID, e.first_name || ',' || e.last_name full_name,
e.salary,
e.salary * 12 annual_salary,
e.department_id dept_id,
d.department_name dept_name,
j.gra salary_grade
from employees e, departments d, job_grades j
where e.department_id = d.department_id
and e.salary >= j.lowest_salary
and e.salary <= j.highest_salary
with read only; -- 읽기 전용으로 한다는 뜻
select *
from emp_info_view; -- 하면 테이블이 보인다.
<뷰의 삭제>
drop view 뷰이름;
뷰는 데이터를 포함하고 있지 않기 때문에 삭제하더라도 데이터는 지워지지 않는다.
예시 ) emp_info_view 삭제하기
drop view emp_info_view;
/////////////////////////////////////////////인라인 뷰 작성하기, Top-N, 분석함수
인라인 뷰는 Top-N분석을 할때 많이 사용된다.
인라인 뷰를 작성할때는 별칭명이 컬럼명이 된다, 특히 연산식일경우 꼭 별칭을 줘야한다.
예시) 부서별 사원수를 조회하기
select A.dept_id, B.department_name, A.cnt
from (select department_id dept_id, count(*) cnt
from employees
group by department_id) A, departments B
where A.dept_id = B.department_id;
예시2 ) Top-N 분석하기, 급여를 가장 많이 받는 직원 3명 조회하기
select rownum, employee_id, first_name, salary
from (select employee_id, first_name, salary
from EMPLOYEES
order by salary desc)
where rownum < 4;
예시3) 가장 최근에 입사한 직원 5명 조회하기
-- 사원아이디, 이름, 부서명, 입사일 조회하기
select rownum, 사원아이디, 이름, 부서명, 입사일
from ( select rownum, e.employee_id 사원아이디, e.first_name 이름, d.department_name 부서명, e.hire_date 입사일
from employees e, departments d
where e.department_id = d.department_id
order by e.hire_date)
where rownum < 6;
예시4) Sundita와 같은 해에 입사한 사원중에서 급여를 가장 많이 받는 직원 3명을 구하기
select rownum, employee_id, first_name, salary, hire_date
from (select *
from EMPLOYEES
where to_char(hire_date, 'yyyy') = (select to_char(hire_date,'yyyy')
from EMPLOYEES
where first_name = 'Sundita')
order by salary desc)
where rownum < 4;
예시5) -- 분석함수 사용하기 (순번을 한번에 매기는것 중복x)
select employee_id, first_name, salary, row_number() over (order by salary desc)
from employees;
예시6) Rank 사용하기 (순위를 매기는것)
select employee_id, first_name, salary, rank() over (order by salary desc)
from employees;
예시7) dense_ranke() 사용하기 (행마다 순위를 부여한다, 같은 순위 다음 순위로 Rank는 건너뛰는데 다르다)
select employee_id, first_name, salary, dense_rank() over (order by salary desc)
from employees;
예시8) rownum 과 row_number() over () 과 비교하기
--급여순으로 정렬했을 때 11위~15위 사이에 속하는 직원 조회하기
select rn,employee_id, first_name, salary
from (select rownum rn, employee_id, first_name, salary -- 급여순으로 정렬된 결과에 순번 부여
from (select employee_id, first_name, salary -- 급여순으로 내림차순 정렬
from employees
order by salary desc))
where rn >=11 and rn<= 15;
--급여순으로 정렬했을 때 11위 ~ 15위사이에 속하는 직원조회하기
select rn, employee_id, first_name, salary
from (select ROW_NUMBER() over (order by salary desc) rn, employee_id, first_name, salary
from employees)
where rn >=11 and rn<=15;
// 값은 위 아래 같으나 아래의(row_number() over)가 훨씬 효율적이다.