View 가상테이블

View

 자주 사용하는 쿼리로 JOIN 하는 테이블 수가 늘어나면 질의문이 길고 복잡해지고 유지보수도 어려울 수 있다. 

 필요한 항목만 각 테이블에서 추출하여 JOIN하고, VIEW 테이블로 저장하면 일반 테이블 처럼 사용할 수 있다.

 이를 뷰를 가상 테이블이라고 한다.

뷰는 JOIN 하는 과정을 스크립트를 만들어 두거나 STORED PROCEDURE를 사용해서 데이트베이스 서버에 저장한다.

 

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로 뷰 테이블의 이름을 변경하기 못한다.

// ALTER TABLE [원본 테이블] RENAME TO [바꾸려는 테이블명];
  

// decode 함수를 이용해서 department_id(부서) 각 10,20,30,40 의 부서명과 지역명을 보여라
select department_id, decode(department_id,10,'ACCOUNTING',20,'RESEARCH',30,'SALES'  ,40,'OPERATION') 부서_명,
                  	  decode(department_id,10,'Seoul'     ,20,'Pusan'   ,30,'Incheon',40,'Kangwon') 지역명,
count(*) 사원수, round(avg(salary),2) 평균급여
from employees
group by department_id
// departments table 에서 department_name을 조인하거나,
// jobs table에서 location 조인할 필요가 없어진다.
// 이런 과정 없이 view를 이용하면 훨씬 더 편하다.

 

관리자 로그인 > create View 권한 부여

SQL> conn sys as sysdba 
// 암호 입력: ******* 연결되었습니다. 

SQL> show user; 
// USER은 "SYS"입니다 

SQL> grant create view to hr; 
// 권한이 부여되었습니다. 

SQL> commit; 
// 커밋이 완료되었습니다. 

SQL> conn hr/rootoor 
// 연결되었습니다.

 

뷰 생성

 

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_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 location l  ON d.location_id   = l.location_id
KOIN countries c ON l.country_id    = c.country_id
JOIN region r    on c.region_id     = r.region_id;
// View table 생성

SELECT last_name, department_name, city, country_name, region_name
FROM empINF_vw2
WHERE employee_id = 123;

DESC empINF_vw2;

SELECT view_name, text FROM USER_VIEWS;
// 어떤 view table이 있는지 확인

 

 

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 employees e
JOIN departments d ON e.department_id = d.department_id
JOIN location 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

DESC empINF_vw3;

// 기존 JOIN과는 조금 다르게, from 다음 table이 기준이 되기 보다는,
// ON에서 서로 가지고 있는 column끼리 아무 table에서 맞추면 된다.

 

View table UPDATE (뷰테이블 수정)

 

UPDATE empINF_vw1
SET last_name = 'KINGS'
WHERE employee_id = '100';

select last_name from empINF_vw1 where employee_id = '100';
select last_name from employees where employee_id = '100';
// View table, employees table 모두 'KINGS'로 변경.

UPDATE employees SET last_name = 'KingKong' WHERE employee_id = '100';

select last_name from empINF_vw1 where employee_id = '100';
select last_name from employees where employee_id = '100';
// View table, employees table 모두 'KingKong'변경.

// View table을 변경하면 원본 테이블도 같이 변경

 

** select * from all_constraints where table_name = 'empINF_vw2';

// 제약조건으로 인해 업데이트가 제대로 이루어지지 않을 때도 있다. **

 

스크립트

REM (REMARK : 주석)

& : 외부에서 입력

 

REM NAME : empINFO.sql
REM DESCRIPTION : show employees 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 location 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;
// C drive에 모든 파일 / empINFO.sql로 저장

 

// 다른 프로그램, cmd, sqlplus 등으로 hr 사용자 접속
SQL> @ C:\empINFO.sql
employee_id의 값을 입력하십시오: 100 
구 7: where employee_id = &employee_id 
신 7: where employee_id = 100

EMPLOYEE_ID LAST_NAME				  SALARY	 DEPARTMENT_NAME 
----------- ------------------------- ---------- ------------------------------ 
CITY 						   STATE_PROVINCE 
------------------------------ ------------------------- 
COUNTRY_NAME 							 REGION_NAME 
---------------------------------------- -------------------------
100 		kingkong				  24000 	 Executive
Seattle	 					   Washington 
United States of America 				 Americas

// 외부에서 employee_id를 가져올 수 있다.

 

INLINE VIEW (문장속에 들어간 View)

 

SELECT employee_id, last_name, hire_date, salary
FROM (select * from employees order by hire_date) emp_ALL_vw1
WHERE rownum <= 10;
// 둘 다 같은 값을 출력함.
CREATE OR REPLACE VIEW emp_ALL_vw1
AS SELECT * FROM employees
ORDER BY hire_date;