오라클 뷰 VIEW

뷰

뷰의 개념

뷰는 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블이다. 뷰는 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체 데이터중에서 일부만 접근할 수 있도록 제한하기 위한 기법이다. 뷰를 가상테이블이라고 하는 이유는 테이블은 디스크에 공간이 할당되어 데이터를 저장할 수 있지만, 뷰는 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장하고 디스크에 저장공간이 할당되지 않는다. (하지만 일반 사용자들은 SQL 명령문을 이용하여 테이블에 저장된 데이터를 검색하고 저작하는 것과 유사하게 뷰를 이용할 수 있다) 뷰는 물리적인 저장공간과 데이터를 가지지는 않지만 테이블에서 파생된 객체이므로 테이블과 유사하게 사용할 수 있다. 오라클에서 뷰를 구성하는 칼럼의 최대 개수는 254개이다. 그리고 뷰에 대한 수정 결과는 뷰를 정의한 기본 테이블에 적용된다?

그리고 뷰를 정의한 기본 테이블의 데이터가 변경되면 뷰에도 반영된다. 또한 뷰는 기본 테이블에서 정의된 모든 무결성 제약조건을 상속받는다. 뷰에대한 정의는 USER_VIEWS 데이터 딕셔너리 테이블을 통해 조회할 수 있다.

;

뷰의 장점

전체 데이터의 일부만 접근할 수 있는 보안 목적(기능)과 사용자에게 편의성을 제공할 수 있음.

;

보안 관점에서는 사용자에게 전체 데이터의 일부만 접근할 수 있는 뷰를 통하여 사용자에게 해당 뷰만 접근할 수 있도록 허용하여 중요한 데이터의 유출을 방지할 수 있다.

또한 뷰를 통하여 사용자에게 필요한 정보만 선택적으로 제공하여 정보 접근의 편의성을 제공할 수 있다.

;

뷰의 종류

뷰의 종류는 뷰를 정의하기 위해 사용된 기본 테이블 수에 따라 단순 뷰 또는 복합 뷰로 구분된다. 단순 뷰는 하나의 기본 테이블에 의해 정의한 뷰이다. 단순 뷰는 INSERT, DELETE, UPDATE와 같은 DML 명령문을 실행할 수 있다. 단순 뷰를 대상으로 실행한 DML 명령문의 처리 결과는 뷰를 정의한 기본 테이블에 적용된다. 복합 뷰는 2개 이상의 기본 테이블로 구성한 뷰이다. 복합 뷰에 대해서는 무결성 제약조건, 표현식, GROUP BY 절의 유무에 따라 DML 명령문을 제한적으로 사용할 수 있다. 복합 뷰에는 DISTINCT, 그룹함수, GROUP BY, START WITH CONNECT BY, ROWNUM을 포함할 수 없다. 그리고 UNION ALL, INTERSECT 등과 같은 집합 연산을 실행할 수 없다.

;

뷰 관리

뷰 생성 및 조회

CREATE VIEW 명령문은 뷰를 생성하기 위한 DDL 명령문이다. 뷰 생성시 칼럼 이름을 명시하지 않으면 뷰를 정의하는 기본 테이블의 칼럼 이름을 상속한다. 하지만 함수나 표현식에 의해 정의되는 칼럼은 별도로 이름을 명시하지 않으면 오류가 발생한다. 뷰에대한 데이터 검색은 SELECT 명령문을 이용하여 기본 테이블의 데이터 검색 방법과 동일하게 검색할 수 있다.

;

사용법

CREATE [ OR REPLACE] [ FORCE|NOFORCE] VIEW view [ (alias, alias, …)] AS subquery

;

OR REPLACE : 기존 뷰와 동일한 이름으로 뷰를 재생성하는 경우

FORCE : 기본 테이블의 존재 여부에 상관없이 뷰 생성

NOFORCE : 기본 테이블이 존재할 경우에만 뷰 생성, 기본값

ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 뷰의 칼럼이름

;

학생 테이블에서 101번 학과 학생들의 학번, 이름, 학과 번호로 정의되는 단순 뷰를 생성하여라

> CREATE VIEW v_stud_dept101(학번,이름,학과번호) AS SELECT studno, name, deptno FROM student WHERE deptno = 101;

;

학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년, 학과이름 으로 정의되는 복합 뷰를 생성하여라.

> CREATE VIEW v_stud_dept102(학번, 이름, 학년, 학과이름)

AS SELECT s.studno, s.name, s.grade, d.dname

FROM student s, department d

WHERE s.deptno=d.deptno AND s.deptno=102;

;

SELECT * FROM v_stud_dept102;

;

교수 테이블에서 학과별 평균 급여와 총계로 정의되는 뷰를 생성하여라.

> CREATE VIEW v_prof_avg_sal

AS SELECT deptno, SUM(sal), AVG(sal)

FROM professor

GROUP BY deptno;

* 함수를 사용하여 뷰를 생성하는 경우 칼럼 별명을 사용하지 않으면 오류가 발생한다.

;

> CREATE VIEW v_prof_avg_sal

AS SELECT deptno, SUM(sal) sum_sal, AVG(sal) avg_sal

FROM professor

GROUP BY deptno;

;

인라인 뷰

인라인 뷰는 FROM 절에서 서브쿼리를 사용하여 생성한 임시 뷰이다. 인라인 뷰는 SQL 명령문이 실행되는 동안만 임시적으로 정의된다. 데이터베이스 실무에서 인라인 뷰는 FROM 절에서 참조하는 테이블의 크기가 클 경우, 필요한 행과 칼럼만으로 구성된 집합을 재정의하여 질의문을 효율적으로 구성할 수 있다.

;

사용법(FROM절에 서브쿼리 사용)

SELECT column_list

FROM (subquery) alias

WHERE condition;

;

인라인 뷰를 사용하여 학과별로 학생들의 평균 키와 평균 몸무게, 학과 이름을 출력하여라

> SELECT dname, avg_height, avg_weight

FROM (SELECT deptno, avg(height) avg_height, avg(weight) avg_weight

FROM student GROUP BY deptno) s,department d

WHERE s.deptno=d.deptno;

;

인라인 뷰를 사용하여 모든 학생 중에서 몸무게가 적은순으로 상위 5명을 출력하여라

;

> SELECT studno, name, weight, rn

FROM ( SELECT studno, name, weight,

;RANK() OVER (ORDER BY weight) rn

;FROM student)

WHERE rn BETWEEN 1 AND 5;

;

뷰에 대한 질의의 내부적인 처리 과정

1. USER_VIEWS 데이터 딕셔너리에서 뷰에 대한 정의를 조회한다.
2. 기본 테이블에 대한 뷰의 접근 권한을 확인한다.
3. 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환한다.
4. 기본 테이블에 대한 질의를 통해 데이터를 검색한다.
5. 검색된 결과를 출력한다.

;

뷰와 관련된 데이터 딕셔너리 테이블

사용자가 생성한 모든 뷰에 대한 정의는 USER_VIEWS 데이터 딕셔너리에 저장된다.

USER_VIEWS 데이터 딕셔너리에는 뷰를 정의한 SELECT 명령문이 문자열로 저장되어 있다. 뷰는 다음 예처럼 디스크상에 공간이나 저장된 데이터가 없이 USER_VIEWS 데이터 딕셔너리에 뷰를 정의한 SELECT 명령문만 문자열로 저장되는 가상 테이블이다.

;

SELECT view_name, text FROM user_views; (USER_VIEWS 데이터 딕셔너리에 뷰를 정의한 SELECT 명령문이 문자열로 저장된다)

;

뷰의 변경

뷰에 대한 변경은 뷰에 대한 정의를 수정하는 것을 의미한다. 뷰의 변경은 기존 뷰에 대한 정의를 삭제한 후 재생성하거나 CREATE 명령문에서 OR REPLACE 옵션을 이용하여 재정의 할 수 있다. OR REPLACE 옵션은 기존에 생성된 뷰가 있을 경우에는 기존 뷰를 무시하고 재생성하는 옵션이다. 뷰의 변경 결과는 USER_VIEWS 데이터 딕셔너리에 저장된다.

;

기존 V_STUD_DEPT101 뷰에 학년 칼럼을 추가하여 재정의하여라

CREATE OR REPLACE VIEW v_stud_dept101

(학번, 이름, 학과번호, 학년)

AS SELECT studno, name, deptno, grade

; ; ; ; ;FROM student

; ; ; ; ;WHERE deptno=101;

;

뷰 정의 확인

DESC v_stud_dept101

;

뷰에 대한 데이터 조작

단순 뷰는 기본 테이블과 동일하게 DML명령문을 사용하여 데이터를 조작할 수 있다. 하지만 뷰에 대한 DML 명령문은 내부적으로는 기본 테이블에 대한 데이터를 조작하는 과정이다. 따라서 뷰에 대한 무결성 제약조건도 기본 테이블에 대한 정의된 무결성 제약조건이 적용된다. 하지만 복합 뷰에 대해서는 일부 DML 명령문의 사용이 제한된다.

뷰에 대한 데이터 조작이 불가능한 경우는 다음과 같은 세가지 경우가 있다. 첫째, 뷰 정의에 포함되지 않는 기본 테이블의 칼럼이 NOT NULL 제약조건으로 지정된 경우에는 뷰에 대한 데이터 삽입이 불가능하다. 그 이유는 뷰에 대한 INSERT 명령문이 기본 테이블에 대한 INSERT 명령문으로 변환되면 NOT NULL 제약조건을 가진 칼럼에 NULL을 입력하는 형태가 되기 때문이다. 둘째, 뷰 정의시 표현식으로 정의된 칼럼에 대해서는 UPDATE, INSERT명령문의 실행이 불가능하다. 예를들어 SAL*12와 같은 산술 표현식으로 정의된 가상 칼럼은 실체가 없기 때문에 삽입이나 수정이 불가능하기 때문이다. 셋째, 뷰 정의시 그룹함수, DISTINCT 그리고 GROUP BY 절을 포함한 경우에는 모든 종류의 DML 명령문을 사용할 수 없다. 그 이유는 이러한 형태의 뷰에서 정의된 칼럼은 실체가 없는 가상 칼럼이기 때문이다.

;

뷰의 삭제

뷰는 실체가 없는 가상 테이블이므로 뷰의 삭제는 USER_VIEWS 데이터 딕셔너리에 저장된 뷰의 정의를 삭제하는 것을 의미한다. 따라서 뷰의 삭제는 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않는다.

;

사용법

DROP VIEW view;

;

학생테이블과 연관된 모든 뷰를 삭제하여라

> DROP VIEW v_stud_dept101;

> DROP VIEW v_stud_dept102;

> DROP VIEW v_pof_avg_sal;

;

> SELECT view_name, text

;

;

;