뷰 (View)

<정의 >

1. 가상테이블, 저장된 쿼리 

2. 실제 테이블이 아니라 쿼리문을 저장하고 있어 테이블과 같이 쿼리문을 통해 조회

 

 

 

<사용 이유> 

1. 원하는 컬럼, 레코드를 사전에 만들어 쿼리로 저장

2. 복잡한 Join문 등을 단일 테이블을 사용하듯이 간단한 쿼리로 사용가능하다

3. 실질적 테이블의 내부 구조를 숨길 수 있다. (보안에 유리)

4. 일반적으로 view는  read only

 

 

(편리성 -  서브쿼리를 사용한 결과와 동일)

 -뷰

select *
from vw_emp20;

 -서브쿼리

SELECT *
FROM (SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20);

 

< 사용 방법>

 

-   원본 테이블 만들기 (복사되는 테이블은 제약조건이 안 넘어온다)

 

create table dept_cop
as
select * from dept;


create table emp_copy
as
select * from emp;

 

1. 뷰 생성 ( 뷰를 만들기 위해서는 마스터 아이디에게 권한을 부여받아야 처리가능)

 

- 형식

create or replace view 뷰테이블명([alias])
as
서브쿼리(select)
[with check option]
[with read only]

- or replace : 같은 이름의 뷰가 이미 존재할 경우 현재 생성할 뷰로 대체하여 생성

- with check option : 지정한 제약조건을 만족하는 데이터에 한해 DML 작업이 가능하도록 뷰 생성

- with read only : 뷰의 열람(select)만 가능하도록 뷰 생성

 

 

-예시 (뷰에 셀렉된 컬럼만 조회가 가능하다)

create or replace view emp_view30
as
select empno,ename,sal,deptno from emp_copy
where deptno = 30;

 

1-1 권한 부여 방법 (권한 부여 후 삭제되기 이전까지 권한 가지고 있음)

 

cmd

sqlplus system/oracle
grant create view to scott;

 

 

2. 생성한 뷰 확인

select *
from user_views;

 

3. 뷰 삭제

drop view vw_emp20;

 

4. 데이터 삽입

select * 
from emp_view30;

insert into emp_view30
values(1111,'hong',1000,30); 

insert into emp_view30(empno,ename,sal)
values(2222,'hong',1000);  --deptno가 null로 추출된다.

insert into emp_view30(empno,ename,sal,deptno)
values(2222,'hong',1000,50);  --deptno가 null이므로 추출이 안된다.


select * 
from emp_copy;

 

***기본 데이터에만 추가가 된다???????

 

 

 

< 복합뷰 >

create or replace view emp_view(사원번호,사원명,급여,부서번호)
as
select empno,ename,sal,deptno
from emp_copy;

select * from emp_view;

select * 
from emp_view
where 부서번호 = 20;
--where deptno = 20;  --error : 별칭을 한번 주고나면 그 뒤로는 원래 컬럼명으로 사용 불가하다.

create or replace view emp_dept_view
as
select empno,ename,sal,e.deptno,dname,loc
from emp e inner join dept d
on e.deptno = d.deptno
order by empno desc;

select *
from emp_dept_view;

 

 

- 부서별 최소급여와 최대급여

create or replace view sal_view(dname,min_sal,max_sal)
as
select dname,min(sal) min_sal, max(sal) max_sal
from emp e inner join dept d
on e.deptno = d.deptno
group by d.dname;

select * from sal_view;

 

- 모든 객체의 이름은 중복될 수 없다 (replace)

create or replace view sal_view
as
select dname,min(sal) min_sal ,max(sal) max_sal,round(avg(sal),0) as avg_sal
from emp e inner join dept d
on e.deptno = d.deptno
group by d.dname;

 

<with check option>

조건절의 컬럼을 수정하지 못하게 한다 

create or replace view view_chk30
as 
select empno, ename, sal,comm, deptno
from emp_copy
where deptno = 30 with check option;

select * from view_chk30;

 

- update 오류 발생

 

<with read only>

모든 컬럼에 대한 C U D 가 불가능(조회만 가능)

 

create or replace view view_read30
as
select empno,ename,sal,comm,deptno
from emp_copy
where deptno = 30 with read only;

- update 오류 발생

 

 

<인라인뷰>

• 서브쿼리가 FROM 절에서 안에서 사용되는 경우, 해당 서브쿼리를 '인라인뷰'라고 한다.
• WITH 절에서 미리 이름을 정의해 두고 사용하는 select 문

 

-인라인 뷰 활용 예시

select rm, b.*
from  ( 
            select rownum rm, a.*
            from (select empno,ename,hiredate
                       from emp
                       order by hiredate asc
                       )a
           )b           
where rm >= 2 and rm <= 4;

 

-인라인 뷰로 입사일이 가장빠른 사원5명 추출하기

 select rownum,empno,ename,hiredate
            from( 
                       select empno,ename,hiredate
                       from emp
                       order by hiredate asc
                     )a
where rownum <= 5 ;

 

 

<인라인 뷰로 TOP-N 추출>

Top N 쿼리 ?

상위 n개의 데이터를 추출하는 쿼리

 

-인라인 뷰 (서브쿼리 활용)

SELECT ROWNUM, E.*
	FROM (SELECT *
    		FROM EMP E
          ORDER BY SAL DESC)E
WHERE ROWNUM <= 3;

 

-인라인 뷰 (WITH절 활용)

WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
FROM E
WHERE ROWNUM <= 3;

 

 

<ROWNUM 낮은순부터 조회>

ROWNUM?

• 데이터가 저장되는 실제 테이블에 존재하지는 않지만 특정 목적을 위해 테이블에 저장되어있는 열처럼 사용 가능
• 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호

 

 

- 입사일이 가장 빠른 5명의 사원조회

select * from emp
order by hiredate asc;

select * from emp
where hiredate <= '81/05/01' ;   

DESC emp;

 

- 뷰의 활용 예시 1)              

select rownum,empno,ename,hiredate
from emp
where rownum <= 5;

select rownum,empno,ename,hiredate
from emp
order by hiredate asc;  --rownum이 순서대로 정렬되지 않는다.

 

- 뷰의 활용 예시 2)              

create or replace view view_hiredate
as
select empno,ename,hiredate
from emp
order by hiredate asc;

select * from view_hiredate;

 

- 2)의 rownum 2~4만 출력하기

select rownum,empno,ename,hiredate
from view_hiredate  --rownum도 순서정렬되고 입사일도 순서정렬된다.
where rownum between 2 and 5;  --출력 실패 : rownum을 조건절에 직접 사용시 반드시 1을 포함하는 조건식을 만들어야 한다.

create or replace view view_hiredate_rm
as
select rownum rm ,empno,ename,hiredate  --rownum rm 별칭을 만들어 다시 테이블 생성한다
from view_hiredate;

select rm,empno,ename,hiredate
from view_hiredate_rm;

select rm,empno,ename,hiredate
from view_hiredate_rm
where rm >= 2 and rm <= 4;