SQL View(View 생성시 다양한 옵션들)

View

view는 하나 이상의 테이블을 묶어서 만드는 가상의 테이블로, select 문을 저장한 객체를 말한다. view는 물리적인 데이터의 값을 가지고있지 않아 관리가 편하다. view에는 하나의 테이블로만 생성하는 단순 view와 하나이상의 테이블들을 가지고 view를 생성하는 복합뷰가 있으며, 이외에도 view를 생성할때 사용할 수 있는 다양한 옵션들이 존재한다.

 

 

view 생성 시 옵션

• or replace
• force / noforce
• view 이름(컬럼명1, 컬럼명2,...)
• with read only
• with check option

---

select * from emp;

복습에 사용할 기본테이블이 될 emp테이블을 먼저 불러준다. (오늘 복습에서 계속 사용할 예정...!)

 

view 생성하기

--view--


--view 생성--
create view v_emp
as select empno, ename, deptno, job
from emp;

--view불러오기--
select * from v_emp;

v_emp

view를 생성할때는 테이블 생성+복사의 형태와 비슷하다. create view 이름 의 형태로 view를 생성할 수 있으며, 생성한 view는 

일반적인 테이블을 조회할때와 똑같이 select문을 이용하여 조회한다.

 

 

or replace

--or replace--
create or replace view v_emp(사원번호, 사원이름, 부서번호, 직업)
as select empno, ename, deptno, job
from emp;

select * from v_emp;

--생성된 뷰 DML--
insert into v_emp values(7200, 'JIMI', 30, 'ANALYST');
update v_emp set 부서번호=10 where 사원이름='SMITH';
update v_emp set 부서번호=50 where 사원이름='SMITH';--에러!--

 

or replace는 이미 기존에 생성된 view이름을가지고 다시 view를 재정의 할때 사용한다. create or replace view 이름 의 형태로 생성하는데, 주의할점은 이렇게 view를 재생성 할경우 기존에 적었던 쿼리문이 없는경우에는 재생성 하기전의 view를 복구 할 수 없어, 주의하여 사용해야 한다.

 

또한 이렇게 제작된 view에 직접 DML구문들을 작성할 수 있는데 만약 view의 기본테이블에 참조키가 설정되어있다면 값을 삽입할때 제한이 걸린다.

update v_emp set 부서번호=50 where 사원이름='SMITH';--에러!--

이처럼 위의 쿼리문에서 가장 마지막 부분인 부서번호를 50번으로 바꾸려고하자 에러가난다. emp테이블이 참조하고있는 부서번호의 부모테이블인dept에 50번의 부서번호가 없기때문이다.

 

 

force / noforce

--FORCE--
select * from v_test;--존재하지 않는 테이블--

create force view v_test
as select member, name, leader
from v_test;

select * from v_test; --생성은 되나 조회는 안된다--

select * from v_test;

force은 기본테이블이 존재하지 않지만 view를 생성할 수 있도록 하는 옵션이다. create force view 이름의 형태로 사용하며,

컬럼의 이름과 테이블명 역시 존재하지 않는 가상으로 적어주어 만들어주면 된다. force의 경우에는 아직 기본이 될 테이블이 개발중인데 다른 팀에게 테이블을 공유해야할때 일단 view의 구조만 만들어두면 테이블 개발이 끝나자 마자 생성해둔 view가 자동적으로 실행되기때문에 바로 다른 팀에게 공유가 가능하다. noforce의 경우 따로 지정하지 않아도 view를 만들때 디폴트로 지정되어있는 옵션이다.

 

 

with read only

--읽기전용 뷰 생성--
create or replace view v_emp
as select * from emp
where job = 'CLERK'
with read only;

select * from v_emp;

--일기전용 view에 DML--
delete from v_emp where deptno = 30;

값 삽입을 시도하자 에러나 나며 삽입되지 않는다.

 

with read only의 옵션으로 view를 생성하게 되면 생성된 view에 DML구문을 작성할 수 없고, 오로지 조회만 가능하다. 또한 with read only옵션은 view를 생성할때 가장 마지막에 옵션명을 적어준다.

 

 

with check option

--with check option--
create or replace view v_emp
as select empno, ename, job, deptno from emp
where deptno=10
with check option;

select * from v_emp;

--생성된 view DML--
insert into v_emp values(7901, 'JIMI', 'CLERK', 10);
insert into v_emp values(7902, 'TIMI', 'CLERK', 20); --에러!--

with check option은 생성된view를 통해 값을 삽입하거나 업데이트할때 지정한 view의 조건에 만족하는지 확인한 후 조건에 부합하는 경우만 view에 값을 삽입하는 옵션이다. view생성시 where절을 통해 dept=10라는 조건을 넣어, 10이 아닌 부서의 직원 데이터는 삽입할 수 없도록했다. 위의 쿼리문에서 맨 마지막쿼리문을 보면 부서번호가 20번인 부서번호의 직원데이터를 삽입하려고 하자 에러가 나는걸 확인할 수 있다.

 

 

 

복합 View

--복합 view--

--복합 view생성을 위한 직원 취향 테이블 생성--
create table emp_favorit(
empno number
constraint empno_fk references emp(empno), 
movie varchar2(100),
food varchar2(100),
music varchar2(100)
);


insert into emp_favorit values(7369, '기생충', '떡볶이', '국내밴드 음악');
insert into emp_favorit values(7782, '미드소마', '삼겹살', '해외 메탈 락');
insert into emp_favorit values(7839, '지구를 지켜라', '짜장면', '클래식');
insert into emp_favorit values(7934, '보 이즈 어프레이드', '떡볶이', '국내 인디밴드');

select * from emp_favorit;
select * from emp;

복합view는 하나 이상의 테이블을 가지고 view를 생성하는 것을 말한다. 먼저, emp테이블과 복합뷰를 생성할 직원들의 취향을 조사한 테이블을 만들어 복합view를 생성해보자. 

 

--복합 view생성--
create or replace view v_emp
as select e.empno, e.ename, e.job, e.deptno, f.movie, f.food, f.music
from emp e, emp_favorit f
where e.empno=f.empno;

select * from v_emp;

복합view는 inner join의 형태과 비슷하다. 테이블 별칭을 사용하여 select절에 컬럼들을 구분하여 적어주고, from절에 사용할 테이블을 나열하여 주며, 이 두테이블을 연결시킬 조건을 적어 복합 view를 생성한다.

 

생성된 복합view를 보면 아예 다른 두 테이블이 합쳐져서 하나의 view로 생성된 것을 확인 할 수 있다.

 

 

---

학습일기

뷰...뷰..!!!! 그냥 뷰 생성하는건 엄청 쉬운데..ㅠㅠ 뷰를 생성할때 사용하는 옵션 하나하나 생각하며 뷰를 생성하려니 왜이렇게 오류가 자주 나는지...ㅠㅠㅠ 옵션 쓰는거에 신경쓰여서 바보같이 뷰 이름 적는걸 까먹고 왜...왜 오류지..다 맞는데...이러고 있었던...휴..

뷰는 옵션위주로 자주 복습해주어야겠다...ㅠ 생각보다 실무에서 뷰가 많이 사용된다고 해서 옵션부분만 완벽 이해 될때까지 복습을 계속 해줬다...ㅠㅠ 어렵지만 계속 써봐야겠다..!!