뷰(VIEW)
하나또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 논리적인 테이블로 실제 데이터는 저장하지 않는다.
뷰를 생성하게 되면 데이터 딕셔너리에 뷰에 대한 정의만 저장되고 뷰의 기본이 되는 테이블을 BASE TABLE 이라고 한다.
테이블의 전체 데이터중 일부칼럼, 일부 레코드만 보여주는 역할을 한다.
뷰를 통해 데이터를 입력, 수정, 삭제 가능하다.
뷰의 기본이 되는 테이블의 무결성 제약조건은 상속한다.
뷰 생성을 위해서는 CREATE VIEW 시스템 권한이 있어야 한다.
데이터의 보안과 사용 편의성을 제공한다.
[형식]
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, IPDATE, DELETE 가능
CONSTRAINT constraint : 체크 제약 조건의 이름
11.1 단순 뷰(Simple View)
BASE TABLE이 하나인 경우이며, 뷰를 통한 DML(Update, Insert, Delete)이 가능하다.
View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT를 포함하는 경우 로우를 Delete할 수 없다
View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT, expression으로 정의된 Column을 포함하는 경우 로우를 Update 할 수 없다.
View가 Join 조건, Aggregate 함수, GROUP BY 구, DISTINCT, expression으로 정의된 Column, View로 선택되지 않은 NOT NULL칼럼을 포함하는 경우 로우를 Insert 할 수 없다.
1. EMP 테이블에서 20번 부서 사원의 사번, 이름, 급여를 정의하는 v_emp20 이라는 이름의 단순뷰를 작성 하시오.
SQL> create view v_emp20
2 as
3 select empno, ename, sal from emp
4 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
11.2 복합 뷰(Complex View)
BASE TABLE이 여러 개며 함수나 그룹함수 등을 SELECT LIST에 가진 경우.
INSERT, UPDATE, DELETE 허용 되지 않는다.
1. EMP 테이블과 DEPT 테이블을 조인하여 10번 부서 사원들의 사번, 이름, 급여, 부서명을 정의하는 v_emp10 이라는 이름의 복합뷰를 작성 하시오.
SQL> create view v_emp10
2 as
3 select empno, ename, sal, dname from emp, dept
4 where emp.deptno = 10
5 and emp.deptno = dept.deptno;
뷰가 생성되었습니다.
SQL> select * from v_emp30;
EMPNO ENAME SAL DNAME
------ ---------- ---------- --------------
7782 CLARK 2450 ACCOUNTING
7839 KING 5000 ACCOUNTING
7934 MILLER 1300 ACCOUNTING
2. EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰를 생성하시오.
(함수를 이용하여 뷰를 생성하는 경우 반드시 칼럼 별명을 사용해야 한다.)
SQL> create view v_emp_sum_avg
2 as
3 select deptno, avg(sal), sum(sal) from emp
4 group by deptno;
select deptno, avg(sal), sum(sal) from emp
*
3행에 오류:
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다
SQL> create view v_emp_sum_avg
2 as
3 select deptno, avg(sal) avg, sum(sal) sum from emp
4 group by deptno;
뷰가 생성되었습니다.
SQL> select * from v_emp_sum_avg;
DEPTNO AVG SUM
---------- ---------- ----------
20 1815 9075
10 2916.66667 8750
11.3 인라인 뷰(Inline View)
인라인 뷰는 스키마 오브젝트는 아니지만 WHERE절의 서브쿼리, FROM절에서 SELECT를 사용하여 테이블 처럼 사용될 데이터를 정의할 때 사용한다. 임시 뷰의 형태, 이름없이 해당 SQL 라인내에 기술한다 하여 인라인 뷰라고 함.
주로 조인 연산을 줄이기 위해, SELECT 되는 타겟 데이터의 양을 줄이기 위해, 분리된 쿼리를 하나의 쿼리에 모아서 사용하기 위해 사용한다
1. 인라인뷰를 사용하여 사원 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하시오.
SQL> select dname, avg_sal, sum_sal
2 from ( select deptno, avg(sal) avg_sal, sum(sal) sum_sal
3 from emp
4 group by deptno) e, dept d
5 where e.deptno = d.deptno;
DNAME AVG_SAL SUM_SAL
-------------- ---------- ----------
RESEARCH 1815 9075
ACCOUNTING 2916.66667 8750
11.4 WITH CHECK OPTION
뷰의 쿼리가 SELECT 할 수 있응 ROW에 대해 INSERT, UPDATE 가능 하다.
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의 조건에 위배 됩니다
11.5 DROP VIEW
뷰를 삭제하는 명령.(USER_VIEWS 딕셔너리뷰에서 뷰 정의만 삭제됨)
BASE TABLE은 삭제되지 않는다.
[형식]
DROP VIEW view_name
댓글 없음:
댓글 쓰기