[Oracle] 오라클 VIEW 생성 및 사용법 정리 (DML-insert, update 포함)

 

VIEW

오라클에서 사용할 수 있는 오브젝트이다. 테이블과도 비슷하다.

다만 VIEW는 테이블처럼 실제 데이터를 저장하고 있지는 않다. 그럼에도 불구하고 일종의 통로 역할로, DML 작업이 가능한 가상의 테이블이다.

 

※ 따라서 더욱 쉽게 보자면 VIEW 는 유저가 복잡한 쿼리문을 매번 작성하는 것이 아니라, 한 개의 뷰 문으로 대입시켜 사용한다. 라고 이해해도 괜찮을 듯 싶다.

 

VIEW 의 특징

- 복잡한 쿼리문을 단순화 시킬 수 있다.

- 뷰는 사용자에게 필요한 정보만 접근하도록 접근을 제한시킬 수 있다.

 

view 생성 시의 주의 사항 :

grant connect, resource to ~ 라는 구문만으로는 view 생성의 권한은 부여되지 않는다. 

따라서 grant create view to ~ 이라는 구문으로 관리자계정에서 생성 권한을 부여해주어야 한다.

 

 

VIEW 표현법

CREATE [ OR REPLACE ] [ FORCE|NOFORCE ] VIEW 뷰이름 [ (컬럼의 별칭 ALIASES) ]
AS ( /* SUBQUERY */ SELECT 문 )
    [ WITH READ ONLY ]
    [WITH CHECK OPTION [CONSTRAINT 제약조건명] ]
	;

※ 대괄호([ ])의 항목은 필요하지 않을 경우 생략이 가능하다.

- OR REPLACE : 해당 구문을 사용하면 뷰를 수정할 때 DROP를 사용한 삭제 후 재생성 없이 수정이 가능하다.

- FORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성이 가능하다.

- NORORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼 함수 등이 존재하지 않으면 생성되지 않는다.

- 컬럼의 ALIASES : SELECT 컬럼의 별칭을 미리 정의할 수 있다. 단 나열될 컬럼의 모든 별칭을 입력해야만 한다.

- WITH READ ONLY : SELECT 만 가능하다. (INSERT, UPDATE, DELETE 불가능)

- WITH CHECK OPTION : WHERE 절의 조건에 해당하는 데이터만 저장, 변경이 가능하다.

- CONSTRINAT 제약조건명 : WITH CHECK OPTION 의 제약조건명을 설정하는 것. 생략 시 SYS~ 로 임의로 설정된다.

 

 

뷰 생성 1

CREATE OR REPLACE VIEW VW_EMP
AS ( SELECT EMP_ID, EMP_NAME, SALARY
	FROM EMPLOYEE) ;

1번 뷰에서는 하나의 테이블을 가공 없이 서브쿼리로 담는 것을 의미한다.

해당 뷰에서는 SELECT, INSERT, UPDATE, DELETE 와 같은 DML 구문을 자유롭게 사용할 수 있다.

 

뷰 생성2

CREATE OR REPLACE VIEW VW_EMP
AS (SELECT E.EMP_ID
    , E.EMP_NAME
    , D.DEPT_TITLE
    , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS "근무년수"
    FROM EMPLOYEE E, DEPARTMENT D
    WHERE E.DEPT_CODE = D.DEPT_ID) ;

2번 뷰에서는 조인 등을 사용하여 생성했다.  이렇게 산술연산자 함수, GROUP BY, UNION, 조인 등을 사용하여 뷰를 생성하였을 경우에는 1번 뷰와는 다른 특징이 있다.

이 중 함수를 사용하여 컬럼을 만들었을 경우 반드시 별칭을 지정해주어야 한다.

2번과 같은 뷰는 SELECT 를 제외한 INSERT, UPDATE, DELETE 와 같은 DML 구문 사용에 제약을 먹을 수 있다.

 

 

※ 원본 컬럼에 대해 가공하지 않은 경우에는 DML이 가능 그러나 컬럼을 가공(DEDOCE, 산술, 그룹함수 등)하면 불가능
    1) 뷰에 정의되어있지 않은 칼럼을 조작하는 경우
    2) 뷰에 정의되어 있지 않은 칼럼 중 베이스테이블 상에 NOT NULL 제약조건이 지정된 경우
    3) 산술연산식 또는 함수를 통해 정의되어 있는 경우
    4) 그룹함수나 GROUP BY 절이 포함되어 있을 경우
    5) DISTINCT 구문 포함되 경우
    6) JOIN 을 이용해서 여러 테이블을 매칭시켜놓은 경우

 

뷰 칼럼 주석 추가

COMMENT ON COLUMN VW_EMP.EMP_NAME IS '이름';

 

뷰 삭제

DROP VIEW VW_EMP;

 

뷰 칼럼의 별칭 부여

-- 1번
CREATE OR REPLACE VIEW VW_EMP
AS ( SELECT EMP_ID "사번"
	, EMP_NAME "이름"
    , DEPT_TITLE "부서명"
    , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS "근무년수"
    FROM EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    );
    
    
-- 2번
CREATE OR REPLACE VIEW VW_EMP
    (사번
    , 이름
    , 부서명
    , 근무년수
    )
AS ( SELECT EMP_ID
	, EMP_NAME
    , DEPT_TITLE
    , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
    FROM EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    );

 

뷰 생성의 기본값인 NOFROCE 와 FORCE

CREATE OR REPLACE /*NOFORCE 기본값 */ FORCE VIEW VW_EMP
AS ( SELECT EMP_ID "사번"
	, EMP_NAME "이름"
    , DEPT_TITLE "부서명"
    , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS "근무년수"
    FROM EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    );

NOFORCE : 서브쿼리 문 상의 테이블, 컬럼, 함수가 존재하지 않으면 오류가 발생하고 뷰는 생성되지 않는다. (기본값)

 

FORCE : 서브쿼리 문 상의 테이블, 컬럼, 함수가 존재하지 않을 경우에도 뷰를 생성한다. 다만 생성된 뷰의 데이터 타입은 일괄적으로 UNDEFINDE 상태이며, 뷰는 동작하지 않는다. 

※ 사용하기 위해서는 뷰 생성 시 오류를 발생시킨, 존재하지 않는 테이블이나 컬럼, 함수 가 생성된 후에는 사용이 가능해진다.

 

ex)

CREATE OR REPLACE FORCE VW_TT
AS SELECT TCODE, TNAME, TCOMMENT
    FROM TT);

-- TT테이블이 없기 때문에 뷰는 생성되더라도 오류

CREATE TABLE TT(
    TCODE NUMBER
    TNAME VARCHAR2(20)
    TCOMMENT VARCHAR2(4000)
    );

-- CREATE 로 TT 테이블을 생성한 후에는 위의 뷰가 사용이 가능해진다.

 

 

WITH READ ONLY

CREATE OR REPLACE VIEW VW_EMP
AS SELECT EMP_ID, EMP_NAME, SALARY
    FROM EMPLOYEE
    WITH READ ONLY
    ;

해당 옵션은 READ, 즉 조회인 SELECT만 가능하고 다른 DML 구문인 INSERT, UPDATE, DELETE 는 불가능하다.

 

 

WITH CHECK OPTION

-- 1) 제약조건명 생략
CREATE OR REPLACE VIEW VW_EMP
AS ( SELECT EMP_ID, EMP_NAME, SALARY
    FROM EMPLOYEE
    WHERE SALARY >= 3000000 )
    WITH CHECK OPTION
    ;
    
-- 2) 제약조건명 입력
CREATE OR REPLACE VIEW VW_EMP
AS ( SELECT EMP_ID, EMP_NAME, SALARY
    FROM EMPLOYEE
    WHERE SALARY >= 3000000 )
    WITH CHECK OPTION
    CONSTRAINT VW_EMP_SAL300
    ;

WITH CHECK OPTION : 서브쿼리 상에 입력한 WHERE.. 조건에 부합하는 데이터만 조작이 가능하다.

만약 UPDATE 절로 특정 이름을 SALARY를 2000000 으로 낮춘다는 것은 WHERE 에 어긋나기 때문에 불가능하다.

 

대신 똑같은 UPDATE 절로 특정 이름의 SALARY를 4000000으로 바꾸는 건 WHERE 조건절에 부합하기 때문에 가능하다.

 

제약조건명은 생략도 가능하며, 생략 시에는 SYS~ 이라는 시스템이 임의로 부여한 제약조건명이 부여된다.