'View' 태그의 글 목록

View

ORACLE 뷰(View)와 Inline 뷰 2017.09.08

ORACLE 뷰(View)와 Inline 뷰

2017. 9. 8. 11:38

[ ORACLE 뷰(View) & 인라인 뷰 ]

-- 뷰(VIEW)란?
    -- : 뷰는 Table과 유사하지만 테이블과 달리 물리적인 공간이 필요하지 않은 가상의 테이블이다.
    -- : 테이블과 마찬가지로 select,insert,update,delete 명령이 가능하다.
    
    -- 사용 이유)
      -- 1. [보안관리를 위한 뷰]
          --  : 보안등급에 맞추어 컬럼 및 범위를 정하여 조회할 수 있도록 제공함
            --  (내가 공개하고 싶은 데이터만 table에서 뽑아서 제공하기 위함)
          -- 연산결과만 제공하고 알고리즘을 숨기기 위해 사용
          -- select list를 함수로 가공하여 update, insert를 못하도록 함
          -- 테이블 명이나 컬럼 명을 숨기기 위함
          
      -- 2. [사용편의성을 위한 뷰]
          -- 검색 조건을 단순화하여 사용할 수 있도록 함
              -- 테이블간 복잡하게 뽑아와야할 것을 그런 결과로 뽑은 하나의 뷰로 만들어 제공하면
              -- 쉽게 사용자는 조회할 수 있다.(for End User)
          -- 조인을 단순화
          -- 사용자를 위한 컬럼명이나 테이블명 제공
          
    -- EX)
    
      -- Employees 테이블에서 필요한 몇몇 컬럼 데이터들만 "뷰"로 뽑아와 보자.
      -- 뷰 생성
      -- job_id가 ST_CLERK인 사람들의 정보를 이용한 뷰
      create view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정
        as
        select employee_id, first_name, job_id, hire_date, department_id
        from Employees
        where job_id = 'ST_CLERK';
        
        
      -- job_id가 SH_CLERK인 사람들의 정보를 이용한 뷰
      create view v_emp1(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정
        as
        select employee_id, first_name, job_id, hire_date, department_id
        from Employees
        where job_id = 'SH_CLERK';
      
      
      -- view를 이용한 조회
      select dept_id from v_emp1;
      
      
      -- view 제거
      drop view v_emp1;
      
      
      -- view 수정
      create or replace view v_emp(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정
        as
        select employee_id, first_name, job_id, hire_date, department_id
        from Employees
        where job_id = 'SH_CLERK'; -- 조건만 수정했음
        
      select * from v_emp;
      
      
      ---- [ view의 활용 ] ----
      -- 1. 보안상 목적
        -- 뷰를 만들 때 select문에서 함수를 써서 만들면 해당 뷰에 insert와 update를 할 수 없게 된다.
        -- 따라서, 별 의미없어보이지만 nvl함수를 써서 보안상 목적을 제공할 수 있다.(꼭 nvl일 필요는 없다.)
      create or replace view v_emp3(emp_id, first_name, job_id, hiredate, dept_id) -- view검색시 사용될 컬럼명 지정
        as
        select nvl(employee_id,null), nvl(first_name,null), job_id, nvl(hire_date,null), department_id
        from Employees
        where job_id = 'SH_CLERK'; 
        
      desc v_emp3;
      
      -- 변경 불가
      update v_emp3 set first_name = 'modify' where first_name='Julia';
      
      -- 연봉을 구하기 위한 긴 구문을 뷰를 이용해 편리하게 조회하는 예
      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;
      
      -- annual salary를 편리하게 조회할 수 잇다.
      select * from v_emp_salary;
      
      
      -- 뷰의 수정을 막는 조회용 with read only!
      -- 조회만 가능한 view 생성!(read only view)
      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; -- with read only하면 해당 뷰를 수정할 수 없게 설정한다. 
      
      desc v_emp_readonly;
      
      select * from v_emp_readonly;
      -- 변경 불가(조회만 가능한 read only view이기 때문)
      update v_emp_readonly set last_name = 'kim'
      where last_name = 'Grant';
      
      
      create view v_sample1
      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; -- 입사한지 5년이 넘은 사람 조건
      
      select * from v_sample1;
      
      
      create view 사원 (사번, 이름, 부서번호, 입사일)
      as
      select employee_id, first_name||' '||last_name, department_id, hire_date
      from employees
      where department_id = 50;
      
      select * from 사원;
      
      
      create view v_join(사번,이름,부서번호,부서명,입사일)
      as
      select emp.employee_id, emp.first_name || ' ' || emp.last_name, dept.department_id,
             dept.department_name, emp.hire_date
      from employees emp, departments dept
      where emp.department_id = dept.department_id (+);
      
      select * from v_join;

[ 인라인(Inline) 뷰(View) 란? ]

: select의 from절에 테이블이 바로 오는 것이 아니라, select절이 오는 방식

왜 사용할까?

-> 서브쿼리를 포함해 테이블 조인이 이루어질 경우 검색하는 필드명은 테이블.필드명처럼해서 길어지게 된다. 이게 서브쿼리를 통해

메인 쿼리로 올라갈 수록 그 길이는 점점 더 길어져 불편할 수 있는데

이때, from절에서 select문을 통해 추출한 필드명에 Alias를 줘서 간단하게 만들 수 있다.

이러한 목적 이외에도 예를들어, 전체 데이터에서 비교하는 것보다 rownum을 이용해서 10번째 튜플 정보만 가져와 그 중에서 조건을 따져 데이터를

가져온다면 비교하는 횟수가 줄어들게 할 수도 있다.

이때, rownum을 통해 가져오는 갯수를 제한하는 방식을 N-TOP 질의라고 한다.

EX) select 사원명, 부서명 from ( select e.ename AS 사원명, d.dName AS 부서명 from emp e, dept d where e.deptno = d.deptno

where rownum <= 10

order by e.sal

);

저작자표시 비영리

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE PL/SQL 개념 - (1) (0)	2017.09.11
ORACLE 시퀀스(sequence) (0)	2017.09.08
ORACLE 무결성 제약조건 (0)	2017.09.07
ORACLE DML( insert, update, delete, select ) (0)	2017.09.06
ORACLE DDL( create, drop, alter, truncate ) (0)	2017.09.06

PREV 1 NEXT

악덕고용주의 개발 일기

View

ORACLE 뷰(View)와 Inline 뷰

'스터디 > DB(ORACLE)' 카테고리의 다른 글

+ Recent posts

티스토리툴바