오라클 단순 뷰(Oracle Simple View)
뷰에 포함된 테이블을 기반으로 뷰에 포함 된 테이블을 기반으로 단순 뷰simple view와 복합 뷰complex view로 구분할 수 있습니다.
단순 뷰는 BASE TABLE이 하나인 경우이며 단순히 컬럼을 나열한 것으로 행번호rownum, DISTINCT, 표현식expression, 조인, 함수, 집합함수(SUM, MAX, MIN, AVG, COUNT), GROUP BY를 사용하지 않으므로 뷰를 통한 DML(Insert, Update, Delete)이 가능 합니다. 또한 BASE TABLE의 NOT NULL 컬럼을 포함하지 않습니다.
뷰가 조인조건, 집합함수(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이(가) 삭제되었습니다.
#SimpleView, #단순뷰, #오라클뷰, #ORACLEVIEW, #오라클교육, #오라클강좌
