과정 목적

본 과정에서는 뷰 생성과 사용에 대해 배웁니다. 또한, 뷰에 대한 정보를 읽어 들이기 위해 관련 데이터 사전 객체를 질의하는 것을 배우게 됩니다.

 

뷰란?

테이블의 뷰를 생성함으로써 데이터 조합 또는 논리적 부분집합을 나타낼 수 있습니다. 뷰는 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블입니다. 뷰는 그 자체로서 소유하는 데이터 는 없지만 창문처럼 창문을 통해 어떤 데이터를 보거나 변경할 수 있습니다.

 

뷰의 장점

보다 자세한 정보를 알고자 한다면, 다음을 참조하십시오. Oracle Server SQL Reference, Release 7.3 또는 8.0, “CREATE VIEW.”

단순 뷰와 복합 뷰

뷰는 2가지 종류 즉, 단순 뷰와 복합 뷰가 있습니다. 근본적인 차이점은 DML(삽입, 갱신, 삭제) 작업에 관련되어 있습니다.

 

뷰 생성

CREATE VIEW 문장에서 서브쿼리를 내장하여 뷰를 생성할 수 있습니다.

구문에서:

OR REPLACE

이미 존재한다면 뷰를 다시 생성합니다.

FORCE

기본 테이블의 존재 여부에 관계없이 뷰를 생성합니다.

NOFORCE

기본 테이블이 존재할 경우에만 뷰를 생성합니다.

view

뷰의 이름입니다.

alias

뷰 질의에 의해 선택된 표현식에 대한 이름을 지정합니다.  별칭의 수는 뷰에 의해 선택된 표현식의 수와 일치해야  합니다.

subquery

SELECT 문장을 완성합니다. SELECT 리스트에서 열에 대한 별칭을 사용할 수 있습니다.

WITH CHECK OPTION

단지 뷰에 의해 액세스 가능한 행만이 삽입, 갱신될 수 있음을 명시합니다.

constraint

CHECK OPTION 제약 조건에 대해 지정된 이름입니다.

WITH READ ONLY

뷰에서 수행될 수 있는 DML 작업이 하나도 없음을 확실히 합니다.

뷰 생성 (계속)

위의 예는 부서 10의 모든 종업원에 대한 종업원 번호, 이름, 업무명을 포함하는 뷰를 생성합니다.

SQL*Plus DESCRIBE 명령어를 사용하여 뷰의 구조를 디스플레이 할 수 있습니다.

 Name                            Null?    Type
 ------------------------------- -------- ------------
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)


뷰 생성 지침

 

뷰 생성 (계속)

서브쿼리에서 열 별칭을 포함함으로써 열 이름을 제어할 수 있습니다.

위의 예는 부서 30에 대해 EMPLOYEE_NUMBER 별칭을 갖는 종업원 번호, NAME 별칭을 갖는 이름, SALARY 별칭을 갖는 급여를 포함하는 뷰를 생성합니다.

또는, CREATE VIEW 절에서 열 별칭을 포함하여 열 이름을 제어할 수 있습니다.

 

뷰에서 데이터 검색

임의의 테이블에서 할 수 있는 것처럼 뷰로부터 데이터를 검색할 수 있습니다. 전체 뷰의 내용을 디스플레이 하거나, 단지 특정 행과 열만을 볼 수 있습니다.

 

데이터 사전의 뷰

일단 뷰가 생성되면, 뷰의 이름과 뷰 정의를 보기 위해 USER_VIEWS 라는 데이터 사전 테이블을 질의할 수 있습니다. 뷰를 만드는 SELECT 문장의 텍스트는 LONG 열에 저장됩니다.

데이터 액세스 뷰

뷰를 사용하여 데이터를 액세스 할 때 오라클 서버는 다음 작업을 수행합니다:
1. USER_VIEWS 데이터 사전 테이블에서 뷰 정의를 검색합니다.
2. 뷰 기반 테이블에 대한 액세스 권한을 확인합니다.
3. 뷰 질의를 기본 테이블 또는 테이블들에서의 동등한 작업으로 전환합니다. 달리 말하면, 데이터는 기본 테이블에서 검색되거나, 기본 테이블의 데이터에 갱신을 합니다.

 

뷰 수정

OR REPLACE 옵션은 비록 이 이름이 이미 존재할지라도 뷰가 생성될 수 있도록 해주므로,  그 소유자에 대한 오래된 뷰 버전을 대체합니다.

주: CREATE VIEW 절에서 열 별칭을 지정할 때, 별칭은 서브쿼리의 열과 동일한 명령으로 나열됨을 명심하십시오.

복합 뷰 생성

위의 예는 부서명, 최소 급여, 최대 급여, 부서의 평균 급여의 복잡한 뷰를 생성합니다. 다른 이름이 뷰에 대해 지정됐음을 명심하십시오. 뷰의 어떤 열이 함수나 표현식에서 유래되었다면 별칭은 필수적입니다.

SQL*Plus DESCRIBE 명령어를 사용하여 뷰의 구조를 볼 수 있습니다. SELECT 문장을 생성하여 뷰의 내용을 디스플레이 합니다.

SQL> SELECT  *
  2  FROM    dept_sum_vu;

NAME              MINSAL    MAXSAL    AVGSAL
-------------- --------- --------- ---------
ACCOUNTING          1300      5000 2916.6667
RESEARCH             800      3000      2175
SALES                950      2850 1566.6667

 

뷰에서 DML 연산 수행

연산이 어떤 특정 규칙을 따른다면 뷰를 통해 데이터에 DML 작업을 수행할 수 있습니다.
다음 중 어느 것을 포함하지 않는다면 뷰에서 행을 제거할 수 있습니다:

 

뷰에서 DML 연산 수행 규칙 (계속)

이전 슬라이드와 다음 중 일부에서 언급된 임의의 조건을 포함하지 않는다면 뷰에서 데이터를 수정할 수 있습니다:

위의 임의의 조건을 포함하지 않고 디폴트 값없는 NOT NULL 열을 포함하지 않는다면, 뷰를 통해 데이터를 추가할 수 있습니다. 뷰를 통해 기본 테이블로 직접적으로 값을 추가할 수 있음을 명심하십시오.

보다 자세한 정보를 알고자 한다면, 다음을 참조하십시오.                                  
0racle8 Server SQL Reference, Release 7.3 또는 8.0, “CREATE VIEW.”

 

WITH CHECK OPTION 절 사용

뷰를 통해 참조 무결성 체크를 수행하는 것이 가능합니다. 또한 데이터베이스 레벨에서 제약 조건을 적용할 수 있습니다. 뷰는 데이터 무결성을 보호하기 위해 사용될 수 있지만, 사용은 매우 제한됩니다.
 
뷰를 통해 수행되는 INSERT와 UPDATE는 WITH CHECK OPTION 절이 있으면 뷰를 가지고 검색할 수 없는 행 생성을 허용하지 않음을 명시합니다. 그러므로 삽입되거나 갱신되는 데이터에 대해서 무결성 제약조건과 데이터 검증 체크를 허용합니다.

뷰가 선택하지 않은 행에 대해 DML 작업을 수행하려고 하면, 지정된 제약조건 명과 함께 에러가 디스플레이 됩니다.

 SQL> UPDATE empvu20
   2  SET    deptno = 10
   3  WHERE  empno = 7788;
 update empvu20
         *
 ERROR at line 1:
 ORA-01402: view WITH CHECK OPTION where-clause violation

주: 부서번호가 10으로 변경된다면 뷰는 더 이상 그 종업원들을 볼 수 없기 때문에 아무 행도 갱신되지 않습니다. 그러므로, WITH CHECK OPTION 절로 뷰는 부서 20 종업원만 볼 수 있고, 이 종업원들에 대한 부서번호가 뷰를 통해 변경되는 것을 허용하지 않습니다.

DML 연산 부정

WITH READ ONLY 옵션으로 뷰를 생성하여 뷰에서 DML 연산이 수행될 수 없게 합니다. 위의 예는 뷰에서 임의의 DML 연산을 하지 못하도록 EMPVU10 뷰를 수정합니다.

뷰에서 행을 제어하려고 하면 에러가 발생됩니다.

SQL> DELETE FROM  empvu10
  2  WHERE  employee_number = 7782;
DELETE FROM empvu10
            *
ERROR at line 1:
ORA-01752:Cannot delete from view without exactly one key-preserved table   

 

뷰 제거

뷰를 제거하기 위해 DROP VIEW 문장을 사용합니다. 이 문장은 데이터베이스에서 뷰 정의를 제거합니다. 뷰 삭제는 뷰가 만들어진 기본 테이블에는 영향을 미치지 않습니다. 그 뷰에 기초하여 만들어진 뷰 또는 다른 어플리케이션은 무효화 됩니다. 생성자 또는 DROP ANY VIEW 권한을 가진 사람만 뷰를 제거할 수 있습니다.

구문형식에서:

        view            뷰의 이름입니다.

 

인라인 뷰

SELECT 문의 FROM 절에 있는 인라인 뷰는 SELECT 문의 데이터 소스를 정의합니다. 다음 예제에서 인라인 뷰 b는 EMP 테이블에 있는 각 부서의 부서 번호 및 최고 급여에 대한 세부 사항을 반환하며 기본 질의의 WHERE a.deptno = b.deptno AND a.sal < b.maxsal 절은 해당 부서에서 최고 급여보다 적게 받는 모든 사원에 대한 사원 이름, 급여, 부서 번호 및 최고 급여를 표시합니다.

SQL> SELECT  a.ename, a.sal, a.deptno, b.maxsal

  2   FROM   emp a, (SELECT   deptno, max(sal) maxsal

  3                   FROM     emp

  4                   GROUP BY deptno) b

  5  WHERE   a.deptno = b.deptno

  6  AND     a.sal < b.maxsal;

 

  ENAME            SAL    DEPTNO    MAXSAL

  ---------- --------- --------- ---------

  CLARK           2450        10      5000

  MILLER          1300        10      5000

  ...

  TURNER          1500        30      2850

  JAMES            950        30      2850

10 rows selected.

“Top-N” 분석

Top-N 질의는 테이블에서 조건에 대한 최상위 레코드 n개 또는 최하위 레코드 n개를 표시하는 시나리오에 유용합니다. 이 결과 집합은 다른 분석에도 사용할 수 있습니다. 예를 들어, Top-N 분석을 사용하여 다음 유형의 질의를 수행할 수 있습니다.

 

“Top-N” 분석 수행

Top-N 질의는 다음에 설명된 요소와 함께 일관성 있는 중첩 질의 구조를 사용합니다.

“Top-N” 분석 예제  

슬라이드 예제는 EMP 테이블에서 최상위 소득자 세 명의 이름 및 급여를 표시하는 방법을 설명합니다. 서브 쿼리는 EMP 테이블에서 모든 사원의 이름 및 급여에 대한 세부 사항을 반환하여 급여의 내림차순으로 정렬합니다. 기본 질의의 WHERE ROWNUM <= 3 절로 인해 결과 집합 중 상위 레코드 세 가지만 표시됩니다.

다음은 인라인 뷰를 사용하는 Top-N 분석의 다른 예제입니다. 다음 예제에서는 인라인 뷰 E를 사용하여 회사의 최장기 근무 사원 네 명을 표시합니다.

SQL>  SELECT ROWNUM as SENIOR,E.ename, E.hiredate

  2  FROM  (SELECT ename,hiredate FROM emp

  3         ORDER BY hiredate)E

  4  WHERE rownum <= 4;

 

 SENIOR ENAME      HIREDATE

--------- ---------- ---------

        1 SMITH      17-DEC-80

        2 ALLEN      20-FEB-81

        3 WARD       22-FEB-81

        4 JONES      02-APR-81

Quick Quiz - SPL 12장

  1. CREATE OR REPLACE FORCE VIEW vw_emp
    AS SELECT * FROM employee ORDER BY ename;
    위의 문장이 정상적으로 실행되지 않는 이유는?

    A. employee table이 존재하지 않기 때문에
    B. View에서는 모든 columns을 조회할 수 없기 때문에
    C. ORDER BY 절을 사용했으므로
    D. FORCE 라는 keyword를 사용했으므로
    답
    O


  2. 다음 문장에 대한 설명 중 잘못된 것은?
    CREATE FORCE VIEW empvu45
    AS SELECT *
    FROM s_emp
    WHERE dept_id = 45
    WITH CHECK OPTION CONSTRAINT empvu45_ck;
    A. 이 view에는 dept_id 가 45인 row만 포함된다.
    B. 이 view의 data에 대해서는 dept_id 를 45 이외의 값으로 수정할 수 없다.
    C. 이 view는 data dictionary에 select 문장으로 저장된다.
    D. 이 view는 s_emp table이 존재하지 않으면 생성할 수 없다.
    답
    X 정답:D


  3. 다음 문장에서 error가 발생하는 곳은?
    A. CREATE VIEW view1
    B. AS
    C. SELECT empno, name, sal
    D. FROM emp
    E. ORDER BY deptno;
    답
    O


  4. 아래와 같이 생성한 VIEW에서 DEPTNO를 수정할 수 있는 DATA는?
    CREATE VIEW sal_vu
    AS SELECT last_name, salary, deptno
    FROM s_emp
    WHERE sal > 45000
    WITH CHECK OPTION;

    A. SMITH, 12000, 20
    B. BROWN, 55000, NULL
    C. LIZA, 40000, 30
    D. ALICE, 50000, NULL
    E. MIKE, 20000, 10
    답
    X 정답:BD