[Oracle] 28.뷰(2)

뷰의 종류

뷰의 종류는 단순뷰와 복합뷰가 있습니다.

단순뷰는 하나의 테이블로 생성된 뷰이고, 복합뷰는 둘 이상의 테이블로 생성된 뷰입니다.

-- 단순뷰

CREATE VIEW dept_sel_avg_vw (부서번호,"급여 총액","급여 평균") AS
    ( SELECT department_id,SUM(salary),AVG(salary)
      FROM
        emp
      GROUP BY
        department_id
);

-- 복합뷰

CREATE VIEW complex_vw AS
    SELECT
        employee_id "사번",
        first_name|| ' '|| last_name "사원 이름",
        e.department_id "부서 번호",
        department_name "부서 이름",
        city "지역명"
    FROM
        emp e,
        departments d,
        locations lo
    WHERE
        e.department_id = d.department_id
        AND d.location_id = lo.location_id;

복합뷰는 다음과 같이 GROUP BY 절을 사용할 수 있습니다.

--group by 사용 가능

SELECT
    COUNT(사번)
FROM
    complex_vw
GROUP BY
    "부서 번호";

OR REPLACE VIEW

이제부터는 뷰를 생성할 때 사용하는 옵션에 대해 알아보겠습니다~

먼저 알아볼 옵션은 OR REPLACE VIEW 입니다.

이 옵션은 테이블이 이미 존재하면 대체해주는 속성입니다.

-- CREATE OR REPLACE 테이블이 이미 존재하면 대체 ,없으면 새로 생성

CREATE OR REPLACE VIEW emp_vw AS
    SELECT
        *
    FROM
        emp
    WHERE
        department_id = 30;

FORCE: 없는 테이블의 뷰 생성

FORCE 속성은 테이블이 존재하지 않아도 뷰를 생성할 수 있게해주는 속성입니다.

--FORCE : 테이블이 정의되지 않아도 뷰를 생성

CREATE OR REPLACE FORCE VIEW koo_vw AS
    SELECT
        *
    FROM
        koo; --koo 실제 koo 테이블이 존재하지 않음

FORCE를 써주지 않으면 기본값으로 NOFORCE가 적용되며 테이블이 존재하지 않으면 뷰를 생성할 수 없습니다.

WITH CHECK OPTION: 조건이 바뀌는 것을 막음

다음과 같은 뷰가 정의되었을 때,

CREATE OR REPLACE VIEW emp_vw AS
    SELECT
        *
    FROM
        emp
    WHERE
        department_id = 30
	WITH CHECK OPTION;

WITH CHECK OPTION을 지정하면, 다음과 같은 UPDATE문을 사용할 수 없습니다.

UPDATE emp_vw
SET
    department_id = 20; -- 오류 발생

WITH READ OLNY

WITH READ OLNY 옵션을 지정하면 DML을 사용하지 못하고 오로지 조회(SELECT)만 가능합니다.

CREATE OR REPLACE VIEW emp_vw AS
    SELECT
        *
    FROM
        emp
    WHERE
        department_id = 30
	WITH READ ONLY;

UPDATE emp_vw
SET
    last_name = '수지'; -- 오류 발생

인라인 뷰

인라인 뷰는 from절에 서브쿼리가 오는 것을 말합니다.

보통 top-N 을 구할 때 사용하는데요~

예를 들어 급여가 가장 많은 5명을 조회할 수 있는 뷰를 만들 때 사용할 수 있습니다.

먼저 ROWNUM 속성을 알아야하는데요~

ROWNUM은 테이블에 주어지는 고유번호입니다! 1부터 순서대로 부여됩니다.

SELECT
    ROWNUM,
    employee_id,
    salary
 FROM
    employees;

이 때 ORDER BY를 해도 지정된 ROWNUM은 바뀌지 않습니다!

SELECT
    ROWNUM,
    employee_id,
    salary
FROM
    employees
ORDER BY
    salary DESC;

그 이유는 ROWNUM 칼럼은 오라클의 내부적으로 부여되는데 INSERT 문을 이용하여 입력할 때,

입력한 순서에 따라 1씩 증가되면서 값이 지정되었기 때문입니다.

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

이 사실을 이용하면 서브쿼리를 from절에 삽입해서 급여를 가장 많이 받는 5명을 보여주는 뷰를 생성할 수 있습니다.

아까도 말했지만 from절에 서브쿼리가 오는 것을 인라인 뷰라고 합니다~

CREATE OR REPLACE VIEW empsal_vw AS
    SELECT
        ROWNUM rown,
        employee_id,
        first_name,
        salary
    FROM
        (
            SELECT
                ROWNUM,
                employee_id,
                first_name,
                salary
            FROM
                employees
            ORDER BY
                salary DESC
        )
    WHERE
        ROWNUM <= 5;

뷰는 쿼리문이기에 기본 테이블의 데이터가 바뀌면 위의 선언한 empsal_vw도 자동으로 바뀌어서

급여를 많이 받는 5명도 자동으로 업데이트됩니다!

어때요~ 뷰 정말 유용하죠??

주의할 것은 다음과 같은 조건은 쓸 수 없습니다.

CREATE OR REPLACE VIEW empsal_vw AS
    SELECT
        ROWNUM rown,
        employee_id,
        first_name,
        salary
    FROM
        (
            SELECT
                ROWNUM,
                employee_id,
                first_name,
                salary
            FROM
                employees
            ORDER BY
                salary DESC
        )
    WHERE
        ROWNUM > 5; -- 조회 안됨

위와 같이 ROWNUM이 어떤 수보다 크다는 조건을 사용해서 뷰를 생성하면, 뷰에는 아무런 데이터가 조회되지 않습니다.