뷰의 개념과 활용

/* view(뷰)
가상 테이블
  데이터를 물리적으로 갖지 않지만 논리적인 집합을 가짐
  테이블과 마찬가지로 select, insert, update, delete 명령이 가능
*/

/*
view의 활용
보안 관리를 위한 뷰
  보안등급에 맞추어 컬럼 및 범위 정하여 조회 가능
  연산 결과만 제공하고 알고리즘을 숨기기 위해 사용
  select list를 함수로 가공하여 update, insert를 못하도록 함
  테이블 명이나 컬럼 명을 숨기도록 함
  
사용편의성을 위한 뷰
  검색조건을 단순화하여 사용할 수 있도록 함(복접한걸 미리 만들어놓고 사용)
  조인을 단순화(미리 조인해서 만든 가상 테이블을 이용)
  사용자를 위한 컬럼명이나 테이블명 제공
*/

-- view 생성
create view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- 생성할 뷰의 컬럼명
as select employee_id, first_name, job_id, hire_date, department_id -- 실제 컬럼들
  from employees
  where job_id = 'ST_CLERK';
  

-- 없으면 생성하고 있으면 대체
create or replace view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- 생성할 뷰의 컬럼명
as select employee_id, first_name, job_id, hire_date, department_id -- 실제 컬럼들
  from employees
  where job_id = 'ST_CLERK';
  

-- 특정 컬럼을 함수로 넣어서 수정을 할 수 없도록!
create or replace view v_emp3(emp_id, first_name, job_id, hiredate, dept_id)
as select employee_id, nvl(first_name, null), job_id, hire_date, department_id
  from employees
  where job_id = 'SH_CLERK';
  
-- nvl 설정때문에 오류
update v_emp3
set first_name = 'Kim'
where first_name = 'Julia';

-- commission_pct가 null이면 0으로 처리
-- annual_sal을 구하는 연산을 숨기고 결과만 보여주는 것
create view v_emp_salary(emp_id, last_name, annual_sal) as
select employee_id, last_name, (salary+nvl(commission_pct, 0)*12)
from employees;

-- 역시 함수로 연산하여 나온 컬럼이기 때문에 수정 불가
update v_emp_salary
set annual_sal = 8000
where last_name = 'Hunold';


-- 뷰의 데이터 수정 다 막는 것
create view v_emp_readonly(emp_id, last_name, annual_sal) as
select employee_id, last_name, (salary+nvl(commission_pct, 0)*12)
from employees
with read only;

-- read only 옵션 때문에 수정 불가
update v_emp_readonly set last_name = 'Kim'
where last_name='Grant';


-- 복잡한 쿼리를 뷰로 만들어서 간단히 조회
create or replace view v_samp1 as
select employee_id, last_name, department_id, hire_date
from employees
where (salary + nvl(commission_pct, 0))*12 > 40000
  and department_id = 50
  and job_id = 'ST_CLERK'
  and sysdate - 365*5 > hire_date;
  
select * from v_samp1;


-- 사용자의 편의성을 위한 테이블명 제공
create view 사원 (사번, 이름, 부서번호, 입사일) as
select employee_id, first_name||' '||last_name, department_id, hire_date
from employees
where department_id=50;

select * from 사원;


-- join이 필요한 테이블을 단순화하여 조회
create view v_join(사번, 이름, 부서번호, 부서명, 입사일) as
select emp.employee_id, emp.first_name||' '||emp.last_name, emp.department_id, dept.department_name, emp.hire_date
from employees emp, departments dept
where emp.department_id = dept.department_id;

select * from v_join;