데이터베이스 뷰 View

뷰란?(주로 조회 할 때 이용한다)
- 뷰는 하나의 가상 테이블이라 생각 하면 된다.
- 뷰는 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터를 관리 할수 있다.
- 뷰는 복잡한 Query를 통해 얻을 수 있는 결과를 간단한 Query로 얻을 수 있게 한다.
- 한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.
- 특정 평가 기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 한다.

 

뷰(view)
1. 가상의 테이블 : 뷰를 통해서 테이블과 같이 제어할 수 있다.
2. 뷰에 저장되는 정보는 SQL문이 저장(뷰에 저장되어 있는 실행문을 호출하면 실행이 된다.)
3. 뷰를 사용하는 목적
   1) 편리성 : 복잡한 명령을 간결하게 처리 및 이용이 가능하다.
   2) 보안성 : 뷰를 통해서 보안성이 없는 데이터만 노출시킬 수 있다.
   (주민번호의 앞자리는 공개가 가능하지만, 뒷자리는 공개시키면 안된다.)


drop view sawon_man -- 지우는 delete 명령

-- 뷰생성(수정) replace는 수정과 덮어쓰기 개념으로 보면 된다.
                       (뷰이름)
create or replace view sawon_man_view
as 
  select * from sawon 
  where sasex = '남자'

create view sawon_women_view
as
  select * from sawon
  where sasex = '여자'

-- 뷰의 사용
select * from sawon_man_view

select * from sawon_women_view

create or replace view gogek_view
	as
select
	gobun, goname, goaddr, substr(gojumin,1,8) || '******' as gojumin
from gogek

select * from gogek_view

 

 

- 계정생성하기(create user test9 identified by test9;)

-- 테스트
1. cmd창 -> sqlplus system/oracle
2. 계정생성
   SQL> create user test9 identified by test9;
3. 권한부여(connect)
   SQL> grant connect to test9;
4. DBO가 권한부여
   SQL> conn test/test
   SQL> grant select on gogek_view to test9
5. test9로 접속/조회
   SQL> conn test
   SQL> select * from test.gogek_view

 

- 권한 부여

 

객체에 대한 사용권한은 DBO 소유주가 주어야한다. test의 table을 사용할 수 있는 객체사용권한은 소유주인 test만이 부여할 수 있다. (이 말은 system이 test table의 권한을 못준다는 뜻이다)

 

- Oracle_test에 들어가서 test9에 조회할 수 있는 select 권한을 부여했다.

-- gogek_view를 조회할 수 있는 권한 부여
grant select on gogek_view to test9

 

 

 

- system 계정에서 test의 table을 사용할 수 있도록 강제적으로 권한을 넘겨주는법(원칙아님)

 

        내국인      외국인
        남  여      남  여
1900    1   2       5    6
2000    3   4       7    8
1800    9   0

           12345678901234 <- position
gojumin = '760815-1325467'

1. 앞자리 2자리를 떼어온다 -> 숫자로 변환
2. 성별자리 1자리 떼어온다 -> 숫자로 변환
3. 1 2 5 6 -> 1900년생 1900 더하기
   3 4 7 8 -> 2000년생 2000 더하기
   0 9     -> 1800년생 1800 더하기

-- 고객테이블에서 출생년도를 계산
create or replace view gogek_view2
as
    select gobun, goname, goaddr, godam,
        concat(substr(gojumin,1,8),'******') as gojumin,
        (
        to_number(substr(gojumin,1,2)) + 
        case
           when to_number(substr(gojumin,8,1)) in (1,2,5,6) then 1900
           when to_number(substr(gojumin,8,1)) in (3,4,7,8) then 2000
           else 1800
        end
        ) as birth_year,
        to_number(substr(gojumin,9,2)) as local
    from gogek

select * from gogek_view2

 

 

 

 

- case문을 활용해서 출생년도를 통해서 10간과 12지 구하기

- 그리고는 create view로 view 생성해서 간단하게 가져오기

-- 10간
4  5  6  7  8  9  0  1  2  3      <= mod(출생년도,10) : 0~9
갑 을 병 정 무 기 경 신 임 계

-- 12지
4  5  6  7  8  9  10 11 0  1  2  3 <= mod(출생년도,12) : 0~11
자 축 인 묘 진 사 오 미 신 유 술 해
-- gogek_view2 이용해서 나이, 띠 추가 -> gogek_view3 뷰 생성

create or replace view gogek_view3
as

select
    g.*,
    (to_number(to_char(sysdate,'YYYY')) - birth_year) as age,
    case
       mod(birth_year,12)
       when 0 then '원숭이'
       when 1 then '닭'
       when 2 then '개'
       when 3 then '돼지'
       when 4 then '쥐'
       when 5 then '소'
       when 6 then '호랑이'
       when 7 then '토끼'
       when 8 then '용'
       when 9 then'뱀'
       when 10 then '말'
       else '양'
    end as 띠,
    (
    case mod(birth_year,10)
       when 0 then '경'
       when 1 then '신'
       when 2 then '임'
       when 3 then '계'
       when 4 then '갑'
       when 5 then '을'
       when 6 then '병'
       when 7 then '정'
       when 8 then '무'
       when 9 then '기'
    end
    ||
    case mod(birth_year,12)
       when 0 then '신'
       when 1 then '유'
       when 2 then '술'
       when 3 then '해'
       when 4 then '자'
       when 5 then '축'
       when 6 then '인'
       when 7 then '묘'
       when 8 then '진'
       when 9 then '사'
       when 10 then '오'
       when 11 then '미'
    end
    ||
    '년'
    ) as 간지,
    -- 위에서 간지구하는것을 substr으로 굉장히 간단하게 만들기
    (
    substr('경신임계갑을병정무기', mod(birth_year,10)+1,1) ||
     substr('신유술해자축인묘진사오미', mod(birth_year,12)+1,1) || '년'
    ) as 간지2,
    case
       when local between 0 and 8 then '서울'
       when local between 9 and 12 then '부산'
       else '자랑스러운 대한민국 출신'
    end as sido
from (select * from gogek_view2) g
-- g문을 넣는 이유는 from문 뒤에있는 view를 불러오기 위해서이다. g가 가장먼저 실행이된다.

select * from gogek_view3

 

 

 

 

Inline view

-- inline view : 일시적인 뷰(SQL내에 포함된 SQL)
-- 급여 순위 : 1 ~ 5등
select 
   rank() over(order by sapay desc) as rank 
from sawon -- 이렇게 사용하면 누가 누구의 rank인지 나오지를 않는다.

select 
   s.*,
   rank() over(order by sapay desc) as rank 
from (select * from sawon) s
where rank between 1 and 5


select * from
(
   select
   s.*,
   rank() over(order by sapay desc) as rank 
from (select * from sawon) s
)
where rank between 1 and 5

 

- 인라인뷰 활용예시

select
   sabun, saname, sahire, sapay,
   floor(months_between(sysdate,sahire)/12) as 근무년수,
   TRUNC(mod(months_between(sysdate,sahire),12),0) as 근무월수,
   round(sapay/12,0) as 월급,
   round(sapay/12 * floor(months_between(sysdate,sahire)/12) + sapay/12 * mod(months_between(sysdate,sahire),12)/12,0) as 퇴직금1,
   round(months_between(sysdate,sahire)*(sapay/12)/12,0) as 퇴직금2
from sawon


select 
  s.*,
  월급*근무년수 + 월급*(근무월수/12) as 퇴직금1
from
(
    select
       sabun, saname, sahire, sapay,
       floor(months_between(sysdate,sahire)/12) as 근무년수,
       TRUNC(mod(months_between(sysdate,sahire),12),0) as 근무월수,
       round(sapay/12,0) as 월급
    from sawon
) s