SQL)강의- 제 14 장 View 생성

출처 : http://naver.kaist.ac.kr/oracle_sql/s14.htm

1. 뷰?

◈뷰란 테이블이나 다른 뷰를 기초로 한 논리적인 테이블이다.
◈뷰는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같다.
◈뷰는 실제적으로는 질의문장을 가진다.


 

♣ 뷰의 장점

◈뷰는 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다.
◈복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다. 예를 들면 조인 방법을 몰라도 조인을 한것처럼 여러 테이블에 대한 데이터를 뷰를 통해 볼수 있다.
◈한 개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있다.
◈특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있다. 예를 들면 10번 부서의 직원은 10번 부서 직원들로 구성된 사원 테이블을 보게 할 수도 있다.

2. 뷰 생성/변경

♣ Syntax

CREATE  [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰명 [(alias[,alias]...)]

AS Subquery  [WITH CHECK OPTION [CONSTRAINT 제약조건]]

             [WITH READ ONLY]

◈CREATE VIEW문에 Subquery를 써서 뷰를 만든다.
◈FORCE는 Base Table 유무에 관계없이 뷰를 만든다.
◈Alias는 Subquery를 통해 선택된 값에 대한 Column명이 된다.
◈WITH CHECK OPTION은 뷰에 의해 액세스 될수 있는 행만이 입력되거나 변경될 수 있음을 지정하고,
   WITH READ ONLY는 이뷰에서 DML이 수행될 수 없게 한다.
◈뷰를 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT문장으로 구성될 수 있다.
◈뷰를 정의하는 질의어에는 ORDER BY 절을 쓸 수 없다.
◈제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.
◈뷰를 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE옵션을 쓸 수 있다.

♣ Simple뷰와 Complex뷰

특징	Simple뷰	Complex뷰
테이블의 수	하나	하나이상
데이터 그룹 포함 가능여부	가능	가능
뷰를 통한 DML	가능	불가능

♣ Simple뷰 생성

♠실습 : 부서번호가 45인 사번과 성, 직급을 가지는 뷰를 만들고 내용을 출력하시오.

SQL> CREATE VIEW empview45               AS SELECT id, last_name, title    FROM   s_emp   WHERE  dept_id = 45; SQL> SELECT *   FROM empview45;

 

♠실습 : empview45 뷰를 사번이 ID_NUMBER로, 성이 EMPPLOYEE로, 직급이 JOB이 되도록 변경하시오.

SQL> CREATE OR REPLACE VIEW empview45 (id_number, employee, job)            AS SELECT id,last_name, title   ROM   s_emp   WHERE  dept_id = 45; SQL> SELECT * FROM empview45; SQL> SELECT * FROM dept_emp_view;

 

♣ Complex 생성

♠실습 : 부서의 이름과 최소급여, 최고급여, 평균급여에 대한 뷰를 만들고 뷰의 구조와 내용을 출력하시오.

SQL> CREATE VIEW dept_emp_view(name, minsal, maxsal, avgsal)           AS  SELECT d.name, min(e.salary), max(e.salary), avg(e.salary)                   FROM   s_emp e, s_dept d                     WHERE  e.dept_id = d.id                   GROUP BY d.name;

 

3. 뷰(View)에 대한 DML 문 사용

◈Simple 뷰에서는 DML연산을 수행.
◈뷰를 통하여 다음의 DML연산은 수행할 없다.

행을 삭제할 수 없는 경우	데이터를 수정할 수 없는 경우	데이터를 추가할 수 없는 경우
- 조인 조건 - 그룹 함수 - GROUP BY 절 - DISTINCT 명령	- 조인 조건 - 그룹 함수 - GROUP BY 절 - DISTINCT 명령	- 조인 조건 - 그룹 함수 - GROUP BY 절 - DISTINCT 명령
	- 식으로 정의된 Column.	- 뷰로 선택되지 않은NOT NULL Column.

◈위에 열거한 것 중 어느 것도 포함하지 않고 뷰에서 선택하지 않은 Base table에 NOT NULL
   Column이 있지 않으면 뷰를 통해 데이터를 추가할 수 있음.

 

♣ WITH CHECK OPTION 절의 이용

♠실습 : 부서번호 41에 대해 WITH CHECK OPTION절을 추가하여s_emp 테이블의 모든 column을
           갖는 뷰를 만드시오. 

SQL> CREATE OR REPLACE VIEW empview41             AS SELECT *                  FROM s_emp                  WHERE dept_id = 41                  WITH CHECK OPTION CONSTRAINT empview41_ck;

 

♠실습 : 뷰를 통해 사원 16의 부서번호를 42로 바꾸어 보시오.

SQL> UPDATE empview41   SET dept_id = 42  WHERE id =16;

◈Note : 부서번호가 42로 바뀌면 뷰는 더 이상 해당 사원을 볼 수 없기 때문에 error가 발생한다.
            따라서 WITH CHECK OPTION절을 써서 뷰는 단지 부서41의 사원만을 볼수 있고
            이 사원의 부서번호는 뷰를 통해서는 변경할 수 없게 한다.

♣ WITH READ ONLY 옵션

◈ 뷰 정의에 WITH READ ONLY옵션을 추가하여 DML연산이 수행될 수 없게 한다.
 

♠실습 : empview45 뷰를 DML연산이 수행되지 않게 수정하시오.

SQL> CREATE OR REPLACE VIEW empview45(id_number,employee,job)             AS SELECT  id,last_name,title FROM s_emp  WHERE dept_id = 45                  WITH READ ONLY;

 

♠실습 : 위에서 변경한 empview45 뷰에서 id_number가 10인 행을 삭제해 보시오.

SQL> DELETE FROM empview45 WHERE id_number = 10;

 

4. 뷰의 구조 및 이름 확인

◈USER_VIEWS Dictionary 뷰에서 해당 뷰의 query 문장과 뷰 이름을 확인할 수 있음

SQL> SELECT  view_name, text_length, text            FROM user_views;

 

♠실습 : USER_VIEWS Dictionary 뷰의 구조를 보라.

SQL> DESC user_views;

 

♠실습 : 사용자가 현재 소유한 모든 뷰의 이름과 내용을 출력하라.

SQL> SELECT * FROM user_views;

 

5. 뷰 삭제

◈뷰를 삭제 하려면 DROP VIEW 명령을 사용한다.
◈이 명령은 데이터베이스에서 뷰의 정의를 삭제한다.
◈뷰는 데이터베이스의 테이블을 토대로 하기 때문에, 데이터의 손실없이 뷰를 삭제할 수 있다.

♠실습 : empview45뷰를 삭제하시오.

SQL> DROP VIEW empview45;