[Oracle SQL] VIEW

/* 
    View (뷰) : table과 유사하며, 테이블처럼 사용한다
              : 데이터를 저장하기 위한 물리적 공간이 필요하지 않은 가상테이블
              : 데이터를 물리적으로 가지지 않지만 논리적인 집합을 가짐
              : 테이블과 마찬가지로 select, insert, update, delete 명령어가 가능하다
              : create을 사용해서 view를 생성함
              
    사용이유
        : 보안관리
            - 보안등급에 맞춰 컬럼 및 범위를 정해 조회하도록 함
            - 연산 결과만 제공하고 알고리즘을 숨기기 위해 사용
            - Selectlist를 함수로 가공하여 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';
 
 
 
select * from v_emp;
 
 
 
create view v_emp1(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 = 'SH_CLERK';
 
 
 
select * from v_emp1;
 
 
 
 
/* View 관리 */
 
select * from v_emp1;
 
 
 
/* View 삭제 */
 
drop view v_emp1;
 
desc v_emp1;
 
 
 
/* View 수정 */
 
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 = 'SH_CLERK';
 
select * from v_emp;
 
 
 
/* View활용 */
/* 
    보안 
    nvl (컬럼명,null) - 데이터를 수정할 수 없도록 함
*/
create 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';
 
 
select * from v_emp3;
 
 
 
/* 에러발생 */
 
update v_emp3 set first_name = 'kim'
where first_name = 'Julia'; 
 
select * from v_emp3;
 
 
 
update v_emp set first_name = 'kim'
where first_name = 'Julia'; 
 
select * from v_emp;
 
 
 
 
/* 연산과정 숨기는 방법 */
 
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;
 
 
select * from v_emp_salary;
 
 
 
 
/* read only : 데이터 수정 불가 */
 
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;
 
 
select * from v_emp_readonly;
desc v_emp_readonly;
 
 
 
 
/* 읽기전용이므로 수정 불가  */
 
update v_emp_readonly
set last_name ='kim'
where last_name='Grant';
 
 
 
/* 사용자 편의성 */
 
create view v_samp1 as
select employee_id, last_name, department_id, hire_date
from employees
where (salary+nvl(commission_pct,0))*12 > 30000
  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 table View */
 
create view v_join(사번, 이름, 부서번호, 부서명, 입사일) as
select emp.employee_id, emp. first_name ||' '||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;