[ORACLE] VIEW(뷰)

이번엔 뷰를 알아 볼 것 이다.

---

 뷰(View) 
  자주 사용하는 쿼리가 여러 테이블을 조인해서 이뤄질 때 JOIN 하는 테이블 수가 늘어나면 질의문이 길고 복잡해지고 유지보수도 어려울 수 있다. 
이럴 때에는 미리 필요한 항목들만 각 테이블에서 추출해서 JOIN 한 뒤 이를 뷰 테이블로 저장해두고 나중에 일반 테이블처럼 사용할 수 있다. 뷰를 가상 테이블이라고 한다.
  뷰는 명령어와 스크립트로 생성할 수 있는데 JOIN 하는 과정을 스크립트를 만들어 두거나 STORED PROCEDURE를 사용해서 데이터베이스 서버에 저장해두고 수시로 꺼내서 수정해가면서 재사용할 수 있다. 스크립트는 파일 시스템에 저장되지만 STORED PROCEDURE는 데이터베이스에 저장되어 관리되므로 최초로 compile 한 이후로는 그냥 수정해서 실행하기만 하면 된다. 
  그리고 View는 제한된 정보(각 테이블에서 필요한 항목들만 꺼내서)로 새로운 테이블을 생성했기 때문에 내부의 다른 항목들이 보이지 않게 되므로 보안적인 측면에서 좋고, 관리자의 권한 위임(privilege delegation) 등에서도 부담이 없다.

  View에서  주의점할 점 

• a) 쿼리 시 맨 끝에 where read only를 붙이면 읽기 전용 뷰가 된다.
• b) view 테이블에서의 데이터 변경은 
  → 단순 뷰인 경우 하나의 테이블에서 필요한 항목들만 추출해서 만드는데 INSERT, UPDATE, DELETE가 자유로우며 (NOT NULL 컬럼 주의)
•  →복합 뷰는 여러 테이블들을 조인해서 필요한 항목들만 추출해서 만드는데 함수, UNION, GROUP BY 등을 사용하기 때문에 INSERT, UPDATE, DELETE가 불가능하다(조인만 사용한 복합 뷰인 경우 제한적으로 가능).
• c) 뷰 가상 테이블에서 데이터를 변경하면 뷰가 참조하고 있는 원본 테이블에서도 데이터가 변경된다!!!
• d) 그리고 view 테이블_명을 변경할 수 없다. 예를 들어서 empInfor_vw 뷰 테이블_명을 
  alter table empInfor_vw
  rename to empInfo; 식으로 empInfo로 뷰 테이블의 이름을 변경하지 못한다.
  
    ※예를 들어서 사원 번호를 입력하면 해당 사원의 employee_id, last_name, salary, department_name, city, state_province, country_name, region_name 정보를 얻는다고 해보자. 
  그러면 employees, departments, locations, countries, 그리고 regions 5개의 테이블을 조인해서 정보를 얻는데 이 과정을 스크립트로 만들 수 있다. 

---

●예제

VIEW
employees 테이블 하나에서 employee_id, last_name salary, hire_dste로 구성되는 가상 테이블 empINF_vw1을 생성
create or replace view empINF_vw1
AS select employee_id, last_name, salary, hire_date from employees;

select * from empINF_vw1
where rownum <=5;

create or replace view empINF_vw1
AS select employee_id, last_name, salary, hire_date from employees;

select * from empINF_vw1
where rownum <=5;

■복합 view■
employees.employee_id, employees.last_name, departments.department_name, locations.city, locations.state_province, region.region_id, countries.contry_name,
region_name으로 구성되는 가상 테이블 empINF_vw2를 생성
1) desc employees 식으로 해서 어느 항목이 어느 테이블에 들어 있는지 확인한다.
2) 각 테이블에서의 공통 항목이 무엇인지 확인
==>region_id : countries_regions, department_id: employees-department,
     location_id : departments-locations, country_id :countries-lacations이므로 5개의 테이블을 조인해 두어야 한다.

create or replace view empINF_vw2
AS select employee_id, last_name, department_name, city, state_province, country_name, 
region_name 
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON d.location_id = l.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id;

select last_name, department_name, city, country_name, region_name
from empINF_vw2
where employee_id = 120;

create or replace view empINF_vw2
AS select employee_id, last_name, department_name, city, state_province, country_name, 
region_name 
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON d.location_id = l.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id;

select last_name, department_name, city, country_name, region_name
from empINF_vw2
where employee_id = 120;

select view_name, text from USER_VIEWS;// 테이블에 뷰테이블 확인

employee_id, last_name, department_name, city country_id, region_name 항목만 추출해서 새로운 empINF_vw3뷰 테이블을 생성하시오
create or replace view empINF_vw3
AS select employee_id, last_name, department_name, city, country_id, region_name 
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON d.location_id = l.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id;

create or replace view empINF_vw3
as select employee_id, last_name, department_name, city, country_id, region_name
from employees e join departments d on e.department_id = d.department_id
                 join locations l on d.location_id = l.location_id
                 join countries c on l.country_id = c.country_id
                 join regions r on c.region_id = r.region_id;

create or replace view empINF_vw3
AS select employee_id, last_name, department_name, city, country_id, region_name 
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON d.location_id = l.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id;

create or replace view empINF_vw3
as select employee_id, last_name, department_name, city, country_id, region_name
from employees e join departments d on e.department_id = d.department_id
                 join locations l on d.location_id = l.location_id
                 join countries c on l.country_id = c.country_id
                 join regions r on c.region_id = r.region_id;

 

■뷰 데이터 변경■
update empINF_vw1
set last_name = 'KINGS'
where employee_id = 100;

commit;*/

select last_name from employees
where employee_id = 100; //뷰에서 변경하면 원본에서도 변경된다.

update employees
set last_name = 'Kingkong'
where employee_id = 100; 

select last_name from empINF_vw1
where employee_id = 100;//원본 테이블에서 변경해도 뷰에서도 변경된다.

update empINF_vw1
set last_name = 'KINGS'
where employee_id = 100;

commit;*/

select last_name from employees
where employee_id = 100; //뷰에서 변경하면 원본에서도 변경된다.

update employees
set last_name = 'Kingkong'
where employee_id = 100; 

select last_name from empINF_vw1
where employee_id = 100;//원본 테이블에서 변경해도 뷰에서도 변경된다.

■복합뷰 변경■
update emINF_vw2
set last_name = 'kingkong'
where employee_id = 100;

commit;

select last_name from empINF_vw2
where employee_id = 100; // 복합뷰에서도 테이블 변경해도 원본도 변경 된다.

insert into empINF_vw2 (employee_id, city, country_name) values('1200', 'Seoul', 'KOREA');//제약조건을 무시하고 넣을수는 없다.

select * from all_constraints
where table_name = 'empINF_vw2';// 복사하거나 따라 갔을때 제약조건이 다 들어가지 않는 경우도 있다.

예를 들어서 사원번호를 입력하면 해당사원의 employee_id, salary, department_name, city,정보를 얻는다고 해보자.
employees, departments, locations, countries, 그리고 regions 5개의 테이블을 조인해서 정보를 얻는데 이과정을 스크립트로 만들수 있다.


REM NAME : empINFO.sql
REM DESCRIPTION : show employee's information when employee_id is checked
REM DATE : 2022.06.10(yyyy.mm.dd)
REM EXECUTE : SQL > START empINFO.sql
REM UPDATE
REM

select employee_id, last_name, salary, department_name, city, state_province,
country_name, region_name
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON l.location_id = d.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id
where employee_id = &employee_id;

select employee_id, last_name, salary, department_name, city, state_province,
country_name, region_name
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON l.location_id = d.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id
where employee_id = &employee_id;

create or replace view empINFO_SA_vw
as select employee_id, first_name, last_name, email, phone_number,
hire_date, job_title, department_name
from employees e JOIN departments d ON d.department_id = e.department_id
JOIN jobs j ON e.job_id = j.job_id
where job_id LIKE 'SA%'; // 세일스 부서에 있는 사람만으로 뷰를 만들수 있다.

create or replace view empINFO_SA_vw
as select employee_id, first_name, last_name, email, phone_number,
hire_date, job_title, department_name
from employees e JOIN departments d ON d.department_id = e.department_id
JOIN jobs j ON e.job_id = j.job_id
where job_id LIKE 'SA%'; // 세일스 부서에 있는 사람만으로 뷰를 만들수 있다.

/*select employee_id, last_name, hire_date from employees
order by hire_date;*/

select ROWNUM, employee_id, last_name, hire_date from employees;

/* select employee_id, last_name, hire_date from employees
order by hire_date;

select rownum, employee_id, last_name, hire_date from employees; */

create table emp_hiredASC
AS select * from employees
order by hire_date;

select rownum, employ_id ,last_name, hire_date from emp_hiredASC
order by hire_date;

create or replace view emp_ALL_vw
AS select * from employees
order by hire_date;

select rownum, employee_id, last_name, hire_date from emp_ALL_vw
where rownum <=10;

select employee_id, last_name, hire_date, salary
from (select * from employees order by hire_date) emp_ALL_vw1
where rownum <=10;//서브 쿼리를 이용해 명칭을 정해줌(인라인 뷰)

/*select employee_id, last_name, hire_date from employees
order by hire_date;*/

select ROWNUM, employee_id, last_name, hire_date from employees;

/* select employee_id, last_name, hire_date from employees
order by hire_date;

select rownum, employee_id, last_name, hire_date from employees; */

create table emp_hiredASC
AS select * from employees
order by hire_date;

select rownum, employ_id ,last_name, hire_date from emp_hiredASC
order by hire_date;

create or replace view emp_ALL_vw
AS select * from employees
order by hire_date;

select rownum, employee_id, last_name, hire_date from emp_ALL_vw
where rownum <=10;

select employee_id, last_name, hire_date, salary
from (select * from employees order by hire_date) emp_ALL_vw1
where rownum <=10;//서브 쿼리를 이용해 명칭을 정해줌(인라인 뷰)