(오라클동영상교육, 오라클학원교육, ORACLE학원교육)오라클 뷰(Oracle View)
오라클자바커뮤니티(http://ojc.asia) 이종철
https://www.youtube.com/watch?v=agFbSyrV22A&list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS&index=22
강의 목표오라클 뷰views의 개념 및 종류를 이론 및 실습을 통해 이해 합니다.강의 순서
실습 환경 오라클19C |
뷰는 하나이상의 테이블 또는 다른 뷰를 기반으로 만들어진 가상의 창(WINDOW) 같은 것으로 뷰를 만드는 SQL문장만 오라클 딕셔너리에 저장되고 SQL문에서 테이블 처럼 사용가능한 가상 테이블 입니다.
뷰 종류
하나의 BASE TABLE을 기준으로 단순히 컬럼만 갖고 와서 사용하는 단순 뷰simple view, 두개 이상의 BASE TABLE로 구성되고 조인, GROUP BY, 컬럼 표현식등을 사용할 수 있는 복합 뷰complex view, FROM 절 아래 서브 쿼리 형태로 SQL문 안에서 사용되는 인라인 뷰inline view, 뷰 자체가 실제 데이터도 가지고 있으면서 그룹함수(MIN, MAX, SUM, AVG, COUNT) 튜닝을 위해 사용되는 구체화 뷰materialized view등이 있습니다.
뷰를 사용하는 장점
1. 뷰의 주요 목적은 데이터를 저장하기 위해 테이블을 만들지 않고도 다른 테이블 및 뷰를 결합하여 새로운 데이터 형식을 만들 수 있습니다.
2. 뷰는 복잡성을 숨길 수 있는데 여러 테이블을 조인해야 하거나 복잡한 논리 또는 계산이 필요한 쿼리가있는 경우 복잡한 부분을 뷰안으로 숨기고 사용자에게는 뷰 만 보여줍니다.
3. 뷰는 보안 메커니즘으로 사용될 수 있는데, 사용자에게 필요한 데이터만 보여주고 나머지는 숨길 수 있습니다. 뷰는 테이블에서 특정 컬럼 및 행을 선택하여 보여줄 수 있습니다. 이를 통해 사용자가 봐야하는 데이터 만 표시 할 수 있는 것이죠.
4. 잘못설계된 테이블을 리팩터링 해야하는 경우 테이블을 동일한 이름의 뷰로 바꿀 수 있습니다. 뷰는 원래 테이블과 똑같은 스키마를 제공하지만 실제 테이블은 아닙니다. 하지만 테이블을 참조하는 기존 SQL코드가 오류가 발생하지 않으므로 시간을 두고 기존 SQL을 변경할 수 있습니다.
뷰(View)란?
뷰view는 하나 또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 가상 테이블로 실제 데이터는 저장하지 않고 뷰를 만드는 SELECT 쿼리문만 오라클 딕셔너리에 저장합니다. 그래서 뷰는 데이터베이스에 저장된 명명 된 쿼리라고 하며, 뷰에서 데이터를 쿼리 할 때 오라클은이 저장된 쿼리를 사용하여 기본 테이블base tables에서 데이터를 검색합니다.
뷰는 데이터 접근을 제한하는 용도로 사용하는데, 테이블 전체 데이터중 일부칼럼, 일부 레코드만 보여줌으로써 데이터의 보안과 사용 편의성을 제공 합니다. EMP 테이블에서 10번 부서만 보는 뷰를 만들수도 있고 empno, ename 칼럼만 보는 뷰도 만들수도 있습니다.
뷰의 기본이 되는 테이블을 BASE TABLE 이라고 하며 뷰를 통한 데이터의 입력, 수정, 삭제 가 가능합니다. 뷰의 기본이 되는 BASE TABLE의 무결성 제약조건은 상속됩니다. 또한 뷰자체를 수정할 수는 없습니다.
뷰 생성을 위해서는 CREATE VIEW 시스템 권한이 있어야 합니다.
[그림13.1 뷰개요]
[기본형식]
| CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW view_name [(alias[,alias]...)] AS Subquery [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT constraint_name]]
|
CREATE VIEW 문을 사용하여 하나 이상의 테이블 또는 뷰를 기반으로하는 논리 테이블 인 뷰를 정의할 수 있습니다.
일반 뷰와 비슷하지만 XMLType의 XMLSchema기반 테이블에서 데이터를 표시하는 XMLType 뷰를 만들 수도 있지만 자주 사용하지 않으므로 본 강좌에서는 소개하지는 않습니다.
단순 뷰(Simple View)
뷰에 포함된 테이블을 기반으로 뷰에 포함 된 테이블을 기반으로 단순 뷰simple view와 복합 뷰complex view로 구분할 수 있습니다.
단순 뷰는 BASE TABLE이 하나인 경우이며 단순히 컬럼을 나열한 것으로 행번호rownum, DISTINCT, 표현식expression, 조인, 함수, 집합함수(SUM, MAX, MIN, AVG, COUNT), GROUP BY를 사용하지 않으므로 뷰를 통한 DML(Insert, Update, Delete)이 가능 합니다.
뷰가 조인조건, 집합함수(SUM, MAX, MIN, AVG, COUNT), GROUP BY 구, DISTINCT를 포함하는 경우 행row(로우)을 삭제할 수 없습니다.
뷰가 조인조건, 집합함수, GROUP BY 구, DISTINCT, 표현식(EXPRESSION)으로 정의된 컬럼을 포함하는 경우 행을 수정할 수 없습니다.
뷰가 조인조건, 집합함수, GROUP BY 구, DISTINCT, 표현식으로 정의된 컬럼, View로 선택되지 않은 NOT NULL칼럼을 포함하는 경우 행을 입력할 수 없습니다.
EMP 테이블을 통해 단순 뷰를 만들고 딕셔너리 뷰에서 확인을 하는 실습을 해보겠습니다.
실습
단순 뷰를 생성해 보겠습니다.
| EMP 테이블에서 20번 부서 사원의 사번(empno, 이름(ename), 직무(job), 부서(deptno)를 정의하는 V_EMP20 이라는 이름의 단순 뷰를 작성 하세요. |
CREATE OR REPLACE VIEW V_EMP20
AS
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
<실행결과>
View V_EMP20이(가) 생성되었습니다.
OR REPLACE구는 이미 있다면 대체하라는 의미로 기존에 V_EMP20 뷰가 있더라도 오류가 발생하지 않습니다.
생성된 뷰를 통해 데이터를 조회해 보겠습니다.
| V_EMP20를 통해 데이터를 조회하세요. |
SELECT * FROM V_EMP20;
<실행결과>
| EMPNO | ENAME | JOB | DEPTNO | |
| 1 | 7369 | SMITH | CLERK | 20 |
| 2 | 7566 | JONES | MANAGER | 20 |
| 3 | 7788 | SCOTT | ANALYST | 20 |
| 4 | 7876 | ADAMS | CLERK | 20 |
| 5 | 7902 | FORD | ANALYST | 20 |
USER_VIEWS 딕셔너리 뷰를 통해 사용자가 생성한 뷰 정보를 조회할 수 있는데, V_EMP20 뷰 생성 정보를 조회해 보겠습니다.
| V_EMP20의 생성 SQL 스크립트를 조회 하세요. |
SELECT TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_EMP20';
<실행결과>
| TEXT | |
| 1 | SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO = 20 |
USER_DEPENDENCIES 딕셔너리 뷰는 객체간 참조하는 정보를 조회할 수 있는데 이를 통해 뷰의 BASE TABLE과의 의존관계를 확인 할 수 있습니다.
| V_EMP20의 의존관계를 조회하세요. |
SELECT NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME
, REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME = 'V_EMP20'
AND TYPE = 'VIEW';
<실행결과>
| NAME | TYPE | REFERENCED_OWNER | REFERENCED_NAME | REFERENCED_TYPE | |
| 1 | V_EMP20 | VIEW | SCOTT | EMP | TABLE |
USER_DEPENDENCIES 뷰의 NAME은 객체명, TYPE은 객체 타입, REFERENCED_OWNER는 의존객체인 EMP의 소유자, REFERENCED_NAME은 의존하는 객체명인 BASE TABLE의 이름을 나타내며, REFERENCED_TYPE은 BASE TABLE의 TYPE을 표시합니다.
앞에서 생성한 뷰를 통해 데이터를 입력/수정/삭제 후 뷰를 삭제해 보겠습니다..
실습
뷰를 통해 데이터를 입력 합니다.
| V_EMP20 뷰를 통해 empno(9999), ename(홍길동), job(SALESMAN), deptno(10) 데이터를 입력하세요. |
INSERT INTO V_EMP20 VALUES (9999,'홍길동','SALESMAN', 10);
<실행결과>
1 행 이(가) 삽입되었습니다.
V_EMP20 뷰는 EMP 테이블을 BASE TABLE로 해서 20번 부사원들을 SELECT 하여 작성된 뷰 입니다. 그런데 10 번 부서원인 9999/홍길동 사원이 잘 입력 되며, 입력된 사원은 V_EMP20 뷰를 조회하면 조회되지 않고 EMP 테이블을 SELECT 해보면 확인할 수 있습니다.
V_EMP20 뷰를 SELECT 해 보겠습니다.
| V_EMP20 뷰에서 입력된 9999/홍길동 사원을 확인하세요. |
SELECT * FROM V_EMP20;
<실행결과>
| EMPNO | ENAME | JOB | DEPTNO | |
| 1 | 7369 | SMITH | CLERK | 20 |
| 2 | 7566 | JONES | MANAGER | 20 |
| 3 | 7788 | SCOTT | ANALYST | 20 |
| 4 | 7876 | ADAMS | CLERK | 20 |
| 5 | 7902 | FORD | ANALYST | 20 |
9999/홍길동 사원은 조회되지 않습니다.
EMP 테이블을 SELECT 해 보겠습니다.
| EMP 테이블에서 입력된 9999/홍길동 사원을 확인하세요. |
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP;
<실행결과>
| EMPNO | ENAME | JOB | DEPTNO | |
| 1 | 7369 | SMITH | CLERK | 20 |
| 2 | 7499 | ALLEN | SALESMAN | 30 |
| ... | ... | ... | ... | ... |
| 14 | 7934 | MILLER | CLERK | 10 |
| 15 | 9999 | 홍길동 | SALESMAN | 10 |
9999/홍길동 사원이 맨 아래 조회 됩니다. 이 데이터가 V_EMP20 뷰를 통해 입력이 되었지만 아쉬운 부분도 있습니다. 이 뷰는 EMP 테이블에서 20번 부서원들을 보여주는 가상 테이블 역할을 하는데 뷰를 통해 20 번 부서원이 아닌 다른 부서원이 입력되는 것이 꺼림직 합니다. 그래서 V_EMP20 뷰를 통해서는 20 부서원들만 입력이 되도록 하고 싶습니다.
9999/홍길동 사원 데이터를 삭제하고 WITH CHECK OPTION을 사용하여 V_EMP20 뷰를 재작성 합니다.
| EMP 테이블에서 9999/홍길동 데이터를 삭제합니다. |
DELETE FROM EMP WHERE EMPNO = 9999;
<실행결과>
1 행 이(가) 삭제되었습니다.
WITH CHECK OPTION 옵션은 뷰가 SELECT 할 수 있는 20번 부서 데이터만 입력이 가능하도록 하고 수정도 20번 부서코드가 아닌 다른 부서코드로 수정을 못하도록 합니다.
| WITH CHECK OPTION을 사용하여 V_EMP20 뷰를 다시 작성하세요. |
CREATE OR REPLACE VIEW V_EMP20
AS
SELECT EMPNO, ENAME, JOB , DEPTNO
FROM EMP
WHERE DEPTNO = 20
WITH CHECK OPTION;
<실행결과>
View V_EMP20이(가) 생성되었습니다.
WITH CHECK OPTION으로 생성된 제약조건을 확인 해 보겠습니다.
| USER_CONSTRAINTS 뷰를 통해 생성된 제약조건을 확인 하세요. |
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'V_EMP20';
<실행결과>
| CONSTRAINT_NAME | CONSTRAINT_TYPE | TABLE_NAME | STATUS | |
| 1 | SYS_C007664 | V | V_EMP20 | ENABLED |
CONSTRAINT_TYPE의 ‘V’는 VIEW임을 나타내고 현재 상태는 활성화(ENABLES) 상태 입니다. 생성 시 WITH CHECK OPTION 다음에 CONSTRAINT구를 사용하여 제약조건 이름을 기술하지 않았으므로 제약조건의 이름(CONSTRAINT_NAME)은 오라클에서 임의로 부여 하였습니다.
뷰를 통해 데이터를 입력 합니다.
| V_EMP20 뷰를 통해 empno(9999), ename(홍길동), job(SALESMAN), deptno(10) 데이터를 입력하세요. |
INSERT INTO V_EMP20 VALUES (9999,'홍길동','SALESMAN', 10);
<실행결과>
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다.
10번 부서원은 V_EMP20 뷰가 SELECT 할 수 있는 데이터가 아니므로 입력이 불가능 합니다.
뷰를 통해 데이터를 수정 합니다.
| V_EMP20 뷰를 통해 7369/SMITH 사원의 부서를 10으로 변경하세요. |
UPDATE V_EMP20
SET DEPTNO = 10
WHERE EMPNO = 7369;
<실행결과>
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
7369/SMITH 사원의 원래 부서는 20번이고 이를 10번으로 변경하고자 했으나 WITH CHECK OPTION으로 생성된 뷰 이므로 다른 부서로 수정이 불가능 합니다. 20번 부서로 수정은 가능하니 직접해보세요. 하지만 20번 부서원을 20번으로 수정하는 것이 의미는 없겠죠.
뷰를 통해 9999/홍길동 데이터를 입력하는데 이번에는 20번 부서로 입력 합니다.
| V_EMP20 뷰를 통해 empno(9999), ename(홍길동), job(SALESMAN), deptno(20) 데이터를 입력하세요. |
INSERT INTO V_EMP20 VALUES (9999,'홍길동','SALESMAN', 20);
<실행결과>
1 행 이(가) 삽입되었습니다.
뷰의 SELECT 쿼리가 조회할 수 있는 데이터 이므로 잘 입력됩니다.
뷰를 통해 9999/홍길동 데이터의 직무(job)를 “MANAGER”로 변경 합니다.
| V_EMP20 뷰를 통해 9999/홍길동 사원의 직무(job)를 “MANAGER”로 변경하세요. |
UPDATE V_EMP20
SET JOB = 'MANAGER'
WHERE EMPNO = 9999;
<실행결과>
1 행 이(가) 업데이트되었습니다.
뷰를 통해 7499/ALLEN 사원의 직무(job)를 “MANAGER”로 변경 합니다. 7499 사원은 30번 부서코드를 가지고 있습니다.
| V_EMP20 뷰를 통해 7499/ALLEN 사원의 직무(job)를 “MANAGER”로 변경하세요. |
UPDATE V_EMP20
SET JOB = 'MANAGER'
WHERE EMPNO = 7499;
<실행결과>
0개 행 이(가) 업데이트되었습니다.
7499/ALLEN 사원은 30번 부서원이고 V_EMP20 뷰가 SELECT 할수 있는 데이터가 아니므로 WHERE절의 조건이 맞지않아 수정되는 행은 없습니다.
뷰를 통해 9999/홍길동 사원의 데이터를 삭제 합니다.
| V_EMP20 뷰를 통해 9999/홍길동 사원의 데이터를 삭제하세요. |
DELETE FROM V_EMP20
WHERE EMPNO = 9999;
<실행결과>
1 행 이(가) 삭제되었습니다.
20번 부서원이므로 잘 삭제 됩니다.
뷰를 통해 7499/ALLEN 사원을 삭제해 보겠습니다. 7499 사원은 30번 부서코드를 가지고 있습니다.
| V_EMP20 뷰를 통해 7499/ALLEN 사원의 직무(job)를 “MANAGER”로 변경하세요. |
DELETE FROM V_EMP20
WHERE EMPNO = 7499;
<실행결과>
0개 행 이(가) 삭제되었습니다.
7499/ALLEN 사원은 30번 부서원이고 V_EMP20 뷰가 SELECT 할 수 있는 데이터가 아니므로 WHERE절의 조건이 맞지않아 삭제되는 행은 없습니다.
뷰를 삭제해 보겠습니다.
| V_EMP20 뷰를 삭제하세요. |
DROP VIEW V_EMP20;
<실행결과>
View V_EMP20이(가) 삭제되었습니다.
복합 뷰(Complex View)
복합 뷰complex view는 BASE TABLE이 하나 이상이고 조인, 함수, GROUP BY절이 포함될 수 있으며 INSERT/UPDATE/DELETE와 같은 DML 작업은 허용되지 않습니다.
EMP 테이블과 DEPT 테이블을 조인하여 복합 뷰를 생성하고 삭제하는 실습을 해보겠습니다.
뷰를 삭제해 보겠습니다.
실습
EMP, DEPT 두 테이블을 조인한 복합 뷰를 생성합니다.
| EMP 테이블과 DEPT 테이블을 조인하여 10번 부서 사원들의 사번(empno), 이름(ename), 직무(job), 부서명(dname)을 정의하는 v_emp10 이라는 이름의 복합뷰를 작성 하시오. (사번, 이름, 직무 컬럼은 EMP 테이블에 있고 부서명 컬럼은 DEPT 테이블에 존재합니다.) |
CREATE OR REPLACE VIEW V_EMP10
AS
SELECT EMPNO, ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = 10
AND EMP.DEPTNO = DEPT.DEPTNO;
<실행결과>
View V_EMP10이(가) 생성되었습니다.
생성한 V_EMP10 뷰를 조회 합니다.
| V_EMP10 뷰의 데이터를 확인 하세요. |
SELECT * FROM V_EMP10;
<실행결과>
| EMPNO | ENAME | JOB | DNAME | |
| 1 | 7782 | CLARK | MANAGER | ACCOUNTING |
| 2 | 7934 | MILLER | CLERK | ACCOUNTING |
EMP 테이블에서 집합함수(SUM, MAX, MIN, AVG, COUNT), GROUP BY를 이용하여 복합 뷰를 생성해 보겠습니다.. 함수를 이용하여 뷰를 생성하는 경우 반드시 칼럼 별명을 사용해야 합니다
| EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰 V_EMP_SUM_AVG를 생성 하세요 |
CREATE OR REPLACE VIEW V_EMP_SUM_AVG
AS
SELECT DEPTNO, AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
<실행결과>
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다.
AVG, SUM 함수를 사용했으므로 컬럼 별칭column alias을 사용해서 뷰의 컬럼명을 정의해야 합니다.
컬럼 별명을 사용해서 다시 생성해 보겠습니다.
| EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰 V_EMP_SUM_AVG를 생성 하세요 |
CREATE OR REPLACE VIEW V_EMP_SUM_AVG
AS
SELECT DEPTNO, AVG(SAL) AVG, SUM(SAL) SUM
FROM EMP
GROUP BY DEPTNO;
<실행결과>
View V_EMP_SUM_AVG이(가) 생성되었습니다.
#오라클뷰, #ORACLE뷰, #오라클VIEW, #오라클교육, #오라클학원, #ORACLE, #ORACLE교육, #ORACLE학원, #VIEW, #뷰란, #오라클동영상, #ORACLE동영상, #오라클강의, #ORACLE강의, #ORACLE강좌, #오라클강좌, 오라클뷰, ORACLE뷰, 오라클VIEW, 오라클교육, 오라클학원, ORACLE, ORACLE교육, ORACLE학원, VIEW, 뷰란, 오라클동영상, ORACLE동영상, 오라클강의, ORACLE강의, ORACLE강좌, 오라클강좌
댓글 없음:
댓글 쓰기