[Oracle/기본]12. 뷰 생성

● 데이터베이스 객체

 

- Table : 행과 열로 구성된 기본적인 저장매체의 단위

- View : 하나 이상의 테이블로부터 데이터의 부분집합을 논리적으로 표현

- Sequence : 기본 키 값을 발생

- Index : 어떤 질의의 성능을 향상

- Synonym : 객체에 대체 이름을 부여

 

● 뷰 란?

 

테이블 또는 다른 뷰를 기초로 하는 논리적 테이블이다.

뷰는 그 자체로서 소유하는 데이터는 없지만 창문처럼 창문을 통해 어떤 데이터를 보거나 변경할 수 있다.

 

● 뷰의 사용 목적

 

1. 뷰는 데이터베이스의 선택적인 부분을 디스플레이 할 수 있기 때문에 데이터베이스에 대한 액세스를 제한할 수 있다.

2. 사용자가 복잡한 질의로부터 결과를 검색하기 위한 단순한 질의를 만들도록 한다. 예를들면, 뷰는 조인 문장을 작성하는

   방법을 알 필요 없이 다중 테이블에서 정보를 질의 할 수 있도록 해준다.

3. ad hoc 사용자와 어플리케이션 프로그램에 대한 데이터 독립성을 제공한다. 하나의 뷰는 여러 개의 테이블로부터

   데이터를 검색하는데 사용될 수 있다.

   * ad hoc : 애드혹 네트워크 / ad hoc network , "특별한 목적을 위한"

4. 특정 조건에 따라서 데이터를 액세스하는 사용자 그룹을 제공한다.

 

● 뷰의 종류

 

- 단순 뷰

1. 오직 하나의 테이블에서만 데이터가 유래

2. 데이터 그룹 또는 함수를 포함하지 않음

3. 뷰를 통한 DML 수행 가능

 

- 복합 뷰 (subquery에서 다중 테이블간 join을 통해 생성.)

1. 다중 테이블에서 데이터 유래

2. 데이터 그룹 또는 함수를 포함할 수 있다.

3. 뷰를 통한 DML을 항상 허용하지 않음

 

● 뷰 생성

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

 

CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view[(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY]

 

OR REPLACE              이미 존재한다면 뷰를 다시 생성한다.

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

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

view                         뷰의 이름

alias                         뷰 질의에 의해 선택된 표현식에 대한 이름을 지정한다.

                               별칭의 수는 뷰에 의해 선택된 표현식의 수와 일치해야 한다.

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

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

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

WITH READ ONLY        뷰에서 수행될 수 있는 DML 작업이 하나도 없음을 확실히한다.

 

Ex. CREATE VIEW empvu10

     AS SELECT empno, ename, job

           FROM emp

          WHERE deptno = 10;

- 뷰를 생성하는 서브쿼리는 조인, 그룹, 서브쿼리를 포함하는 복잡한 SELECT구문을 포함할 수 있다.

- 뷰를 정의하는 서브쿼리는 ORDER BY 절을 포함할 수 없다. ORDER BY 절은 뷰에서 데이터를 읽을 때 지정된다.

- 이전에 부여된 객체 권한을 삭제하거나, 뷰를 재생성 또는 재부여 하지 않고 뷰의 정의를 변경하기 위해 OR REPLACE 옵션을

  사용할 수 있다.

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

  CREATE VIEW salvu30

  AS SELECT     empno EMPLOYEE_NUMBER, ename NAME, sal SALARY

        FROM     emp

       WHERE    deptno = 30; 

 

● 뷰 수정

 

OR REPLACE 옵션은 비록 이 이름이 이미 존재할지라도 뷰가 생성될 수 있도록 해주므로, 그 소유자에 대한

오래된 뷰 버전을 대체한다.

 

CREATE OR REPLACE VIEW empvu10

          (employee_number, employee_name, job_title)

AS SELECT empno, ename, job

      FROM emp

    WHERE deptno = 10;

 

● 복합 뷰 생성

 

CREATE VIEW dept_sum_vu

                   (name, minsal, maxsal, avgsal)

AS SELECT     d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

      FROM    emp e, dept d

     WHERE   e.deptno = d.deptno

 GROUP BY   d.dname;

 

● WITH CHECK OPTION 절 사용

 

- 뷰에 대한 DML 연산이 뷰의 조건을 만족할 때만 수행되도록 한다.

- 뷰를 통해 수행되는 INSERT와 UPDATE는 WITH CHECK OPTION 절이 있으면 뷰를 가지고 검색할 수 없는 행

  생성을 허용하지 않음을 명시한다.

 

  Ex. CREATE OR REPLACE VIEW empvu20

       AS SELECT *

             FROM emp

           WHERE deptno = 20

       WITH CHECK OPTION CONSTRAINT empvu20_ck;

 

만약 위의 예제에 deptno를 20 이외의 값으로 변경 하려고 하면 에러가 난다.

뷰는 deptno = 20인 조건밖에 볼 수 없기 때문이다.

 

● DML 연산 부정

   WITH READ ONLY 사용

 

● 뷰 제거

    DROP VIEW empvu10;

 

 

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

Ex. 복합 뷰 생성예제 추가.

 

모든 종업원에 대한 이름, 부서명, 급여와 급여등급을 기초로하여 SALARY_VU 뷰를 생성하십시오.

Employee, Department, Salary, Grade로 열을 레이블 하십시오.

(dept, emp, salgrade 테이블 이용)

 

 

 

CREATE VIEW salary_vu

AS SELECT a.ename AS employee

              ,b.dname AS department

              ,a.sal AS salary

              ,c.grade AS grade

      FROM emp a

              ,dept b

              ,salgrade c

    WHERE a.deptno = b.deptno

       AND a.sal BETWEEN c.losal AND c.hisal