인라인 뷰inline view는 실제 뷰가 아니라 SELECT의 FROM절에 있는 서브 쿼리sub query로 FROM 절에서 테이블 처럼 사용될 데이터를 정의할 때 사용 합니다. FROM절의 테이블을 대체 할 수 있다고 해서 파생 테이블이라고도 부릅니다.
0열 선택0열 다음에 열 추가
0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
SELECT 컬럼리스트 FROM 테이블;
SELECT 컬럼리스트 FROM ( SELECT * FROM 테이블 ) t;
셀 병합
행 분할
열 분할
너비 맞춤
삭제
서브 쿼리는 FROM절에서도 사용 가능 한데 이를 뷰(View)인데 CREATE VIEW 명령어로 만들지 않고 SQL문의 해당 라인에 직접 기술한다고 해서 인라인 뷰(InLine View)라고 합니다. SQL문 라인에 직접 기술하니 필요한 시점에만 사용되는 특징이 있습니다.
인라인 뷰를 사용하면 조인 작업을 제거하거나 별도의 쿼리를 하나의 쿼리로 통합해서 복잡한 쿼리를 단순화 할 수 있습니다. 또한 복잡한 조인 연산시 조인의 타겟 테이블의 데이터 개수를 줄일 수 있습니다.
인라인뷰를 이용한 쿼리 예문을 실습해 보겠습니다.
실습
0열 선택0열 다음에 열 추가
0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
실습을 위한 사원(EMP), 부서(DEPT) 테이블 데이터를 확인
인라인뷰를 사용하여 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)
뷰는 하나이상의 테이블 또는 다른 뷰를 기반으로 만들어진 가상의 창(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의 무결성 제약조건은 상속됩니다. 또한 뷰자체를 수정할 수는 없습니다.
WITH CHECK OPTION : 뷰의 생성 쿼리가 SELECT 할 수 있는 데이터만 INSERT, UPDATE, DELETE 가능.
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과의 의존관계를 확인 할 수 있습니다.
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 작업은 허용되지 않습니다.
