<정의 >
1. 가상테이블, 저장된 쿼리
2. 실제 테이블이 아니라 쿼리문을 저장하고 있어 테이블과 같이 쿼리문을 통해 조회
<사용 이유>
1. 원하는 컬럼, 레코드를 사전에 만들어 쿼리로 저장
2. 복잡한 Join문 등을 단일 테이블을 사용하듯이 간단한 쿼리로 사용가능하다
3. 실질적 테이블의 내부 구조를 숨길 수 있다. (보안에 유리)
4. 일반적으로 view는 read only
(편리성 - 서브쿼리를 사용한 결과와 동일)
-뷰
select *
from vw_emp20;
-서브쿼리
SELECT *
FROM (SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20);
< 사용 방법>
- 원본 테이블 만들기 (복사되는 테이블은 제약조건이 안 넘어온다)
create table dept_cop
as
select * from dept;
create table emp_copy
as
select * from emp;
1. 뷰 생성 ( 뷰를 만들기 위해서는 마스터 아이디에게 권한을 부여받아야 처리가능)
- 형식
create or replace view 뷰테이블명([alias])
as
서브쿼리(select)
[with check option]
[with read only]
- or replace : 같은 이름의 뷰가 이미 존재할 경우 현재 생성할 뷰로 대체하여 생성
- with check option : 지정한 제약조건을 만족하는 데이터에 한해 DML 작업이 가능하도록 뷰 생성
- with read only : 뷰의 열람(select)만 가능하도록 뷰 생성
-예시 (뷰에 셀렉된 컬럼만 조회가 가능하다)
create or replace view emp_view30
as
select empno,ename,sal,deptno from emp_copy
where deptno = 30;
1-1 권한 부여 방법 (권한 부여 후 삭제되기 이전까지 권한 가지고 있음)
cmd
sqlplus system/oracle
grant create view to scott;
2. 생성한 뷰 확인
select *
from user_views;
3. 뷰 삭제
drop view vw_emp20;
4. 데이터 삽입
select *
from emp_view30;
insert into emp_view30
values(1111,'hong',1000,30);
insert into emp_view30(empno,ename,sal)
values(2222,'hong',1000); --deptno가 null로 추출된다.
insert into emp_view30(empno,ename,sal,deptno)
values(2222,'hong',1000,50); --deptno가 null이므로 추출이 안된다.
select *
from emp_copy;
***기본 데이터에만 추가가 된다???????
< 복합뷰 >
create or replace view emp_view(사원번호,사원명,급여,부서번호)
as
select empno,ename,sal,deptno
from emp_copy;
select * from emp_view;
select *
from emp_view
where 부서번호 = 20;
--where deptno = 20; --error : 별칭을 한번 주고나면 그 뒤로는 원래 컬럼명으로 사용 불가하다.
create or replace view emp_dept_view
as
select empno,ename,sal,e.deptno,dname,loc
from emp e inner join dept d
on e.deptno = d.deptno
order by empno desc;
select *
from emp_dept_view;
- 부서별 최소급여와 최대급여
create or replace view sal_view(dname,min_sal,max_sal)
as
select dname,min(sal) min_sal, max(sal) max_sal
from emp e inner join dept d
on e.deptno = d.deptno
group by d.dname;
select * from sal_view;
create or replace view sal_view
as
select dname,min(sal) min_sal ,max(sal) max_sal,round(avg(sal),0) as avg_sal
from emp e inner join dept d
on e.deptno = d.deptno
group by d.dname;
<with check option>
조건절의 컬럼을 수정하지 못하게 한다
create or replace view view_chk30
as
select empno, ename, sal,comm, deptno
from emp_copy
where deptno = 30 with check option;
select * from view_chk30;
- update 오류 발생
<with read only>
모든 컬럼에 대한 C U D 가 불가능(조회만 가능)
create or replace view view_read30
as
select empno,ename,sal,comm,deptno
from emp_copy
where deptno = 30 with read only;
- update 오류 발생
<인라인뷰>
- 서브쿼리가 FROM 절에서 안에서 사용되는 경우, 해당 서브쿼리를 '인라인뷰'라고 한다.
- WITH 절에서 미리 이름을 정의해 두고 사용하는 select 문
-인라인 뷰 활용 예시
select rm, b.*
from (
select rownum rm, a.*
from (select empno,ename,hiredate
from emp
order by hiredate asc
)a
)b
where rm >= 2 and rm <= 4;
-인라인 뷰로 입사일이 가장빠른 사원5명 추출하기
select rownum,empno,ename,hiredate
from(
select empno,ename,hiredate
from emp
order by hiredate asc
)a
where rownum <= 5 ;
<인라인 뷰로 TOP-N 추출>
Top N 쿼리 ?
상위 n개의 데이터를 추출하는 쿼리
-인라인 뷰 (서브쿼리 활용)
SELECT ROWNUM, E.*
FROM (SELECT *
FROM EMP E
ORDER BY SAL DESC)E
WHERE ROWNUM <= 3;
-인라인 뷰 (WITH절 활용)
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
FROM E
WHERE ROWNUM <= 3;
<ROWNUM 낮은순부터 조회>
ROWNUM?
- 데이터가 저장되는 실제 테이블에 존재하지는 않지만 특정 목적을 위해 테이블에 저장되어있는 열처럼 사용 가능
- 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호
- 입사일이 가장 빠른 5명의 사원조회
select * from emp
order by hiredate asc;
select * from emp
where hiredate <= '81/05/01' ;
DESC emp;
- 뷰의 활용 예시 1)
select rownum,empno,ename,hiredate
from emp
where rownum <= 5;
select rownum,empno,ename,hiredate
from emp
order by hiredate asc; --rownum이 순서대로 정렬되지 않는다.
- 뷰의 활용 예시 2)
create or replace view view_hiredate
as
select empno,ename,hiredate
from emp
order by hiredate asc;
select * from view_hiredate;
- 2)의 rownum 2~4만 출력하기
select rownum,empno,ename,hiredate
from view_hiredate --rownum도 순서정렬되고 입사일도 순서정렬된다.
where rownum between 2 and 5; --출력 실패 : rownum을 조건절에 직접 사용시 반드시 1을 포함하는 조건식을 만들어야 한다.
create or replace view view_hiredate_rm
as
select rownum rm ,empno,ename,hiredate --rownum rm 별칭을 만들어 다시 테이블 생성한다
from view_hiredate;
select rm,empno,ename,hiredate
from view_hiredate_rm;
select rm,empno,ename,hiredate
from view_hiredate_rm
where rm >= 2 and rm <= 4;
'oracle' 카테고리의 다른 글
1026_시퀀스 (0) | 2022.10.26 |
---|---|
1024_제약조건(foreign key, check, default) (0) | 2022.10.26 |
1019_날짜함수 (1) | 2022.10.26 |
1020_서브쿼리 (0) | 2022.10.26 |
Foreign Key(외래키) (0) | 2022.10.25 |