[SQL] 12. 뷰(View)

뷰(View)

- 뷰(View)는 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블이다.

1. 뷰의 사용 목적

- 접근제어를 통한 자동 보안이 제공되므로 데이터베이스 액세스를 제한하기 위해 사용

- 복잡한 질의를 쉽게 만들어주기 때문에 사용자의 데이터 관리를 간단하게 해줌

- 같은 데이터를 다른 뷰로 나타낼 수 있으므로 같은 데이터에 대해 동시에 여러 사용자의 다른 응용프로그램 요구를 지원해줌.

2. 단순 뷰와 복합 뷰

특징	단순 뷰	복합 뷰
테이블 수	하나	둘 이상
함수 포함	없음	있음
데이터 그룹 포함	없음	있음
뷰를 통한 DML	있음	없음

*뷰에 관한 정보

SELECT * FROM USER_VIEWS;

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

1. 뷰 생성 권한

select * from user_role_privs; --현재 사용자의 롤

select * from user_sys_privs; -- 현재 사용자에게 주어진 권한

 

2. 뷰 생성

- CREATE VIEW 문장 내에서 서브쿼리 작성

- 서브쿼리는 조인 구문 포함 가능

- CREATE VIEW 절에서 열 별칭은 서브쿼리에서의 열과 같은 순서로 나열됨.

CREATE [OR REPLACE]
	[FORCE | NOFORCE]
VIEW view_name[(alias[, alias ...])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY]

1) 뷰 생성

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

select * from emp_view_dept60;

 

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

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

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

select * from emp_dept60_salary;

 

3. 뷰 질의

select * from user_views;

4. 뷰 수정

- CREATE OR REPLACE VIEW 절을 사용하여 뷰를 수정

- ALTER 사용X

CREATE OR REPLACE VIEW veiw_name...

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

desc emp_dept60_salary;

5. 복합 뷰 생성

- 두 개 이상 테이블로부터 값을 출력하는 뷰를 생성

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;

select * from emp_view;

 

6. 뷰 삭제

- 뷰는 데이터베이스에서 기본 테이블을 기반으로 하므로 데이터 손실 없이 삭제 가능

DROP VIEW view_name;

 

뷰를 이용한 DML 연산

1. 뷰에서 DML 연산 실행 규칙

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

- 뷰가 다음을 포함한다면 행 제거 불가능(그룹함수, GROUP BY, DISTINCT)

- 뷰가 다음을 포함한다면 데이터 수정 불가능(행제거불가표현, 표현식, ROWNUM)

- 뷰가 다음을 포함한다면 데이터 추가 불가능(행제거불가조건, 수정불가조건, NOTNULL)

create table emps as select * from employees;

 

1) 데이터 조작이 가능한 경우

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

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

delete from emp_dept60 where employee_id=104;
select * from emps where employee_id=104;

2) 행 제거가 안 되는 경우

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

delete from emp_dept60 where employee_id=60;

3) 데이터 수정이 안 되는 경우

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

update emp_dept60 set annual_salary=annual_salary*1.1
where employee_id=106;

 

4) 데이터 입력이 안 되는 경우

create or replace view emp_dept60
as select
employee_id,
first_name,
last_name,
email,
salary
from emps where department_id=60;

insert into demp_dept60
values(500,'JinKyoung','Heo', 'HEOJK',8000);

 

2. WITH CHECK POINT

- 뷰에 대한 DML연산이 뷰의 조건을 만족할 때만 수행하게 함

create or replace view emp_dept60
as select employee_id, first_name, hire_Date, salary, department_id
from emps
where department_id=60
with check option;

update emp_dept60
set department_id=10
where employee_id=105;

 

3. WITH READ ONLY

- DML 연산을 수행할 수 없게 함

- DML 수행 시 에러 발생

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;

delete from emp_dept60
where employee_id=105;

 

인라인 뷰(Inline View)

- SQL 구문에서 사용 가능한 별칭(상관 이름)을 사용하는 서브 쿼리

- 기본질의의 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;