[DB] View

<VIEW>

=테이블또는 다른 뷰를 기초로 하는 논리적 테이블

view는 USER_VIEWS 딕셔너리에 저장된다. (select * from USER_VIEWS;)

사용 목적

1. 접근제한을 통한 자동 보안이 제공

→ 일부 정보를 공유할 때 테이블 자체에 읽기 권한을 준다면 모든 행, 열이 조회가 된다.(보안 취약)

view는 테이블의 일부 행, 열로 만들 수 있는데

이렇게 정의한 view만 공유한다면 자동으로 보안이 제공된다

 

2. join구문을 이용하여 view를 만들고

그 데이터로 부터 조회를 하면 훨씬 더 편리한다.

 

3. 같은 데이터를 다른 뷰로 나타낼 수 있으므로

같은 데이터에 대해 동시에 여러 사용자의 다른 응용프로그램 요구를 지원

 

단순 뷰, 복합 뷰

특징	단순뷰	복합뷰
테이블 수	1	2이상
함수 포함	X	O
데이터 그룹 포함	X	O
뷰를 통한 DML	O	X

 

<뷰 생성, 데이터 검색, 수정, 삭제>

뷰 생성 권한

=뷰 생성 권한이 있어야 뷰 생성 가능

 

USERS_ROLE로 뷰 생성권한 확인 가능

select * from user_role_privs;

 

USER_SYS_PRIVS사용자가에게 주어진 권한 조회

select * from user_sys_privs;

 

뷰 생성

=create view로 서브 쿼리 작성

--create view
create view emp_view_dept60
as select employee_id, first_name, last_name, job_id, salary
from employees
where department_id=60;

--check view
desc emp_view_dept60;

 

뷰 생성 서브쿼리에서 별칭 사용

=서브 쿼리에서 열 별칭으로 열 이름 제어할 수 있다.

 

예제

employee_id → empno

first_name+last_name → name

salary → monthly_salary

로 명칭을 변경한다.

create view emp_dept60_salary (empno, name, monthly_salary)
as select
	employee_id,
        first_name||' '|| last_name,
        salary
from employees,
where department_id=60;

 

뷰 질의

=뷰로부터 데이터 검색 가능

전체 뷰 내용 디스플레이 하거나, 특정 행과 열만 볼 수 있다

뷰 생성되면 뷰이름, 뷰정의를 보기 위해 USER_VIEWS(데이터 딕셔너리 테이블)을 질의할 수 있다.

 

뷰 수정

=or replace로 수정할 수 있다.

or replace를 추가하면 해당이름의 뷰가 이미 존재해도 뷰가 생성될 수 있게 해준다.

create or replace view 뷰이름....

create or replace view emp_dept60_salary
as select
	employee_id as empno,
    first_name||' ' ||last_name as name,
    job_id as job,
    salary
from employees
where edpartment_id=60;

 

복합 뷰 생성

두 개 이상 테이블 이용해서 뷰 생성

create view emp_view
as select
        e.employee_id as id,
        e.first_name as name, 
        d.department_name as department,
        j.job_title as job
    from employees e
    left join departments d on e.department_id=d.department_id
    join jobs j on e.job_id=j.job_id;

뷰 삭제

=데이터 손실 없이 뷰를 삭제할 수 있다.

뷰를 만든 사람이나 drop any view권한을 가진 사람만 뷰를 제거할 수 있다.

drop view 뷰이름;

 

<뷰를 이용한 DML 연산>

뷰에서 DML연산 실행 규칙

- 단순 뷰 - DML연산 수행 가능

- 뷰가 group by, 그룹 함수, distince를 포함하면 행 제거 불가

- 뷰가 rownum, 표현식으로 정의된 열, 위의 행 제거할 수 없는 조건 가지면 데이터 수정할 수 없다

- 뷰가 위의 행 제거와 수정할 수 없는 조건, 뷰에 의해 선택되지 않은 not null열이 기본 테이블에 있으면 데이터 추가 불가

 

1. 삭제가 안될 때

뷰가 다음을 포함하면 행 제거 불가(수정, 입력도 불가)

    그룹 함수

     group by 절

     distince 키워드

 

ex)안되는 경우

create or replace view emp_dept60
as select distinct * from emps where department_id=60;

 

2. 수정이 안되는 경우

뷰가 다음을 포함하면 데이터 수정 불가(입력도 불가)

    행 제거 할 수 없는 조건

    표현식으로 정의된 열

    rownum 의사열

 

ex) 안되는 경우

create or replace view emp_dept60
as select employee_id, first_name ||', '||last_name as name, salary*12 as anuunal_salary
from emps where department_id=60;

 

3. 입력이 안되는 경우

뷰가 다음을 포함하면 데이터 추가 불가

    행 제거할 수 없는 조건과 행 수정할 수 없는 조건

    뷰에 의해 선택되지 않은 not null 열이 기본 테이블에 있을 때

 

With Check Option

뷰에 의해 액세스 가능한 행만이 삽입 갱신될 수 있다.

→ 무결성 제약조건과 데이터 검증 체크 가능.

With Read Only

=뷰에서 수행할 수 있는 DML작업이 없음을 확실히 하는 것.

=view를 만들때는 뒤에 with read only를 넣어주는 것이 좋다

create or replace view emp_dept60
as select employee_id, first_name, hire_date, salary, department_id
from emps
where department_id=60
with read only;

<INLINE VIEW>

from절에 서브쿼리가 있는 것

서브쿼리를 from절에 사용해 하나의 테이블 또는 뷰처럼 사용할 수 있다.

뷰도 하나의 독립적인 select문이므로 from절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있다.

그래서 from 절에 오는 뷰를 인라인 뷰라고 한다.

 SELECT	row_number, first_name, salary
FROM (SELECT first_name, salary, row_number() OVER (ORDER BY salary DESC) AS row_number							
FROM employees)
WHERE row_number BETWEEN 1 AND 10;