[오라클 DB] 뷰(VIEW)

 

뷰(VIEW)

 

뷰(VIEW)란, 간단히 말해 다른 릴레이션으로부터 유도된 릴레이션(Derived relation)이다.

같은 의미로 뷰는, 한 사용자의 전체 외부 뷰 대신에 하나의 가상 릴레이션(Virtual relation)을 의미한다.

 

기존의 기본 릴레이션(Base relation, 실제 릴레이션)에 대한 SELECT문 형태로 정의되며,

이는 뷰 = SELECT문 이라고 쉽게 이해할 수 있다.

 

뷰와 기본 릴레이션의 관계

 

 

뷰 정의

 

위에서 언급했듯, 뷰는 SELECT문 형태로 정의되며, 정의 방법은 다음과 같다.

 

CREATE OR REPLACE VIEW 뷰 이름 [(애트리뷰트(들))]
AS SELECT문
[WITH CHECH OPTION];

 

뷰의 이름 다음에 애트리뷰트들을 생략하면, SELECT문의 SELECT절에 열거된 애트리뷰트들의 이름과 동일한 애트리뷰트들이 뷰에 포함된다. 하지만 다음과 같은 경우엔 모든 애트리뷰트들을 포함한다해도 생략하지 않고 모든 애트리뷰트의 이름을 지정해주어야 한다.

 

1.  SELECT절에 산술식 또는 집단 함수에 사용된 애트리뷰트가 있는 경우

2. 뷰 정의에 두 개 이상의 다른 릴레이션으로부터 가져온 애트리뷰트들의 이름들이 같은 경우

 

 

이전에 생성한 뷰를 삭제하기 위한 SQL문은 다음과 같다.

 

DROP VIEW 뷰 이름;

 

 

뷰 예시

예제에 사용될 EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션 => 여기 클릭

 

한 릴레이션 위에서 뷰를 정의하는 예시는 다음과 같다.

Q. EMPLOYEE 릴레이션에 대해서 "3번 부서에 근무하는 사원들의 사원번호, 사원이름, 직책으로 이루어진 뷰'를 정의해보자.

 

3번 부서에 근무하는 사원들의 사원번호, 이름, 직책을 뷰로 표현하면 파란색 영역이 추출됨.

 

 

위 그림의 파란색 영역을 뷰로 추출하기 위한 SQL문은 다음과 같이 정의할 수 있다.

 

CREATE OR REPLACE VIEW EMP_DNO3 (ENO, ENAME, TITLE)
AS SELECT EMPNO, EMPNAME, TITLE
      FROM EMPLOYEE
      WHERE DNO = 3;

 

위 구문에서, SELECT절에 정의한 애트리뷰트가 뷰로 추출하고싶은 애트리뷰트와 같으므로 아래와 같이 생략하여도 결과는 동일하다.

 

CREATE OR REPLACE VIEW EMP_DNO3
AS SELECT EMPNO, EMPNAME, TITLE
      FROM EMPLOYEE
      WHERE DNO = 3;

 

 

---

 

두 릴레이션 위에서 뷰를 정의하는 예시는 다음과 같다.

Q. EMPLOYEE와 DEPARTMENT 릴레이션에 대해서 "기획부서에 근무하는 사원들의 이름, 직책, 급여로 이루어진 뷰"를 정의해보자.

 

위와 같은 경우, 두 릴레이션을 조인하여 하나의 릴레이션으로 만든 후 뷰를 추출하면 된다.

 

CREATE OR REPLACE VIEW EMP_PLANNING
AS SELECT E.EMPNAME, E.TITLE, E.SALARY
      FROM EMPLOYEE E, DEPARTMENT D
      WHERE E.DNO = D.DEPTNO AND D.DEPTNAME = "기획";

 

이 또한, SELECT절의 애트리뷰트들을 그대로 뷰에 지정할 것이기 때문에 애트리뷰트들을 따로 지정하지 않고 생략해주어도 된다.

 

 

---

 

뷰의 장점

 

그렇다면 뷰를 사용하는 이유는 무엇일까? 이유는 다음과 같다.

 

1. 복잡한 질의를 간단하게 표현할 수 있게 한다.

 

다음과 같은 예시를 보자.

 

Q. 기획부에 근무하는 사원들 중에서 직책이 부장인 사원의 사원 이름과 급여를 검색하라.

 

이에 대한 SQL문은 다음과 같이 정의할 수 있다.

 

SELECT E.EMPNAME, E.SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.DEPTNAME = '기획' AND D.DEPTNO = E.DNO AND E.TITLE = '부장';

 

하지만 다음과 같이 뷰를 정의했다고 가정해보자.

 

CREATE OR REPLACE VIEW EMP_PLANNING
AS SELECT E.EMPNAME, E.TITLE, E.SALARY
      FROM EMPLOYEE E, DEPARTMENT D
      WHERE E.DNO = D.DEPTNO AND D.DEPTNAME = '기획';

 

위 뷰는 부서명이 '기획'인 사원들의 이름, 직책, 급여를 EMP_PLANNING라는 뷰로 정의한 것이다.

 

위와 같이 뷰를 한 번 정의하면, 맨 처음 SQL문은 다음과 같이 간단하게 표현할 수 있게 된다.

 

SELECT EMPNAME, SALARY
FROM EMP_PLANNING
WHERE TITLE = '부장';

 

 

 

2. 데이터 무결성을 보장하는데 활용된다.

 

기본적으로 뷰를 통해 튜플을 추가하거나 수정할 때 튜플이 뷰를 정의하는 SELECT문의 WHERE절의 기준에 맞지 않으면 뷰의 내용에서 사라진다.

 

다음과 같은 예시를 보자.

 

CREATE OR REPLACE VIEW EMP_DNO3 (ENO, DNO, ENAME, TITLE)
AS SELECT EMPNO, DNO, EMPNAME, TITLE
      FROM EMPLOYEE
      WHERE DNO = 3;

 

위 뷰는 부서 번호가 3인 사원의 사원 번호, 부서 번호, 사원 이름, 직책을 EMP_DNO3라는 뷰로 정의한 것이다.

 

EMP_DNO3 뷰를 생성한 후, 다음과 같은 연산을 시도하면 어떻게 될까?

 

UPDATE EMP_DNO3
SET DNO = 2
WHERE ENO = 3427;

 

이 연산을 허용하는 순간, 사원 번호가 3427인 사원의 부서 번호(DNO)가 2로 변경되게 되며 이는 기존 릴레이션과 값이 일치하지 않아 무결성을 위반하게 된다. 이를 막기 위해 다음과 같은 구문을 추가해 줄 수 있다.

 

WITH CHECK OPTION

 

데이터 무결성을 보장하기 위해 뷰를 다시 정의하면 다음과 같이 정의할 수 있다.

 

CREATE OR REPLACE VIEW EMP_DNO3 (ENO, DNO, ENAME, TITLE)
AS SELECT EMPNO, DNO, EMPNAME, TITLE
      FROM EMPLOYEE
      WHERE DNO = 3
      WITH CHECK OPTION;

 

 

3. 데이터 독립성을 제공한다.

 

뷰는 데이터베이스의 구조가 바뀌어도 기존의 질의(응용 프로그램)를 다시 작성할 필요성을 줄이는데 사용될 수 있다.

 

다음 예시를 보자.

 

CREATE OR REPLACE VIEW EMPLOYEE
AS SELECT E1.EMPNO, E1.EMPNAME, E2.TITLE, E2.MANAGER, E1.SALARY, E2.DNO
      FROM EMP1 E1, EMP2 E2
      WHERE E1.EMPNO = E2.EMPNO;

 

EMPLOYEE 릴레이션이 EMP1(EMPNO, EMPNAME, SALARY)과 EMP2(EMPNO, TITLE, MANAGER, DNO)으로 분해되면 응용 프로그램에서 EMPLOYEE를 접근하던 SELECT문은 더 이상 수행되지 않는다.

 

하지만, 위 처럼 EMPLOYEE라는 뷰를 정의했다면 응용 프로그램에서 EMPLOYEE 릴레이션을 접근하던 SELECT문은 계속해서 수행될 수 있다.

 

 

4. 데이터 보안 기능을 제공한다.

 

뷰를 통해 데이터 접근 권한을 부여하고, 정교한 보안 매커니즘으로 사용할 수 있다.

 

예를 들어, EMPLOYEE 릴레이션의 SALARY 애트리뷰트는 숨기고 나머지 애트리뷰트들은 모든 사용자가 접근할 수 있도록 하고 싶다고 가정해보자.

이와 같은 요구를 이루기 위해 우리는 SALARY 애트리뷰트를 제외하고 EMPLOYEE 릴레이션의 모든 애트리뷰트를 포함하는 뷰를 정의하고, 사용자에게 뷰에 대한 SELECT 권한을 허가하는 방식을 채택할 수 있을 것이다.

 

비슷한 이유로 뷰는 동일한 데이터에 대한 여러 가지 뷰를 제공할 수 있다.

 

만약 EMPLOYEE 릴레이션의 모든 정보는 사장이 볼 수 있는 권한을 가지고 있고, 부장은 연봉을 제외한 모든 정보를 볼 수 있으며, 사원은 이름과 직책 이외의 모든 정보를 볼 수 없다면 각각의 권한에 맞는 여러가지 뷰를 생성하여 각 뷰에 대한 접근 권한을 부여할 수 있을 것이다.

 

 

---

 

뷰의 갱신

 

뷰에 대한 갱신도 기본 릴레이션에 대한 갱신으로 변환된다.

아래와 같은 갱신 예시들을 보며 설명을 이어나가겠다.

 

갱신 1 : 한 릴레이션으로 정의된 뷰에 대한 갱신

설명하기 앞서, EMPLOYEE 테이블을 생성할 때 사용된 정의어 구문은 다음과 같다.

 

EMPLOYEE 테이블 생성 

CREATE TABLE EMPLOYEE(
       (EMPNO      NUMBER       NOT NULL,                                                                                                     
       EMPNAME  CHAR(10)      UNIQUE,                                                                                                    
       TITLE          CHAR(10)       DEFAULT ‘사원’,
       MANAGER  NUMBER,
       SALARY      NUMBER,      CHECK(SALARY < 6000000),
       DNO             NUMBER,      CHECK(DNO IN(1,2,3,4,5,6)) DEFAULT 1,
       PRIMARY KEY (EMPNO),
       FOREIGN KEY(MANAGER) REFERENCES  EMPLOYEE(EMPNO),
       FOREIGN KEY(DNO) REFERENCES  DEPARTMENT(DEPTNO) ON DELETE CASCADE);
);

 

그렇다면 아래의 예시를 보자.

 

위와 같은 뷰 갱신은 성공적으로 수행될 수 있을까?

답은 YES 다.

 

MANAGER, SALARY, DNO 애트리뷰트는 값이 입력되지 않았으나 NOT NULL 혹은 DEFAULT 값이 지정되어 있기 때문에 MANAGER과 SALARY는 널 값, DNO는 디폴트 값인 1이 기본 릴레이션에 INSERT될 것이다.

 

 

갱신 2 : 두 개의 릴레이션 위에서 정의된 뷰에 대한 갱신

 

 

EMP_PLANNING 뷰는 위에서 정의했듯, EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션을 조인하여 정의한 뷰이다.

위 질의어는 EMPLOYEE의 기본 키인 EMPNO의 값이 삽입되지 않으므로 연산이 거절된다.

 

더 나아가, 조인으로 이루어진 릴레이션은 기본 키, 외래 키를 모두 만족해야 한다.

이는 매우 복잡해지므로 이런 경우 갱신을 아예 허용하지 않는 경우가 대부분이다.(일부 DBMS는 하나의 릴레이션에만 대응되면 수정을 허용하기도 한다.)

 

갱신 3 : 집단 함수 등을 포함한 뷰에 대한 갱신

 

 

이 경우 갱신이 불가능하다.

 

계산된 평균 값을 사원의 급여로 어떻게 수정해야 할지 결정을 못하기 때문이다.

( = 집단 함수 결과 값을 어떻게 반영해야 할지 결정할 수 없음)

 

 

정리하면, 갱신이 불가능한 뷰는 다음과 같이 정리할 수 있다.

 

1. 한 릴레이션 위에서 정의되었으나 그 릴레이션의 기본 키가 포함되지 않은 뷰

2. 기본 릴레이션의 애트리뷰트들 중에서 뷰에 포함되지 않은 애트리뷰트에 대해 NOT NULL이 지정되어 있을 때

3. 집단 함수가 포함된 뷰

4. 조인으로 정의된 뷰