오라클 VIEW

■ VIEW(가상의테이블)
-- 뷰란 이미 특정한 데이터베이스 내에 존재하는 하나 이상의 테이블에서 사용자가 얻기 원하는 데이터들만을 정확하고 편하게 가져오기 위하여 사전에 원하는 컬럼들 만을 모아서 만들어 놓은 가상의 테이블로 편리성 및 보안에 목적이 있다.

  CREATE [OR REPLACE]  VIEW 뷰이름
       AS subquery

or replace를 붙이면 view가 있을 경우 수정하고 없으면 새로 만듬

 

 

-- sys(뷰를만들수있는권한부여)
GRANT CREATE VIEW TO 사용자

-- 권한확인
select * from user_sys_privs;

create or replace view vbook
as
    select b.b_id,title,c_name,price,g.g_id,g_name,p_su,p_date
      from book b
      join danga d on b.b_id=d.b_id
      join panmai p on b.b_id=p.b_id
      join gogaek g on p.g_id=g.g_id

-- 모든 테이블 확인
select * from tab

-- 뷰의이름 뷰의내용
select view_name, text from user_views

-- 뷰의컬럼
select * from col where tname='vbook'

select * from vbook

 

 

 

<문제> 

 insa 테이블을 이용한 뷰만들기
- num, name, buseo,jikwi,birth, age, gender, basicpay,sudang,tpay,tax
- birth,age,gender는 ssn 컬럼이용
-tax는 basicpay+sudang가 300만원 이상이면 3%(basicpay+sudang)
       basicpay+sudang가 300만원 이상이면 2%
       나머지 0%
-tpay : basicpay+sudang

 

create or replace view vinsa
as
      select num, name, buseo,jikwi,substr(1,6) birth, trunc(months_between(sysdate,to_date(substr(ssn,1,6),'rrmmdd'))/12) age,
      decode(mod(substr(ssn,8,1),2),1,'여',0,'남') gender, basicpay,sudang,(basicpay+sudang) tpay,
          case
              when basicpay+sudang>=3000000 then 0.03
              when basicpay+sudang>=2000000 then 0.02
              else 0
          end * (basicpay+sudang) tax
      from insa;
select * from tab
select * from vinsa;

 

 

 

※ 뷰를통한 insert는 단순뷰만 가능하다

 

 

 

 

-- 사용자 권한 확인
-- 사용자 계정
SQL>SELECT * FROM user_sys_privs;

----------------------------------------------------
-- 사용자 권한 부여(RESOURCE롤의 권한 만으로는 뷰를 생성할 수 없다.)
-- SYS 계정
-- 서버에 있는 모든 시스템 권한 출력
CMD>sqlplus sys/암호 as sysdba
SQL>SELECT * FROM system_privilege_map;

--  사용자에게 뷰를 만들 수 있는 권한 부여
SQL>GRANT CREATE VIEW TO 사용자;

-- 사용자 계정
-- 권한 확인
SQL>CONN 사용자/암호
SQL>SELECT * FROM user_sys_privs;


//*****************************************
-- 뷰 정보 확인
SELECT view_name, text FROM user_views;
SELECT * FROM tab;   -- tabtype:view로 표시
SELECT * FROM col;
DESC viewPAN;

 

-- 뷰 삭제
DROP VIEW yearPan;
SELECT view_name, text FROM user_views;



-- 머트리얼라이즈 뷰(MATERIALIZED VIEW)<express 버전은 지원 않함>
/*
실제 데이터(통계정보)를 자신이 가지고 있으며, master 테이블이라고 불리는 기준이 되는 테이블에 INSERT, UPDATE, DELETE가 발생하면 새로운 데이터를 MATERIALIZED 뷰에 반영

- SUM, MIN, MAX, AVG, COUNT 등 그룹함수를 미리 계산해 놓을 때 사용
- USER_SEGMENTS 에서 확인 가능

-- 옵션
- BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성
- BUILD DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성
- REFRESH :  MView의 데이터를 새로 고치는 시기와 방법을 결정
    1) 시기
      1) ON COMMIT - 기초 테이블에 COMMIT 이 일어날 때 REFRESH 가 일어나는 방안이며,
         이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나,
         MView에 조인만이 있는 경우,  GROUP BY 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만  사용이 가능
       2) ON DEMAND - 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS,  REFRESH_DEPENDENT) 를 실행 한 경우 Refresh 되는 경우
    2) 방법
     1) COMPLETE - MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는 것으로
            ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우
     2) FAST - 새로운 데이터가 삽입될 때마다 점진적으로 REFRESH 되는 방안
     3) FORCE - 이 경우 먼저 FAST REFRESH가 가능한지 점검 후 가능하면 이를 적용하고,
            아니면 COMPLETE REFRESH를 적용(디폴트)
    4) NEVER - REFRESH를 쓰지 않는다.
- ENABLE QUERY REWRITE : MView 생성시 이 옵션을 주어야만 임의의 SQL문장을 처리시
       QUERY REWRITE를 고려한다. 이는 쿼리 재작성의 기능이다. 이전의 쿼리를 수정 하지 않고 재작성이 가능 한 기능
*/

----------------------------------------------------
-- 사용자에게 권한 설정
   -- QUERY REWRITE : 쿼리 재작성 권한
-- SYS 사용자(sqlplus sys/암호 as sysdba)
GRANT QUERY REWRITE TO 사용자;
GRANT CREATE MATERIALIZED VIEW TO 사용자;