2020041302 / 뷰(View)

-- * 오라클 뷰(View)

-- [1] 뷰의 데스트용 테이블 생성

-- (1) DEPT 테이블을 복사한 DEPT_COPY 테이블을 생성

create table dept_copy

as

select * from dep; -- dept 모든 테이터를 dept_copy 테이블에 복사

-- (2) EMP 테이블을 복한 EMP_COPY 테이블을 생성

create table emp_copy

as

select * from emp;

-- [2] 뷰(View) 정의하기

-- 뷰를 생성하기 위해서는 create view로 시작한다

-- as 다음은 마치 서브쿼리문과 유사하다

-- 서브쿼리는 우리가 지금까지 사용하였던 select 문을 기술하면 된다

-- ex) 만일 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 자주 검색한다고 하면

select empno, ename, deptno

from emp_copy

where deptno = 30;

-- 위와 같이 결과를 출력하기 위해서 매번 select문을 입력하기란 여간 번거로운 일이 아닐 수 없음

-- 뷰는 이와같이 번거로운 select문을 매번 입력하는 대신 보다 쉽게 원하는 결과를 얻고자

-- 출발한 개념

create view emp_view30 -- 가상 emp_view30을 만든것이다.

as 

select empno, ename, deptno

from emp_copy

where deptno = 30;

-- 위 실행 시 error

create view emp_view30

as 

select empno, ename, deptno

from emp_copy

where deptno = 30;

오류 보고 -

ORA-01031: insufficient privileges -- 권한이 불충분합니다

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

-- 위 해결방법

-- dos command 차에서 sqlplus로 연결

-- dos cmd

C:\Users\tjoeun>sqlplus system/admin1234

SQL*Plus: Release 11.2.0.2.0 Production on 월 4월 13 11:20:53 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> show user --시스템인지 확인

USER is "SYSTEM"

SQL>

-- grant 명령어 권한부여 명령어

SQL> grant create view to scott;

Grant succeeded. --권한부여 완료

SQL>

-- 재 실행 시 정상동작

create view emp_view30

as 

select empno, ename, deptno

from emp_copy

where deptno = 30;

View EMP_VIEW30이(가) 생성되었습니다. --실행결과

select * from emp_view30;

--desc 각 컬럼에 대한 이름과 자료형 형태를 보여주는 명령어

desc emp_view30;

이름     널? 유형           

------ -- ------------ 

EMPNO     NUMBER(4)    

ENAME     VARCHAR2(10) 

DEPTNO    NUMBER(2)

desc emp;

이름       널?       유형           

-------- -------- ------------ 

EMPNO    NOT NULL NUMBER(4)    

ENAME             VARCHAR2(10) 

JOB               VARCHAR2(9)  

MGR               NUMBER(4)    

HIREDATE          DATE         

SAL               NUMBER(7,2)  

COMM              NUMBER(7,2)  

DEPTNO            NUMBER(2)

-- 위에서 보여지듯 view로 생성 시 프라이머리와 같은 데이터 무결성 제약 조건(DICR)까지 복사되어 지지는 않는다

-- [문제] 기본 테이블은 emp_copy로 합니다. 20번 부서에 소속된 사원들이ㅡ

-- 사번과 이름, 부서번호, 상관의 사번을 출력하기 위한 select 문을

-- emp_view20 이라는 이름의 뷰로 정의해 보자

-- [3] 뷰의 내부 구조와 user_views 데이터 딕셔너리

desc user_views;

이름               널?       유형             

---------------- -------- -------------- 

VIEW_NAME        NOT NULL VARCHAR2(30)   

TEXT_LENGTH               NUMBER         

TEXT                      LONG           

TYPE_TEXT_LENGTH          NUMBER         

TYPE_TEXT                 VARCHAR2(4000) 

OID_TEXT_LENGTH           NUMBER         

OID_TEXT                  VARCHAR2(4000) 

VIEW_TYPE_OWNER           VARCHAR2(30)   

VIEW_TYPE                 VARCHAR2(30)   

SUPERVIEW_NAME            VARCHAR2(30)   

EDITIONING_VIEW           VARCHAR2(1)    

READ_ONLY                 VARCHAR2(1)

select view_name, text from user_views;

VIEW_NAME        TEXT

EMP_VIEW30 "select empno, ename, deptno from emp_copy where deptno = 30"

-- [4] 뷰의 동작 원리

-- 1) 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서

-- 뷰에 대한 정의를 조회한다

-- 2) 기본 테이블에 대한 접근 권한을 살핀다

-- 3) 뷰에 대한 질의를 기본테이블에 대한 질의로 변환

-- 4) 기본 테이블에 대한 질의를 통해 데이터를 검색

-- 5) 검색된 결과를 출력한다

-- [5] 뷰와 기본 테이블 관계 파악

-- 1) 뷰를 통한 데이터 저장이 가능한가?

insert into emp_view30

values (8000, 'ANGEL', 30);

1 행 이(가) 삽입되었습니다.

select * from emp_view30; -- 데이터가 삽이 가능하다

select * from emp_copy; -- 

-- [6] 뷰를 사용하는 이유? --pdf 참조

-- ex) 인사과 뷰

-- view 생성방법

create view emp_vies

as

select empno, ename, job, hiredate, deptno

from emp_copy;

-- [7] 뷰의 특징

-- 1) 단순 뷰에 대한 데이터 조작

insert into emp_view30

values(8010, 'SHEOLSOO' , 30 );

select * from emp_view30;

select * from emp_copy;

-- 2) 단순 뷰의 컬럼에 별칭 부여하기

create view emp_view_copy(사원번호, 사원명, 급여, 부서번호) -- 따옴표를 생략하고 

as

select empno, ename, sal, deptno

from emp_copy;

select * from emp_view_copy; -- 위에서 입력한 테이블명이 별칭으로 출력되는 것을 확인 가능하다

--where deptno = 30; --error

whrer 부서번호 = 30

select * from emp_view_copy; -- 위에서 입력한 테이블명이 별칭으로 출력되는 것을 확인 가능하다

--where deptno = 30; --error, 별칭을 사용한 이름으로 출력이 가능함

select * from emp_view_copy; -- 위에서 입력한 테이블명이 별칭으로 출력되는 것을 확인 가능하다

whrer 부서번호 = 30

-- 3) 그룹 함수를 사용한 단순뷰

create view view_sal

as

select deptno, sum(sal), avg(sal) -- 원본 데이터 컬럼이름이 없어서 view를 생성하지 못한다

from emp_copy

group by deptno; -- error

error 문구

create view view_sal

as

select deptno, sum(sal) as "급여합", avg(sal) as "급여평균" -- 원본 데이터 컬럼이름이 없어서 view를 생성하지 못한다

from emp_copy

group by deptno;

select * from view_sal;

-- 주의

create view view_sal_year

as

select ename, sal * 12 "연봉"

from emp_copy;

select * from view_sal_year;

-- 4) 복합 뷰

select empno, ename, sal, e.deptno, dname, loc

from emp e, dept d

where e.deptno = d.deptno

order by empno desc; -- 사원번호를 기준으로 내림차순으로 출력하라

-- 뷰 테이블로 작성

create view emp_view_dept

as

select empno, ename, sal, e.deptno, dname, loc

from emp e, dept d

where e.deptno = d.deptno

order by empno desc;

select * from emp_view_dept;

-- [8] 뷰 삭제

drop view emp_view_dept;

-- 실습에서 만들어진 뷰 확인

select view_name, text from user_views;

-- [9] 뷰 생성에 사용되는 다양한 옵션

-- or replace

-- : 조재하지 않는 뷰이면 새로운 뷰를 생성하고 기존에 존재하는 뷰이면 그 내용을 변경한다

create or replace view emp_view30

as

select empno, ename, comm, deptno

from emp_copy

where deptno = 30;

select * from emp_view30;

-- [10] 뷰 생성에 사용되는 다양한 옵션

-- force

-- : 기본테이블이 존재하지 않을 때도 뷰를 생성해야 하는 경우 사용한다

-- noforce

-- : 기본테이블이 존재하는 경우만 뷰가 생성(default) 

desc employees;

select * from employees;

create or replace view employees_view

as

select empno, ename, deptno

from employees

where deptno = 30;

create or replace force view employees_view

as

select empno, ename, deptno

from employees

where deptno = 30;

컴파일 오류는 발생과 함께 뷰가 생성되었습니다.

select view_name, text

from user_views;

insert into employees_view

values(8020, '이순신', 30); -- error 실제로 존재하는 테이블이 아니기 때문이다

-- [11] 뷰 생성에 사용되는 다양한 옵션

-- whit check option

-- : 뷰를 생성할 때 조건 제시에 사용된 컬럼 값을 변경 못하도록 하는기능

-- : 뷰를 설정할 때 조건으로 설정한 컬럼 이외이 다른 컬럼의 내용은 변경할 수 있다

create or replace view emp_view30

as

select empno, ename, sal, comm, deptno -- sal 만 추가해서 실습진행

from emp_copy

where deptno = 30;

selec