DB객체) STORED VIEW 객체

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

STORED VIEW

- 하나 이상의 테이블에서 원하는 데이터를 선택하여 보여주는 가상 테이블 (링크)

- view를 통해서 실제 테이블을 보는 것

- 다른 테이블 정보를 보여줄 뿐, 실제 데이터를 갖고 있진 않음

- 데이터에 대한 저장 공간을 차지 하지 않음

---

create view 하기 전에 해당 권한을 부여받아야 합니다.

(resource 롤에 create view가 포함 되어 있지 않음!)

 

create view 부여 (관리자 계정으로 접속)

grant create view to kh;

권한 부여를 했다면 다시 사용하려는 계정을 접속해줍니다.

 

employee의 일부 정보를 확인할 수 있는 view_emp_2 생성

create or replace → view가 존재하지 않으면 create / 존재한다면 replace

-- drop view view_emp_2;
create or replace view view_emp_2
as
select
    emp_id,
    emp_name,
    email,
    (select dept_title from department where dept_id = e.dept_code) dept_title,
    (select job_name from job where job_code = e.job_code) job_name,
    decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
from
    employee e;

as를 이용하여 employee에서 자주 사용하는 컬럼들을 추려내 view테이블을 만들었습니다.

create or replace는 view가 존재하지 않으면 create(생성) 해주고, 존재한다면 replace(수정) 해줍니다.

 

view_emp_2 뷰/테이블 조회

-- view_emp_2 테이블 조회
select
    *
from view_emp_2;

원하는 정보들만 추려내서 만들어진 것이 확인 됩니다.

 

view의 작동 원리

-- view의 작동 원리
select * from user_views;

출력된 결과 중 TEXT를 보면, view를 생성할 때 작성했던 쿼리의 내용과 동일하다는 것을 확인할 수 있습니다.

 

select
    *
from (
    select
        emp_id,
        emp_name,
        email,
        (select dept_title from department where dept_id = e.dept_code) dept_title,
        (select job_name from job where job_code = e.job_code) job_name,
        decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
    from
        employee e
);

 

view는 실제 데이터를 저장한 것이 아닌 위의 inlinve-view, 즉 쿼리를 저장한 것입니다.

그러다가 select * from view_emp_2; 를 했을 때 view_emp_2라는 자리가 view_emp_2에서 관리되고 있는 서브 쿼리를 해당 자리에 넣고 그 때마다 실행하는 것입니다. (위 코드 처럼)

 

inline-view와 비슷하다고 생각할 수 있지만,

stored view는 해당 쿼리를 저장해놨다가 view 이름으로 계속 호출해서 사용할 수 있습니다. (재사용 가능!)

---

따라서 민감한 정보를 제외한 정보들을 다른 사용자에게 보여줄 때도 유용할 것 같습니다.

 

chany 계정에게 employee의 제한된 정보 view_emp_2 조회 권한 부여

-- chany에게 employee의 제한된 정보 view_emp_2 조회 권한 부여
grant select on kh.view_emp_2 to chany;

 

-- chany 계정
-- kh.view.emp_2 조회
select * from kh.view_emp_2;

 

---

join view

-- join view
-- employee, job, department, location, nation -> view_emp_info_2
create or replace view view_emp_info_2
as
select
    e.emp_id, e.emp_name, e.email, e.phone,
    d.dept_title,
    l.local_name,
    n.national_name,
    j.job_name
from
    employee e 
        left join department d on e.dept_code = d.dept_id
        left join location l on d.location_id = l.local_code
        left join nation n on l.national_code = n.national_code
        left join job j on e.job_code = j.job_code;
        
select * from view_emp_info_2;

stored view는 재사용이 가능하기 때문에 복잡한 join들을 해놓은 view를 만들어놓고 필요에 따라서 가져다 쓸 수 있습니다.

 

select
    *
from
    view_emp_info_2
where
    dept_title = '인사관리부'
    and
    national_name = '한국';

이렇게 조인이 자주 일어나는 테이블들은 미리 join view를 해두고, 재사용하여 사용하면 편리할 것 같습니다.