[DBMS_Oracle] 뷰 3

뷰 3

1. 뷰를 사용하는 이유

1 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화시킬 수 있다. 

2 보안에 유리하다.

사용자마다 특정 객체만 조회할 수 있도록 권한을 부여를 할 수 있기에 동일한 테이블을 접근하는 사용자마다에 따라 서로 다르게 보도록 여러 개의 뷰를 정의해 놓고 특정 사용자만이 해당 뷰에 접근할 수 있도록 합니다. 

예를 들어 사원 테이블에 개인 적인 정보인 급여와 커미션은 부서에 따라 접근을 제한해야 합니다. 

급여나 커미션 모두에 대해서 인사과에서는 조회할 수 없도록 하고 경리과에서는 이 모두가 조회될 수 있도록 하지만 영업부서에서는 경쟁심을 유발하기 위해서 다른 사원의 커미션을 조회할 수 있도록 해야 합니다. 

2. 뷰의 종류

뷰는 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰(Simple View)와 복합 뷰(Complex View)로 나뉩니다.

(1) 실습 [단순 뷰에 대한 데이터 조작]

1) 단순 뷰에 대해서 DML 즉,INSERT/UPDATE/DELETE 문을 사용할 수 있음을 확인합시다. 

EMP_VIEW30 뷰에 데이터를 추가해 봅시다.

INSERT INTO EMP_VIEW30

VALUES(8000, 'ANGEL', 30);

SELECT * FROM EMP_VIEW30;

2) 단순 뷰를 대상으로 실행한 DML 명령문의 치리 결과는 뷰를 정의할 때 사용한 기본 테이블에 적용됩니다. 

SELECT * FROM EMP_COPY;

(2) 실습 [단순 뷰의 컬럼에 별칭 부여하기]

기본 테이블(EMP_COPY)의 컬럼 명을 상속받지 않고 한글화 하여 컬럼 명이 사원번호, 사원명, 급여, 부서번호로 구성되도록 합시다.

1) EMP_VIEW30 뷰에 데이터를 추가해 봅시다.

CREATE OR REPLACE

VIEW EMP_VIEW(사원번호, 사원명, 급여, 부서번호)

AS 

SELECT EMPNO, ENAME, SAL, DEPTNO 

FROM EMP_COPY;

2) EMP_VIEW 는 전체 사원에 대해서 특정 컬럼만 보여주도록 작성하였습니다. 다음과 같이 EMP_VIEW 를 SELECT 하면서 WHERE 절을 추가하여 30번 부서 소속 사원들의 정보만 볼 수 있습니다. 

SELECT * FROM EMP_VIEW

WHERE 부서번호=30;

EMP_VIEW 뷰는 칼럼에 별칭을 주게 되면 기본 테이블의 칼럼 명을 더 이상 상속받지 못하므로 30번 부서를 검색하기 위해서는 뷰를 생성할 때 준 칼럼 별칭(부서번호)을 사용해야 합니다. 

(3) 실습 [그룹 함수를 사용한 단순 뷰]

그룹 함수 SUM과 AVG를 사용해서 각 부서별 급여 총액과 평균을 구하는 뷰를 작성해 봅시다. 뷰를 작성하기 위해서 SELECT 절 다음에 SUM이란 그룹 함수를 사용하면 결과를 뷰의 특정 컬럼처럼 사용하는 것입니다. 

따라서 물리적인 칼럼이 존재하지 않는 가상 칼럼이기에 칼럼 명도 상속 받을 수 없습니다. 뷰를 생성할 때 가상 칼럼을 사용하려면 사용자가 반드시 이름을 따로 설정해야 한다는 것을 명심하기 바랍니다. 

1) 부서별 급여 총액과 평균을 구하기 위한 뷰를 생성해보도록 합시다. 

CREATE VIEW VIEW_SAL

AS

SELECT DEPTNO, SUM(SAL) AS "SalSum", AVG(SAL) AS "SalAvg"

FROM EMP_COPY

GROUP BY DEPTNO;

3. 단순 뷰

단순 뷰에 대해서 DML 명령어를 사용하여 조작이 가능하다고 하였습니다. 

하지만, 다음과 같은 몇 가지의 경우에는 조작이 불가능합니다.

1 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는 경우 INSERT 문이 사용 불가능합니다. 왜냐하면 뷰에 대한 INSERT 문은 기본 테이블에 NULL 값을 입력하는 형태가 되기 때문입니다. 

2 SAL*12와 같이 산술 표현식으로 정의된 가상 칼럼이 뷰에 정의되면 INSERT나 UPDATE가 불가능합니다. 

3 DISTINCT을 포함한 경우에도 DML 명령을 사용할 수 없습니다.

4 그룹 함수나 GROUP BY 절을 포함한 경우에도 DML 명령을 사용할 수 없습니다.

4. 복합 뷰

- 뷰를 사용하는 이유 중의 하나가 복잡하고 자주 사용하는 질의를 보다 쉽고 간단하게 사용하기 위해서라고 했습니다. 이를 살펴보기 위해서 사원 테이블과 부서 테이블을 자주 조인한다고 합시다.

- 사원 테이블과 부서 테이블을 조인하기 위해서는 다음과 같이 복잡한 SELECT 문을 매번 작성해야 합니다. 

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;

- 뷰를 사용하는 이유 중의 하나가 복잡하고 자주 사용하는 질의를 보다 쉽고 간단하게 사용하기 위해서라고 했습니다. 위에 작성한 조인문에 "CREATE VIEW EMP_VIEW_DEPT AS" 만 추가해서 뷰로 작성해 놓으면 “SELECT * FROM EMP_VIEW_DEPT; " 와 같이 간단하게 질의 결과를 얻을 수 있습니다. 

(1) 실습 [복합 뷰 만들기]

사원 테이블과 부서 테이블을 조인하기 위해서 복합 뷰를 생성해 봅시다. 

1) 다음은 사번, 이름, 급여, 부서번호, 부서명, 지역명을 출력하기 위한 복합 뷰입니다. 

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;

2) 뷰를 생성한 후, 이를 활용하면 복잡한 질의를 쉽게 처리할 수 있습니다.

SELECT * FROM EMP_VIEW_DEPT;

5. 뷰 삭제

- 뷰는 실체가 없는 가상 테이블이기 때문에 뷰를 삭제한다는 것은 USER_VIEWS 데이터 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것을 의미합니다. 

- 따라서 뷰를 삭제해도 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않습니다.

지금까지 생성한 뷰 중에서 VIEW_SAL을 삭제합시다.

DROP VIEW VIEW_SAL;

6. 인라인 뷰

- 사원 중에서 입사일이 빠른 사람 5명(TOP-5)만을 얻어 오는 질의문을 작성해 봅시다. 

- TOP-N을 구하기 위해서는 ROWNUM과 인라인 뷰가 사용됩니다. 인라인 뷰는 조금 후에 다루어 보도록 하고, 우선 ROWNUM 칼럼에 대해서 살펴보도록 합시다. 

- ROWNUM 칼럼은 DDL을 학습하면서 살펴보았지만 보다 자세히 살펴보도록 합시다. 

    

(1) 실습 [ROWNUM 컬럼 성격 파악하기]

1) 다음은 ROWNUM 칼럼 값을 출력하기 위한 쿼리문입니다. 

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM EMP;

2) 입사일이 빠른 사람 5명만(TOP-N)을 얻어오기 위해서는 일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그 중 일부의 데이터만 출력할 수 있도록 해야 하므로 ORDER BY 절을 사용하여 입사일을 기준으로 오름차순 정렬해 봅시다. 

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;

3) 이번에는 입사일을 기준으로 오름차순 정렬을 하는 쿼리문에 ROWNUM 칼럼을 출력해 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;

위 결과를 보면 입사일을 기준으로 오름차순 정렬을 하였기에 출력되는 행의 순서는 바뀌더라도 해당 행의 ROWNUM 칼럼 값은 바뀌지 않는다는 것을 알 수 있습니다. 

ROWNUM 칼럼은 오라클의 내부적으로 부여되는데 INSERT 문을 이용하여 입력하면 입력한 순서에 따라 1씩 증가되면서 값이 지정되어 바뀌지 않습니다. 

정렬된 순서대로 ROWNUM 칼럼 값이 매겨지도록 하려면 새로운 테이블이나 뷰로 새롭게 데이터를 저장해야만 합니다. 

(2) 실습 [뷰와 ROWNUM 칼럼으로 TON-N 구하기]

ROWNUM 칼럼의 성격은 파악했으므로 이제 뷰와 함께 사용하여 TOP-N을 구해봅시다. TOP-N은 일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그 중 일부의 데이터만 출력할 수 있도록 하여 구합니다. 

1) 입사일을 기준으로 오름차순 정렬한 쿼리문으로 새로운 뷰를 생성해 봅시다

CREATE OR REPLACE VIEW VIEW_HIRE

AS

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;

 

2) 입사일을 기준으로 오름차순 정렬을 하는 뷰에 ROWNUM 칼럼을 함께 출력해 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM VIEW_HIRE;

3) 자, 이제 입사일이 빠른 사람 5명만을 얻어와 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM VIEW_HIRE

WHERE ROWNUM<=5;

(3) 인라인 뷰로 TOP-N 구하기

- 인라인 뷰는 SQL 문장에서 사용하는 서브 쿼리의 일종으로 보통 FROM 절에 위치해서 테이블처럼 사용하는 것입니다. 형식은 다음과 같습니다.

- 인라인 뷰란 메인 쿼리의 SELECT 문의 FROM 절 내부에 사용된 서브 쿼리문을 말합니다. 

- 우리가 지금까지 생성한 뷰는 CREATE 명령어로 뷰를 생성했지만, 인라인 뷰는 SQL 문 내부에 뷰를 정의하고 이를 테이블처럼 사용합니다. 

1) 실습 [인라인 뷰로 TOP-N 구하기]

인라인 뷰를 사용해서 입사일이 빠른 사람 5명만을 얻어오기로 합시다. 아래 문장을 보면 FROM 절 다음인 VIEW_HIRE 위치에 VIEW_HIRE를 정의할 때 사용한 서브 쿼리문을 기술한 것뿐입니다. 

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM ( SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE)

WHERE ROWNUM<=5;