오라클 View, 단순뷰, 복합뷰, inline view, view 조회 및 삭제, Materialized View(MVIEW)

-- 1. 단순뷰

-- View를 생성하기 위해서는 create view 권한이 필요

-- sysdba로 접속해서

-- grant create view to scott; 를 해줘야한다.

-- view 생성

create or replace view v_emp1

as

select empno, ename, hiredate

from emp;

-- view에 index 생성 불가

create index idx_v_emp_ename

on v_emp1(ename);

-- 테이블 생성

create table o_table (a number, b number);

-- view view1 생성

create view view1

as

select a, b

from o_table;

-- insert

insert into view1 values (1, 2);

-- 값 나옴

select * from view1;

-- 값 나옴

select * from o_table;

rollback; -- 앞에 insert 했던 내용 rollback 되어 삭제됨

-- 값 없음

select * from view1;

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

-- with read only 옵션값으로 view 생성

create view view2

as 

select a, b

from o_table 

with read only;

select * from view2;

-- readonly로 만든 view라서 insert 불가

insert into view2 values (3, 4);

-- insert 가능

insert into view1 values (3, 4);

-- 3,4 insert 된 값 출력

select * from view2;

-- 3,4 insert 된 값 출력

select * from o_table;

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

insert into view1 values (5, 6);

select * from view1;

-- 

create view view3

as

select a, b

from o_table

where a=3

with check option;  -- a=3인 값을 다른 값으로 변경할 수 없다. a값 변경 불가. b값 변경가능. 

select * from view3;

-- a값 변경불가

update view3

set a=5

where b=4;

-- b값 변경가능

update view3

set b=6

where a=3;

select *  from view3;

-- 삭제가능

delete from view3

where a=3;

select *  from view3;

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

-- 2. 복합뷰

-- join을 사용한 view 생성

create or replace view v_emp

as

select e.ename, d.dname

from emp e, dept d

where e.deptno = d.deptno;

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

-- 3. inline view(인라인 뷰)

select e.deptno, d.dname, e.sal

from ( select deptno, max(sal) sal

       from emp 

       group by deptno) e, dept d

where e.deptno = d.deptno;

select deptno, max(sal)

from emp

group by deptno;

select * from professor;

COL deptno FOR a7

select decode(deptno, ndeptno, null, deptno) deptno, profno, name

from (select LAG(deptno) OVER (order by deptno) ndeptno, deptno, profno, name

    from professor);

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

-- 4. view 조회 및 삭제하기

select view_name, text, read_only from user_views;

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

5. Materialized View(MVIEW) - 구체화된 뷰

-- Mview를 생성하기 위해서는 query rewrite와 create materialized view 권한이 있어야한다.

-- sysdba로 접속하여 아래 명령문으로 권한주기

-- grant query rewrite to scott; 

-- grant create materialized view to scott;

create materialized view m_prof

build immediate --  Mview 생성시 즉시 서브쿼리부분을 수행해서 데이터 가져오기

refresh

on demand -- 사용자가 수동으로 동기화 명령을 수행해서 동기화

complete -- Mview 내의 데이터 전체가 원본 테이블과 동기화 되는 방법

-- enable query rewrite -- 주석

as

select profno, name, pay

from professor;

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

-- Mview에는 데이터가 존재하므로 index  생성 가능

create index idx_m_prof_pay

on demand;

-- 데이터 삭제

delete from professor 

where profno=4007;

commit;

-- 동기화가 되지않아 count 갯수가 다르게 출력됨

select count(*) from professor;

select count(*) from m_prof;

-- 동기화

begin

    dbms_mview.refresh('m_prof');

end;

/

-- 다른 동기화 명령

variable num number;

exec dbms_mview.refresh_dependent(:num, 'professor','c'); 

-- professor테이블을 사용하는 모든 mview를 찾아서 한꺼번에 동기화

-- c는 refresh 수준으로 complete를 의미

-- 해당 사용자가 만든 모든 mview를 동기화

exec dbms_mview.refresh_all_mviews;

-- mview 조회, 삭제

select mview_name, query

from user_mviews

where mview_name = 'm_prof';

-- mview 삭제

drop materialized view m_prof;