[SQL] Oracle - VIEW

 

< 뷰 (VIEW) >

 

- SELECT(쿼리문)을 저장해 둘 수 있는 객체

- 임시테이블 또는 논리적인 가상테이블의 개념

   (실질적으로 데이터를 저장하는 것이 아니라, 쿼리문을 TEXT로 저장)

 

[ 생성방법 ]

- 관리자 계정으로 GRANT구문을 통해 CREATE VIEW 권한을 주어야 함

CREATE VIEW 뷰명           AS 서브쿼리;

[ 상세표현법 ] CREATE OR REPLACE FORCE/NOFORCE VIEW 뷰명          AS 서브쿼리      WITH CHECK OPTION      WITH READ ONLY;

[ 옵션 ]

> OR REPLACE : 해당 뷰가 존재하지 않으면 새로 생성 / 존재하면 갱신시켜주는 옵션
> FORCE / NOFORCE
  - FORCE : 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰가 생성
  - NOFORCE(기본값) : 서브쿼리에 기술된 테이블이 반드시 존재해야만 뷰가 생성
> WITH CHECK OPTION : 서브쿼리 조건절에 기술된 내용에 만족하는 값으로만 DML 가능
                                            조건에 부합하지 않는 값으로 수정하는 경우 오류가 발생
> WITH READ ONLY : 뷰에 대해서 조회만 가능(DML 수행 불가)    

 

--> VIEW 생성
--> OR REPLACE
CREATE OR REPLACE VIEW VW_EMP_JOB -- NOFORCE 기본값
    AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
              DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') "성별",
              EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) "근무년수"
         FROM EMPLOYEE
         JOIN JOB USING(JOB_CODE);
-- 서브쿼리의 SELECT절에 함수나 연산이 들어가면 반드시 별칭 지정해줘야함


--> FORCE / NOFORCE
CREATE OR REPLACE /*NOFORCE*/ VIEW VW_TEST
    AS SELECT FORCE, NOFORCE
         FORM NIKE;
-- 테이블이 존재하지 않으므로 오류 발생
-- ORA-00942: table or view does not exist

CREATE OR REPLACE FORCE VIEW VW_TEST
    AS SELECT FORCE, NOFORCE
         FROM NIKE;
-- 경고: 컴파일 오류와 함께 뷰가 생성되었습니다.
SELECT * FROM VW_TEST;

CREATE TABLE NIKE(
    FORCE NUMBER,
    NOFORCE NUMBER
);

SELECT * FROM VW_TEST; -- 테이블 생성 후, 볼 수 있음


--> WITH READ ONLY
CREATE OR REPLACE VIEW VW_EMPBONUS
    AS SELECT EMP_ID, EMP_NAME, BONUS
         FROM EMPLOYEE
        WHERE BONUS IS NOT NULL
  WITH READ ONLY;

SELECT * FROM VW_EMPBONUS;

DELETE FROM VW_EMPBONUS
 WHERE EMP_ID = 213;
-- ORA-42399: cannot perform a DML operation on a read-only view

 

[ DML구문 사용 가능 ]

- VIEW에 DML구문을 적용하면 실제 데이터가 담겨있는 베이스 테이블에 적용됨

- BUT) DML구문 사용이 불가능한 경우도 있음

    1) 뷰에 정의되지 않은 컬럼을 조작하는 경우
    2) NOT NULL 제약조건이 지정된 경우
    3) 산술연산식 또는 함수를 통해서 정의되어있는 경우
    4) 그룹함수 GROUP BY 절이 포함된 경우
    5) DISTINCT구문이 포함된 경우
    6) JOIN을 이용해서 여러 테이블을 매칭시켜놓은 경우