[오라클 교재] 제12장 VIEW

1. VIEW의 개념

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

 

EMP  TABLE

EMPNO ENAME      JOB             MGR HIREDATE                 SAL      COMM    DEPTNO --------- ---------- --------- --------- ------------------ --------- --------- ---------      7839 KING       PRESIDENT           17-NOV-81               5000                  10      7698 BLAKE      MANAGER        7839 01-MAY-81               2850                  30      7782 CLARK      MANAGER        7839 09-JUN-81               2450                  10      7566 JONES      MANAGER        7839 02-APR-81               2975                  20      7654 MARTIN     SALESMAN       7698 28-SEP-81               1250      1400        30      7499 ALLEN      SALESMAN       7698 20-FEB-81               1600       300        30      7844 TURNER     SALESMAN       7698 08-SEP-81               1500         0        30      7900 JAMES      CLERK          7698 03-DEC-81                950                  30      7521 WARD       SALESMAN       7698 22-FEB-81               1250       500        30      7902 FORD       ANALYST        7566 03-DEC-81               3000                  20      7369 SMITH      CLERK          7902 17-DEC-80                800                  20      7788 SCOTT      ANALYST        7566 09-DEC-82               3000                  20      7876 ADAMS      CLERK          7788 12-JAN-83               1100                  20      7934 MILLER     CLERK          7782 23-JAN-82               1300                  10

EMP_10  VIEW

EMPNO ENAME      JOB             MGR HIREDATE                 SAL      COMM    DEPTNO --------- ---------- --------- --------- ------------------ --------- --------- ---------      7839 KING       PRESIDENT           17-NOV-81               5000                  10      7782 CLARK      MANAGER        7839 09-JUN-81               2450                  10      7934 MILLER     CLERK          7782 23-JAN-82               1300                  10

 

1.1 VIEW의 장점

1)       VIEW은 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다.

2)       복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다.

3)       데이터 독립성을 허용한다.

4)       동일한 데이터의 다른 VIEW를 나타낸다.

5)       조인을 한 것처럼 여러 테이블에 대한 데이터를 VIEW을 통해볼 수 있다.

6)       한 개의 VIEW로 여러 테이블에 대한 데이터를 검색할 수 있다.

7)       특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있다.

 

 

 

1.2 VIEW의 종류

1.2.1 Simple VIEW

1)       오직 하나의 테이블에서만 데이터가 유래된다.

2)       데이터 그룹 또는 함수를 포함하지 않는다.

3)       VIEW를 통해 DML 수행 가능

 

1.2.3 Complex VIEW

1)       다중 테이블에서 데이터가 유래된다.

2)       데이터 그룹 또는 함수를 포함한다.

3)       VIEW을 통한 DML을 항상 허용하지 않는다.

 

1.3 VIEW의 생성

1)       CREATE VIEW문장 내에서 SUBQUERY을 내장하여 VIEW를 생성한다.

2)       SUBQUERY은 복합 SELECT 구문을 포함할 수 있고 ORDER BY 절을 포함할 수 없다.

 

1.3.1 Syntax

CREATE  [OR  REPLACE]  [FORCE | NOFORCE]  VIEW view_name [(alias[,alias,...])] AS Subquery [WITH  CHECK  OPTION  [CONSTRAINT  constraint ]] [WITH  READ  ONLY]

OR  REPLACE          이미 존재한다면 다시 생성한다.

FORCE                Base Table 유무에 관계없이 VIEW을 만든다.

NOFORCE              기본 테이블이 존재할 경우에만 VIEW를 생성한다.

view_name            VIEW의 이름

Alias                Subquery를 통해 선택된 값에 대한 Column명이 된다.

Subquery             SELECT문장을 기술한다.

WITH CHECK OPTION    VIEW에 의해 액세스 될 수 있는 행만이 입력,갱신될 수 있다.

Constraint           CHECK OPTON제약 조건에 대해 지정된 이름이다.

WITH READ ONLY               이VIEW에서 DML이 수행될 수 없게 한다.

 

☞ Guidelines

1)       VIEW을 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT문장으로 구성될 수 있다.

2)       VIEW을 정의하는 질의어에는 ORDER BY 절을 쓸 수 없다.

3)       제약 조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.

4)       VIEW을 삭제하거나 재생성하지 않고 VIEW의 정의를 변경하려면 OR REPLACE옵션을 쓸 수 있다.

문제1) EMP 테이블에서 20번 부서의 세부 사항을 포함하는 EMP_20 VIEW를 생성 하여라

SQL> CREATE VIEW emp_20   2  AS  SELECT *   3      FROM emp   4      WHERE deptno = 20;   View created.   SQL> DESC emp_20  Name                            Null?    Type  ------------------------------- -------- ----  EMPNO                           NOT NULL NUMBER(4)  ENAME                                    VARCHAR2(10)  JOB                                      VARCHAR2(9)  MGR                                      NUMBER(4)  HIREDATE                                 DATE  SAL                                      NUMBER(7,2)  COMM                                     NUMBER(7,2)  DEPTNO                          NOT NULL NUMBER(2)   SQL> SELECT *   2  FROM emp_20;    EMPNO ENAME      JOB             MGR HIREDATE         SAL      COMM    DEPTNO ------ ---------- --------- --------- ---------- --------- --------- ---------   7566 JONES      MANAGER        7839 02-APR-81       2975                  20   7902 FORD       ANALYST        7566 03-DEC-81       3000                  20   7369 SMITH      CLERK          7902 17-DEC-80        800                  20   7788 SCOTT      ANALYST        7566 09-DEC-82       3000                  20   7876 ADAMS      CLERK          7788 12-JAN-83       1100                  20

 

문제2) EMP 테이블에서 30번 부서만 EMPNO를 EMP_NO로 ENAME를 NAME로 SAL를 SALARY로 바꾸어 EMP_30 VIEW를 생성 하여라.

SQL> CREATE VIEW EMP_30   2  AS SELECT empno emp_no,ename name, sal salary   3  FROM emp   4  WHERE deptno = 30;   View created.

SQL> CREATE VIEW EMP_30   2  AS SELECT empno emp_no,ename name, sal salary   3  FROM emp   4  WHERE deptno = 30; FROM emp      * ERROR at line 3: ORA-00955: name is already used by an existing object        위와 같은 ERROR는 이미 EMP_30이라는 OBJECT(table)가 존재한다는        의미이므로 EMP_30을 DROP 한 후 다시 생성하여라.   SQL> DROP TABLE emp_30;   Table dropped. SQL> CREATE VIEW EMP_30   2  AS SELECT empno emp_no,ename name, sal salary   3  FROM emp   4  WHERE deptno = 30;   View created.

 

1.4  VIEW의 구조 및 이름 확인

일단 VIEW가 생성되면, VIEW의 이름과 VIEW 정의를 보기 위해 USER_VIEWS라는 데이터 사전 테이블을 질의할 수 있습니다. VIEW을 만드는 SELECT문장의 텍스트는 LONG열에 저장됩니다.

 

문제3) 현재 SESSION를 이루고 있는 사용자가 소유한 VIEW를 조회하시오.

SQL> COL view_name FORMAT a15 SQL> COL text_length FORMAT 99,990 SQL> COL text FORMAT a40 SQL> SELECT *   2  FROM user_views;   VIEW_NAME       TEXT_LENGTH TEXT --------------- ----------- ---------------------------------------- EMP_20                  103 SELECT "EMPNO","ENAME","JOB","MGR","HIRE                             DATE","SAL","COMM","DEPTNO"                                 FROM emp EMP_30                   70 SELECT empno emp_no,ename name, sal sala                             ry                             FROM emp                             WHERE deptno = 30 SALES                   291 SELECT REPID, ORD.CUSTID, CUSTOMER.NAME                             CUSTNAME, PRODUCT.PRODID,                             DESCRIP PRODNA

 

1.5 데이터 액세스 VIEW

VIEW을 사용하여 데이터를 액세스할 때 ORACLE SERVER은 다음 작업을 수행합니다.

1)       USER_VIEWS데이터 사전 테이블에서 VIEW 정의를 검색합니다.

2)       VIEW 기반 테이블에 대한 액세스 권한을 확인합니다.

3)       VIEW 질의를 기본 테이블 또는 테이블들에서의 동등한 작업으로 전환합니다.

 

문제4) emp_30의 VIEW에서 자료를 조회하여라.

SQL> SELECT *   2  FROM emp_30;   EMP_NO NAME          SALARY --------- ---------- ---------      7698 BLAKE           2850      7654 MARTIN          1250      7499 ALLEN           1600      7844 TURNER          1500      7900 JAMES           2450      7521 WARD            1250   6 rows selected.

 

1.5.1 VIEW의 수정

OR REPLACE옵션은 비록 이 이름이 이미 존재할지라도 VIEW가 생성될 수 있도록 해주므로 그 소유자에 대한 오래된 VIEW 버전업할 수 있다.

 

문제5) 부서번호 10번만 포함하고 있는 이미 생성되어 있는 VIEW의 내용을 사원번호(employee_no),이름(employee_name),업무(job_title)의 내용으로 변경하여라.

SQL> CREATE OR REPLACE VIEW emp_10   2  (employee_no,employee_name,job_title)   3  AS SELECT empno,ename,job   4  FROM emp   5  WHERE deptno = 10;   View created.

 

♣ 주의

CREATE VIEW절에서 열 별칭을 지정할 때 별칭은 SUBQUERY의 열과 동일한 명령으로 나열됨을 명심하십시오.

 

1.6 복합 VIEW 생성

두 테이블로부터 값을 출력하는 그룹 함수를 포함하는 복잡한 VIEW를 생성합니다. VIEW의 어떤 열이 함수나 표현식에서 유래되었다면 별칭은 필수적입니다.

 

 

 

 

문제6) 부서별로 부서명,최소 급여,최대 급여,부서의 평균 급여를 포함하는 DEPT_SUM VIEW을 생성하여라.

SQL> CREATE VIEW dept_sum (name,minsal,maxsal,avgsal)   2  AS SELECT d.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)   3  FROM dept d,emp e   4  WHERE d.deptno = e.deptno   5  GROUP BY d.dname;   View created.

 

1.7 VIEW에서 DML연산 수행

1)       단순 VIEW에서 DML연산을 수행할 수 있습니다.

2)       VIEW가 다음을 포함 한다면 행을 제거할 수 없습니다.

①       그룹 함수

②       GROUP BY절

③       DISTINCT키워드

3)       다음을 포함한다면 VIEW에서 데이터를 수정할 수 없습니다.

①       그룹 함수

②       GROUP BY절

③       DISTINCT키워드

④       표현식으로 정의된 열

⑤       ROWNUM의사열

1)       다음을 포함한다면 VIEW에서 데이터를 추가할 수 없습니다.

①       그룹 함수

②       GROUP BY절

③       DISTINCT키워드

④       표현식으로 정의된 열

⑤       ROWNUM의사열

⑥       VIEW에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 경우

 

1.8 WITH CHECK OPTION절 사용

VIEW을 통해 참조 무결성 체크를 수행하는 것이 가능합니다. 또한 데이터베이스 LEVEL에서 제약 조건을 적용할 수 있습니다. VIEW은 데이터 무결성을 보호하기 위해 사용될 수 있지만, 사용은 매우 제한됩니다. VIEW을 통해 수행되는 INSERT 와UPDATE는 WITH CHECK OPTION 절이 있으면 VIEW을 가지고 검색할 수 없는 행 생성을 허용하지 않음을 명시합니다. 그러므로 삽입되거나 갱신되는 데이터에 대해서 무결성 제약 조건과 데이터 검증 체크를 허용합니다. VIEW가 선택하지 않은 행에 대해 DML작업을 수행하려고 하면, 지정된 제약 조건 명과 함께 에러가 출력됩니다.

문제7) EMP 테이블과 동일한 EMP_20(20번 부서만)이라는 VIEW를 WITH CHECK OPTION을 사용하여 생성하여라.

SQL> CREATE OR REPLACE VIEW emp_20   2  AS SELECT *   3  FROM emp   4  WHERE deptno = 20   5  WITH CHECK OPTION CONSTRAINT emp_20_ck;   View created. SQL> UPDATE emp_20   2  SET deptno = 30   3  WHERE empno = 7566; UPDATE emp_20        * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

 

♣ 참고

부서번호가 30으로 변경된다면 VIEW은 더 이상 그 종업원들을 볼 수 없기 때문에 아무 행도 갱신되지 않습니다. 그러므로 WITH CHECK OPTION절로 VIEW은 부서20종업원만 볼 수 있고, 이 종업원들에 대한 부서번호가 VIEW을 통해 변경되는 것을 허용하지 않습니다.

 

1.9 DML연산 부정

1)       WITH READ ONLY옵션으로 VIEW을 생성하면 VIEW에서 DML연산을 수행할 수 없습니다.

2)       VIEW에서 임의의 행에서 DML연산을 수행하려고 하면 ORACLE SERVER 에러 ORA-01752가 발생합니다.

 

문제8) EMP 테이블에서 EMP_10(10번 부서 중EMPNO,ENAME,JOB)이라는 VIEW를 WITH READ ONLY OPTION을 사용하여 생성하여라.

SQL> CREATE OR REPLACE VIEW emp_10   2  AS SELECT empno,ename,job   3  FROM emp   4  WHERE deptno = 10   5  WITH READ ONLY;   View created. SQL> DELETE FROM emp_10   2  WHERE empno = 7782; DELETE FROM emp_10             * ERROR at line 1: ORA-01752: cannot delete from view without exactly one key-preserved table

 

1.10 VIEW의 제거

VIEW는 데이터베이스에서 기본 테이블을 기반으로 하기 때문에 데이터 손실 없이 VIEW를 제거할 수 있다.

 

1.10.1 Syntax

DROP  VIEW  view_name;

 

문제9) 앞에서 생성한 EMP_10,EMP_20 VIEW을 삭제하여라.

SQL> DROP VIEW emp_10;   View dropped.   SQL> DROP VIEW emp_20;   View dropped.

 

 

◈ 연 습 문 제 ◈

 

1. EMP 테이블에서 사원 번호,이름,업무를 포함하는 EMP_VIEW VIEW를 생성하여라.

 

 

2. 1번에서 생성한 VIEW를 이용하여 10번 부서의 자료만 조회하여라.

 

 

3. 1번에서 생성한 VIEW를 DATA DICTIONARY에서 조회하여라.

 

 

4. EMP 테이블과 DEPT 테이블을 이용하여 이름,업무,급여,부서명,위치를 포함하는 EMP_DEPT_NAME이라는 VIEW를 생성하여라.

 

 

5. VIEW 생성시 WITH READ ONLY OPTION에 대하여 설명하여라.

 

 

6. VIEW 생성시 WITH CHECK OPTION에 대하여 설명하여라.

 

 

7. VIEW를 이용하여 자료를 수정할 수 있는 경우와 없는 경우에 대하여 설명하여라.