[05-2] Oracle OBJECT(VIEW)

VIEW 뷰

SELECT(쿼리문)을 저장해둘 수 있는 객체 (자주 쓰는 긴 SELECT문을 저장해두면 긴 SELECT문을 매번 다시 기술할 필요가 없음) 임시테이블(실제 데이터가 들어가는 것은 아님)

VIEW 생성방법   [표현법]   CREATE VIEW 뷰명   AS 서브쿼리;      CREATE OR REPLACE VIEW 뷰명   AS 서브쿼리; -> OR REPLACE는 생략이 가능하다.   뷰 생성 시 기존에 중복된 이름이 없다면 새로 만들고            기존에 중복된 이름의 뷰가 있다면 해당 뷰를 변경(갱신)하는 옵션  -- 뷰는 논리적인 가상테이블 ==> 실질적으로 데이터를 저장하고 있지 않음  -- 참고) 해당 계정이 가지고 있는 VIEW들에 대한 내용을 조회하고자 한다면 데이터 딕셔너리 중 USER_VIEW를 조회하면 된다.

  CREATE VIEW VW_EMPLOYEE
  AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, NATIONAL_NAME, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, NATIONAL N, JOB J
WHERE DEPT_CODE = DEPT_ID
  AND LOCATION_ID = LOCAL_CODE
  AND L.NATIONAL_CODE = N.NATIONAL_CODE
  AND E.JOB_CODE = J.JOB_CODE
  AND NATIONAL_NAME = '한국';

뷰 컬럼에 별칭 부여

-- 사원의 사번, 이름, 직급명, 성별, 근속연수
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);



-- 이렇게도 별칭 지정 가능! 단, 모든 컬럼의 별칭을 지정해줘야함
CREATE OR REPLACE VIEW VW_EMP_JOB(사번, 사원명, 직급명, 성별, 근무년수)
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);​

 

뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용 가능 뷰에다가 적용 => 실제 데이터가 담겨있는 베이스테이블에 적용이 된다

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

VIEW 옵션

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

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

-- 2) FORCE/NOFORCE
CREATE OR REPLACE /*NOFORCE*/ VIEW VW_TEST
    AS SELECT FORCE, NOFORCE
         FROM STARWARS;    
-- ORA-00942 : table or view does not exist
-- 존재하지 않는 테이블

CREATE OR REPLACE FORCE VIEW VW_TEST
    AS SELECT FORCE, NOFORCE
         FROM STARWARS;
-- 경고 : 컴파일 오류와 함께 뷰가 생성되었습니다.
-- 없는거 강제로 만들어

SELECT * FROM VW_TEST;
-- 오류 발생

SELECT * FROM USER_VIEWS;

CREATE TABLE STARWARS(
    FORCE NUMBER,
    NOFORCE NUMBER
);

SELECT * FROM VW_TEST;
-- 테이블 생성 후 다시 한 번 조회하면 오류가 발생하지 않음

-- 3) WITH CHECK OPTION
CREATE OR REPLACE VIEW VW_EMP
AS
SELECT * -- 3 조회할 컬럼 기술
FROM EMPLOYEE -- 1 조회할 테이블
WHERE SALARY >= 3000000 --2 조건 기술
WITH CHECK OPTION;

SELECT * FROM VW_EMP;

UPDATE VW_EMP
SET SALARY = 5000000
WHERE EMP_NAME = '박머루';

UPDATE VW_EMP
SET SALARY = 2999999
WHERE EMP_NAME = '박머루';
-- 서브쿼리에 기술한 조건에 부합하지 않기 때문에 변경 불가

ROLLBACK;

-- 4) WITH READ ONLY; (RO)
CREATE OR REPLACE VIEW VW_EMPLOYEE
    AS SELECT EMP_ID, EMP_NAME, BONUS
         FROM EMPLOYEE
        WHERE BONUS IS NOT NULL
    WITH READ ONLY;
    -- 읽기전용
    
SELECT * FROM VW_EMPLOYEE;

DELETE FROM VW_EMPLOYEE
WHERE EMP_ID = 213;
-- "cannot perform a DML operation on a read-only view" 읽기 전용이라 지울 수 없어