오라클 SQL - 뷰(VIEW) 사용하기

 

뷰(View)란 무엇인가?

 

• 하나 이상의 테이블에 있는 데이터를 논리적으로 처리하는 오브젝트이다.
• 실제 데이터를 갖지 않고 단지 select 문만 가지고 있다.
• 간접 access 한다.
• 즉 보여주고 싶은 데이터만 나열해서 보여줄 수 있다.
• create view 시스템 권한이 필요하다.
  
  create view 권한 확인하는 방법 :
  
  > select * from session_privs;

 

---

뷰의 기능 (간접 access)

 

{HR SESSION}

뷰 만들기 :

create view emp_20
as select * from employees where department_id = 20;

 

뷰 조회하기 :

select * from emp_20;

마치 employees 테이블의 데이터처럼 보여지고있다.

이것이 뷰의 기능이다.

 

그렇다면 이것이 table인지 뷰인지 구분하려면 어떻게 해야할까?

내가 만든 object 정보 확인하기 :

select * from user_objects;

---

뷰의 실행과정

 

뷰의 정보 확인하기 :

select * from user_views;

emp_20 의 TEXT를 복사해서 실행시켜보면

아래와 같은 결과가 나온다.

emp_20이라는 뷰가 가진 select문을 실행시킨 결과이다.

 

즉,

1) 입력한 오브젝트가 있는지 확인한다. (없으면 오류 발생)

2) user_objects에서 오브젝트 타입을 알아본다.

3) 오브젝트 타입이 뷰이면 user_views에서 해당 뷰를 찾는다.

4) 뷰가 갖고있는 select 문장을 가져온다.

5) 가져온 select 문장을 호출한다.

---

뷰 연습하기

 

만약 INSA 유저가 다 엑세스하면 안되고 뷰를 통해서만 볼 수 있다고 가정할때

{SYS SESSION}

INSA 유저에게 emp_20 뷰를 엑세스할 권한주기 :

grant select on emp_20 to insa;

 

{INSA SESSION}

내가 받은 object 권한 알아보기 :

select *from user_tab_privs;

 

제대로 엑세스가 되는지 확인하기 :

select * from hr.emp_20;

---

뷰 삭제와 수정

 

{HR SESSION}

1) 뷰 삭제

먼저 새로운 뷰를 만들어 보자 :

create view emp_sal
as select employee_id id, last_name||first_name name, salary*12 sal
from employees;

그 다음 뷰를 삭제하려면 :

drop view emp_sal;

 

2) 뷰의 수정

뷰는 수정할 수 있는 방법이 없다. 그래서 삭제와 생성을 다시 해야한다.

뷰 삭제와 생성을 한꺼번에 해주려면 :

create or replace view emp_sal
as select employee_id id, last_name||first_name name, salary*12 sal
from employees;

별칭은 이렇게도 사용이 가능하다.

create or replace view emp_sal(id, name, sal)
as select employee_id, last_name||first_name, salary*12
from employees;

확인하기
select * from emp_sal;

성공!!

 

잠깐!

만약 별칭을 쓰지 않는다면 어떻게 될까? :

create view emp_sal 
as select employee_id id, last_name||first_name name, salary*12 sal
from employees;

slect 문에 함수와 특수문자가 포함되어서 오류가 발생한다. (CITAS를 사용할때도 마찬가지이다.)

때문에 꼭 별칭을 사용하자.

---

뷰의 종류

 

1) 단순뷰

• 뷰안에 select 문이 하나인 경우
• 뷰안에 함수를 사용하지 않은 경우
• DML허용한다.(insert, update, delete 등등 가능 / 표현식이 있으면 delete만 가능)
  - 즉 뷰를 통해서 수정하면 실제 데이터도 수정이 된다.

 

2) 복합뷰 (단순뷰의 반대)

• 뷰안에 select 문이 여러개가 있는 경우
• 뷰안에 함수를 사용하는 경우
• DML 불허한다.

---

1) 단순뷰 연습 - 뷰를 통해서 데이터를 수정

수정전 :

select employee_id, salary
from employees where department_id = 20;

 

수정하기 :

update emp_20
set salary = salary * 1.1;

 

수정후 :

select employee_id, salary
from employees where department_id = 20;

rollback;

 

2) 복합뷰의 예시

create view dept_sum_sal 
as 
select d.department_name, e.sumsal
from (select department_id, sum(salary) sumsal
        from employees
        group by department_id)e, departments d
where d.department_id = e.department_id;

select * from dept_sum_sal;

 

---

뷰가 조회만 가능하도록 만들기

 

1) with read only 사용

create or replace view emp_20
as select * from employees where department_id = 20
with read only;

desc emp_20
select * from emp_20;

이렇게 만들면 수정이 안된다.

update emp_20
set salary = salary * 1.1;

 

2) 뷰에 체크키 걸기

create or replace view emp_20
as select * from employees where department_id = 20 ------> 체크키 조건식
with check option constraint emp_20_ck; ----> emp_20_ck이란 이름으로 체크키 만들기

update emp_20
set department_id = 10;

rollback;