가상 테이블 뷰(VIEW)

가상 테이블 뷰(VIEW)

테이블에 근거한 논리적인 가상 테이블. 데이터를 저장하고 있지 않더라도 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있다.

CREATE [OR REPLACE] [{FORCE | NOFORCE}] VIEW view_name

[(alias, alias, alias,...)] -- 별칭 (생략 가능)

AS subquery -- SELECT문

[WITH CHECK OPTION] 

[WITH READ ONLY]; -- SELECT만 가능해진다.

뷰를 생성할 때엔 CREATE VIEW로 생성한다. AS 다음은 서브쿼리문과 유사하다. subquery에는 SELECT문을 기술하면 된다.

CREATE OR REPLACE VIEW

뷰의 구조를 바꾸려면 CREATE OR REPLACE VIEW로 뷰를 생성해야 한다. CREATE VIEW는 구조를 바꿀 수 없다.

FORCE | NOFORCE

FORCE를 사용하면 테이블 없이 뷰를 생성한다.

WITH CHECK OPTION

WITH CHECK OPTION을 사용하면 뷰를 통해 볼 수 있는 범위 내에서만 UPDATE, INSERT가 가능하다.

WITH READ ONLY

WITH READ ONLY를 사용하면 SELECT만 가능하며 INSERT, UPDATE, DELETE를 할 수 없다.

CREATE VIEW EMP_VIEW30

AS

SELECT EMPNO, ENAME, SAL, DEPTNO 

FROM EMP_COPY

WHERE DEPTNO = 30;

SELECT * FROM EMP_VIEW30;

EMP 테이블의 30번 부서만 보여주는 뷰가 만들어졌다.

1. 뷰를 사용하는 이유

- 위와 같이 EMP 테이블에서 사원번호, 사원명, 부서번호만 출력되게 하는 VIEW를 생성하면 VIEW만 사용할 수 있는 유저는 VIEW에서 볼 수 있는 정보 외의 정보는 볼 수 없다. (보안 목적)

2. 단순 뷰와 복합 뷰

- 단순 뷰 

하나의 테이블로 생성

그룹 함수의 사용 불가능

DISTINCT 사용 불가능

DML 사용 가능

- 복합 뷰 

여러개의 테이블로 생성

그룹 함수의 사용 가능

DISTINCT 사용 가능

DML 사용 불가능

1) 단순 뷰의 별칭

CREATE OR REPLACE 

VIEW EMP_VIEW(사원번호, 사원명, 급여, 부서번호) -- alias(별칭)

AS

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP_COPY;

SELECT * FROM EMP_VIEW;

2) 복합 뷰 만들기

CREATE VIEW EMP_VIEW_DEPT

AS

SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO -- 조인

ORDER BY EMPNO DESC;  -- 정렬

SELECT * FROM EMP_VIEW_DEPT;

3) 그룹함수를 사용한 단순 뷰

문제) 각 부서별 최대 급여와 최소 급여를 출력하는 뷰를 SAL_VIEW라는 이름으로 작성하라.

CREATE VIEW SAL_VIEW

AS 

SELECT D.DNAME, MAX(E.SAL) MAX_SAL, MIN(E.SAL) MIN_SAL

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

GROUP BY D.DNAME

SELECT * FROM SAL_VIEW;

3. 뷰 삭제

DROP VIEW view_name;

뷰는 실체가 없는 가상테이블이므로 뷰를 삭제해도 뷰를 정의한 테이블의 구조나 데이터에는 영향을 주지 않는다.

4. 뷰 생성에 사용되는 다양한 옵션

1) CREATE OR REPLACE VIEW

뷰의 구조를 바꾸려면 CREATE OR REPLACE VIEW로 뷰를 생성해야 한다. CREATE VIEW는 구조를 바꿀 수 없다.

2) FORCE | NOFORCE

FORCE를 사용하면 테이블 없이 뷰를 생성한다. 지정하지 않으면 뷰의 디폴트는 NOFORCE(테이블이 있어야만 생성)

3) WITH CHECK OPTION

WITH CHECK OPTION을 사용하면 뷰를 통해 볼 수 있는 범위 내에서만 UPDATE, INSERT가 가능하다.

4) WITH READ ONLY

WITH READ ONLY를 사용하면 SELECT만 가능하며 INSERT, UPDATE, DELETE를 할 수 없다.

CREATE OR REPLACE VIEW EMP_VIEW30

AS

SELECT EMPNO, ENAME, SAL, DEPTNO 

FROM EMP_COPY

WHERE DEPTNO = 30 WITH READ ONLY;

UPDATE EMP_VIEW30

SET SAL = 2000

SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view

42399.0000 - "cannot perform a DML operation on a read-only view"

5. 뷰 활용하여 Top-N 구하기

1) ROWID : 레코드를 랜덤하게 접근하기 위한 논리적인 주소값

2) ROWNUM : 각 행에 대한 일련번호

SELECT ROWNUM, ENAME, DEPTNO FROM EMP;

기본적으로는 ROWNUM 순으로 나온다.

SELECT ROWNUM, ENAME, DEPTNO FROM EMP

ORDER BY ENAME

ENAME으로 정렬하면 ROWNUM의 정렬 순서가 달라진 것이 확인 가능.

3) 인라인 뷰로 구하는 TOP-N의 개념

인라인 뷰 : SELECT문의 FROM절 내부에 사용된 서브 쿼리문. 인라인 뷰는 SQL문 내부에 뷰를 정의하고 테이블처럼 사용한다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM (

SELECT EMPNO, ENAME, HIREDATE -- 인라인 뷰

FROM EMP

ORDER BY HIREDATE

)

WHERE ROWNUM <= 10

문제) 급여를 많이 받는 순서대로 5명만 출력하는 SAL_TOP5_VIEW를 작성하라.

CREATE OR REPLACE VIEW SAL_TOP5_VIEW -- 뷰 생성

(RANK, EMPNO, ENAME, SAL) -- alias

AS

SELECT ROWNUM, EMPNO, ENAME, SAL

FROM (

SELECT EMPNO, ENAME, SAL FROM EMP_COPY -- 인라인 뷰

ORDER BY SAL DESC -- 급여 내림차순은 급여가 많은 것부터 출력된다.

)

WHERE ROWNUM <= 5 -- 5명만 출력