뷰(View)

• 뷰(View) : 실제 물리적으로 존재하지 않는 가상의 논리적인 테이블
  • 자주 사용하는 복잡한 sql 을 view로 만들어둠으로써 편리하게 사용할 수 있다.
  • 보안 유지 상 사용자별로 조회할 수 있는 칼럼을 제한할 수 있다.
  • 사용자 별로 접근 권한을 두고자 할 때 View 를 이용한다.
  • select 한 것으로 이름을 정해줄 때 사용한다.
  • count, sum 등에 맞는 애칭이 필요하다.
  • 이 view 에서도 조건식에 맞는 view를 조회할 수 있다.
  • view를 통해서 추가, 수정, 삭제가 가능하다. 실제로는 뷰를 만들 때 사용한 emp 테이블에 레코드가 추가되고 뷰의 속성 이외의 속성들이 null을 허용하거나, delete 값이 있어야지만 추가할 수 있다.

create view 뷰이름 as select~

-- 가장 판매량이 높은 책 3권의 정보
select * from book
where bookid in( 
select bookid
from (
    select bookid, count(*)
    from orders
    where 
    to_char(sysdate, 'yyyy/mm') =
    to_char(orderdate, 'yyyy/mm')
    group by bookid
    order by count(*) desc
) where rownum <= 3);

-- 이렇게 만들어두면 top3를 조회할 때 자유롭게 사용 가능하다.
create view top3
as
select * from book
where bookid in( 
select bookid
from (
    select bookid, count(*)
    from orders
    where 
    to_char(sysdate, 'yyyy/mm') =
    to_char(orderdate, 'yyyy/mm')
    group by bookid
    order by count(*) desc
) where rownum <= 3);

--위에서 붙인 뷰 이름 사용
select * from top3;

-- 실습을 위해 날짜 바꾸기
update orders set orderdate = sysdate 
where orderid >= 21;

-- 오늘 날짜의 출판사별 총 판매수량, 총 판매 금액을 조회하는 뷰를 만들고 실행한다.
-- 가상의 테이블은 column 애칭 필요
-- 없으면 만들고 있으면 replace 해달라는 요청
create or replace view today_sale
as
select publisher, count(saleprice) ordernum, sum(saleprice) sum
from orders o, book b
where o.bookid = b.bookid and
to_char(sysdate, 'yyyy-mm-dd') = to_char(orderdate, 'yyyy-mm-dd')
group by publisher;

--확인
select * from today_sale

-- 뷰 테이블에 조건식 넣기
select * from today_sale
where ordernum >= 2;

-- 도서명에 '축구'를 포함하고 있는 도서의 정보를 조회하는 뷰를 생성해본다.
create or replace view vw_book
as
select * from book
where bookname like '%축구%';

-- 대한민국에 거주하는 고객의 정보를 출력하는 뷰를 생성
create or replace view vw_customer
as
select * from customer
where address like '%대한민국%';

select * from vw_customer;

• 권한에 따른 뷰 보기 예시

/*sqlplus 에서*/
--실습을 위해 새 계정 만들기
conn system/manager;

 create user c##kim identified by kim;
 
--권한 부여
grant connect, resource to c##kim;

-- 새 계정에 연결
conn c##kim/kim

-- 권한이 부여된 테이블 보기( 그전에 아래꺼 해주기)
select * from c##madang.vw_emp;

-- 아래 테이블은 볼 수 없다.
select * from c##madang.emp;

/* c##madang 에서 */
-- c## madang이 갖고 있는 테이블 emp 중에 c##kim 에게 
-- 사원번호, 사원명, 부서번호, 이메일만 조회할 수 있는
-- 권한을 부여

create or replace view vw_emp
as
select eno, ename, dno, email
from emp;

select * from vw_emp;

-- kim 에게 액세스 권한 부여
grant select on c##madang.vw_emp to c##kim;

-- kim 에게 권한 제한
revoke select on c##madang.vw_emp from c##kim;

• 뷰에 뷰 자체 조건에 맞지 않은 데이터 추가/수정/삭제 해보기

--20번 부서에 근무하는 직원들의 사원번호, 이름, 직책
--입사일 부서번호를 조회하는 뷰를 생성 vw_emp20
create or replace view vw_emp20 as
select eno, ename, job, HIREDATE, dno from emp
where dno = 20;

-- view의 조건에 맞지 않아도 레코드가 들어갈까?
select * from emp;


-- 모 테이블에 추가되고 뷰에는 조회되지 않는다.
insert into vw_emp20 values(3001, '테스트', '사원', sysdate, 30);
select * from vw_emp20;
select * from emp;

-- 뷰 생성 조건과 맞지 않는 값으로 수정가능하다.
insert into vw_emp20 values(3002, '테스트', '사원', sysdate, 20);
update vw_emp20 set dno = 30
where eno = 3002;
-- 수정 가능 확인
select * from emp where eno = 3002;

-- 삭제 되는것 확인
update emp set dno = 20 where eno = 3002;
select * from vw_emp20;
delete vw_emp20 where eno = 3002;
select * from emp where eno = 3002; --삭제됨

drop view vw_emp20;

• 뷰를 생성할 때 설정한 조건에 맞지 ㅇ낳는 레코드를 추가할 수 없도록 또한, 조건에 맞지 않는 값으로 수정한 수 없도록 뷰를 만들고 싶을 때 다음과 같은 키워드를 넣는다
  • with check option

-- 뷰 생성
create view vw_emp20
as
select eno, ename, job, hiredate, dno
from emp
where dno =20
with check option;

-- 추가
insert into vw_emp20 values(4000, '김유신', '사원', sysdate, 20);

insert into vw_emp20 values(4001, '유관순', '사원', sysdate, 30);
-- ORA-01402 : view WITH CHECK OPTION where-clause violation

update vw_emp20 set job = '과장' where eno= 4000;

update vw_emp20 set dno = 30 where eno= 4000;
-- ORA-01402 : view WITH CHECK OPTION where-clause violation

• 조회(읽기)만 가능한 뷰 생성
• with read only

-- 뷰 생성
create view vw_emp20
as
select eno, ename, job, hiredate, dno
from emp
where dno =20
with read only;

-- 추가/ 수정/ 삭제가 안되는 것을 볼 수 있다.
insert into vw_emp20 values(4000, '김유신', '사원', sysdate, 20);
-- ORA-42399: cannot perform a DML operation on a read-only view

update vw_emp20 set job = '과장' where eno= 4000;
-- ORA-42399: cannot perform a DML operation on a read-only view

delete vw_emp20;
-- ORA-42399: cannot perform a DML operation on a read-only view

drop view vw_emp20;

 

시스템 뷰

• 오라클이 제공하는 미리 만들어놓은 뷰. 데이터 사전이라고 도 한다.
• user_objects : 사용자가 만든 객체의 정보를 갖고 있는 데이터 사전. 테이블명, 뷰, 제약들 등이 있다.

-- USER_OBJECTS 속성 구성 확인
desc USER_OBJECTS;
--제약, 테이블, 뷰 등을 조회
select object_name from user_objects;

• user_tables : 사용자가 만든 테이블의 정보를 갖고 있는 데이터 사전

-- User_tables 구성 확인
desc user_tables;
-- 내가 만든 테이블들 확인
select table_name from user_tables;

• user_contraints : 사용자가 만든 모든 제약의 정보를 갖고 있는 데이터 사전(전에 했으므로 생략)