(ORACLE강좌)오라클 뷰(ORACLE VIEW), 인라인 뷰(Inline View), WITH CHECK OPTION, CREATE VIEW, DROP VIEW,

오라클 뷰(ORACLE VIEW), 인라인 뷰(Inline View), WITH CHECK OPTION, CREATE VIEW, DROP VIEW,

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=639

 

오라클 뷰(ORACLE VIEW), 인라인 뷰(Inline View), WITH CHECK OPTION, CREATE VIEW, DROP VIEW,

오라클 뷰(ORACLE VIEW), 인라인 뷰(Inline View), WITH CHECK OPTION, CREATE VIEW, DROP VIEW,12. 뷰(VIEW) n 하나 또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 논리적인 테이블로 실제 데이터는 저장하지 않는다. n 뷰를 생성하게 되면 데이터 딕셔너리에 뷰에 대한 정의만 저장되고, 뷰의 기본이…

ojc.asia

12. 뷰(VIEW)

n 하나 또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 논리적인 테이블로 실제 데이터는 저장하지 않는다.

n 뷰를 생성하게 되면 데이터 딕셔너리에 뷰에 대한 정의만 저장되고, 뷰의 기본이 되는 테이블을 BASE TABLE 이라고 한다.

n 테이블의 전체 데이터중 일부칼럼, 일부 레코드만 보여주는 역할을 한다.

n 뷰를 통해 데이터를 입력, 수정, 삭제 가능하다.

n 뷰의 기본이 되는 테이블의 무결성 제약조건은 상속한다.

n 뷰 생성을 위해서는 CREATE VIEW 시스템 권한이 있어야 한다.

n 데이터의 보안과 사용 편의성을 제공한다.

[형식]

CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW view_name [(alias[,alias]...)]

AS Subquery

[WITH READ ONLY]

[WITH CHECK OPTION [CONSTRAINT constraint]];

OR REPLACE : 이미 뷰가 존재 한다면 RE-CREATE 하라는 의미

FORCE : BASE TABLE의 존재유무와 관계없이 뷰 생성

NO FROCE : 기본 값이며, BASE TABLE이 존재해야 뷰 생성 가능하다.

ALIAS : BASE TABLE의 칼럼명과 다르게 주는 경우

WITH READ ONLY : 읽기 전용 뷰 생성

WITH CHECK OPTION : 뷰의 생성쿼리가 SELECT 할 수 있는 데이터만 INSERT, UPDATE, DELETE 가능하다.

CONSTRAINT constraint : 체크 제약 조건의 이름

12.1 단순 뷰(Simple View)

n BASE TABLE이 하나인 경우이며, 뷰를 통한 DML(Update, Insert, Delete)이 가능하다.

n View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT를 포함하는 경우 로우를 Delete할 수 없다

n View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT, expression으로 정의된 Column을 포함하는 경우 로우를 Update 할 수 없다.

n View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT, expression으로 정의된 Column, View로 선택되지 않은 NOT NULL칼럼을 포함하는 경우 로우를 Insert 할 수 없다.

1. EMP 테이블에서 20번 부서 사원의 사번, 이름, 급여를 정의하는 v_emp20 이라는 이름의 단순뷰를 작성 하시오. SQL> create or replace view v_emp20 as select empno, ename, sal from emp where deptno = 20; 뷰가 생성되었습니다. SQL> select * from v_emp20; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 1000 7566 JONES 2975 7788 SCOTT 1000 7876 ADAMS 1100 7902 FORD 3000 2. 작성한 V_EMP20 뷰의 생성정보와 BASE 테이블과의 의존관계를 확인 하세요. SELECT text_length, text, read_only, view_type FROM user_views WHERE view_name = 'V_EMP20'; TEXT_LENGTH TEXT READ_ONLY --------------------------------------------------------- 89 "select empno, ename, sal N from emp where deptno = 20 and job='CLERK' with check option " SELECT * FROM DBA_DEPENDENCIES WHERE OWNER = 'SCOTT' AND NAME = 'V_EMP20' AND TYPE = 'VIEW'; NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPEREFERENCED_LINK_NAME ---------------------------------------------------------------------------------------------------------------- V_EMP20 VIEW SCOTT EMP TABLE

12.2 복합 뷰(Complex View)

n BASE TABLE이 여러 개이며 함수나 그룹함수 등을 SELECT LIST에 가진 경우.

n INSERT, UPDATE, DELETE 허용 되지 않는다.

1. EMP 테이블과 DEPT 테이블을 조인하여 10번 부서 사원들의 사번, 이름, 급여, 부서명을 정의하는 v_emp10 이라는 이름의 복합뷰를 작성 하시오. SQL> create or replace view v_emp10 as select empno, ename, sal, dname from emp, dept where emp.deptno = 10 and emp.deptno = dept.deptno; 뷰가 생성되었습니다. SQL> select * from v_emp10; EMPNO ENAME SAL DNAME ------ ---------- ---------- -------------- 7782 CLARK 2450 ACCOUNTING 7839 KING 5000 ACCOUNTING 7934 MILLER 1300 ACCOUNTING 2. EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰를 생성하시오. (함수를 이용하여 뷰를 생성하는 경우 반드시 칼럼 별명을 사용해야 한다.) SQL> create or replace view v_emp_sum_avg as select deptno, avg(sal), sum(sal) from emp group by deptno; select deptno, avg(sal), sum(sal) from emp * 3행에 오류: ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다 SQL> create or replace view v_emp_sum_avg as select deptno, avg(sal) avg, sum(sal) sum from emp group by deptno; 뷰가 생성되었습니다. SQL> select * from v_emp_sum_avg; DEPTNO AVG SUM ---------- ---------- ---------- 20 1815 9075 10 2916.66667 8750

12.3 인라인 뷰(Inline View)

n 인라인 뷰는 스키마 오브젝트는 아니지만 FROM절에서 SELECT를 사용하여 테이블 처럼 사용될 데이터를 정의할 때 사용한다. 임시 뷰라고 할 수 있으며 이름없이 해당 SQL 라인내에 기술한다 하여 인라인 뷰라고 한다.

n 주로 조인 연산시 테이블의 데이터를 줄이기 위해, 분리된 쿼리를 하나의 쿼리에 모아서 사용하기 위해 사용한다

1. 인라인뷰를 사용하여 사원 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하시오. select dname, avg_sal, sum_sal from ( select deptno, avg(sal) avg_sal, sum(sal) sum_sal from emp group by deptno) e, dept d where e.deptno = d.deptno; DNAME AVG_SAL SUM_SAL -------------- ---------- ---------- RESEARCH 1815 9075 ACCOUNTING 2916.66667 8750

12.4 WITH CHECK OPTION

n 뷰의 쿼리가 SELECT 할 수 있는 ROW에 대해 INSERT, UPDATE 가능 하다.

n USER_CONSTRAINTS 뷰를 통해 생성된 제약조건을 확인 가능하다.

1. WITH CHECK OPTION 에 대하여 실습 하시오. CREATE OR REPLACE VIEW v_emp1020 AS SELECT * FROM emp WHERE deptno = 10 Or deptno = 20 WITH CHECK OPTION CONSTRAINT ck_emp_1020; Select * from v_emp1020; Update v_emp1020 set deptno = 30; Update v_emp1020 set deptno = 30 * 1행에 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다 Insert into v_emp1020 (empno, ename, deptno) values (9998, '박정남' ,30); Insert into v_emp1020 (empno, ename, deptno) values (9998, '박정남' ,30) * 1행에 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다 -- 인라인 뷰에서도 with check option 사용 가능 하다. insert into (select empno, ename, deptno from emp where deptno=30) values (9998, '박정남' ,30); 1 개의 행이 만들어졌습니다. insert into (select empno, ename, deptno from emp where deptno=30 with check option) values (9997, '김정술' ,40); insert into (select empno, ename, deptno from emp where deptno=30 with check option) values (9997, '이종철' ,40) * 1행에 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다 2. USER_CONSTRAINTS 뷰를 통해 생성된 제약조건을 확인 할 수 있다. SELECT constraint_name, constraint_type, table_name, status FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'V_EMP1020'; CONSTRAINT_NAME C TABLE_NAME STATUS --------------------------------------------------------------------------------------------- CK_EMP_1020 V V_EMP1020 ENABLED

12.5 DROP VIEW

n 뷰를 삭제하는 명령.(USER_VIEWS 딕셔너리뷰에서 뷰 정의만 삭제됨)

n BASE TABLE은 삭제되지 않는다.

[형식]

DROP VIEW view_name;