About View

출처:http://www.oracleclub.com/lecture/1036
########################################################################################################################
뷰의 제한 조건.
########################################################################################################################
- 테이블에 NOT NULL로 만든 컬럼들이 뷰에 다 포함이 되 있어야 한다.
- ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터도 INSERT 할 수 없다
- WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신 할 수 없다.
- WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제,수정을 할 수 있다

########################################################################################################################
뷰 문법
########################################################################################################################
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
AS 서브쿼리
[with check option [constraint 제약조건]]
[with read only]

 

- FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성
- WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지정
- WITH READ ONLY : SELECT만 가능한 VIEW 생성
- 함수를 사용한 컬럼은 반드시 ALIAS를 지정해야 한다.

########################################################################################################################
뷰 변경, 재컴파일
########################################################################################################################
뷰는 쿼리에 대한 변경이 불가능하기에 AS SELECT 옵션에 작성한 SQL을 변경하고 싶다면 해당 뷰를 재생성해야 한다.

 

뷰에 이상이 생겨 상태가 INVALID로 된 경우 재컴파일을 통해(AS SELECT가 유효해야 한다) VALID 상태로 바꿀 수 있다.
SQL> ALTER VIEW view_name RECOMFILE;

########################################################################################################################
뷰 예제
########################################################################################################################
-- 뷰 생성   
SQL> CREATE OR REPLACE VIEW name_query
     AS
       SELECT a.ename, b.dname
       FROM  emp a, dept b
       WHERE a.deptno = b.deptno
         AND b.deptno = 20;

 

-- 뷰를 이용한 조회
SQL> SELECT * FROM name_query;

ENAME                DNAME
-------------------- ----------
SMITH                RESEARCH
JONES                RESEARCH
...

 

-- 뷰 제거
SQL> DROP VIEW name_query;

########################################################################################################################
WITH CHECK OPTION
########################################################################################################################
WITH CHECK OPTION
뷰의 조건식을 만족하는 데이터만 INSERT 또는 UPDATE가 가능 하도록 하는 옵션 이다.

 
-- 부서 번호 10의 데이터를 조회 하는 뷰
SQL> CREATE OR REPLACE VIEW check_option
     AS
       SELECT empno, ename, deptno
       FROM  emp
       WHERE deptno = 10
       WITH CHECK OPTION ;
 
-- 부서 번호가 10인 사원만 INSERT, UPDATE할 수 있다.
SQL> INSERT INTO check_option(empno, ename, deptno)
     VALUES (10005, 'jain', 30);

INSERT INTO check_option(empno, ename, deptno)
            *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
########################################################################################################################
WITH READ ONLY
########################################################################################################################
SELECT만 가능한 VIEW를 생성 한다

 
-- 아래 뷰는 읽기만 가능하다.
SQL> CREATE OR REPLACE VIEW read_only
     AS
       SELECT empno, ename, deptno
       FROM  emp
       WHERE deptno = 10
       WITH READ ONLY;
########################################################################################################################
뷰의 정보 조회
########################################################################################################################
USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있다.
 
SQL> SELECT view_name, text
     FROM USER_VIEWS;