View(뷰), System View(시스템 뷰)

View
==> 실제로는 존재 하지 않는 논리적인 가상의 테이블을 말합니다.
==> 자주사용하는 복잡한 조건식을 갖는 select문이 있다면
그것을 조회하는 sql명령를 뷰로 만들어놓으면
사용이 용이합니다.
==> 보안유지상 특정 테이블의 칼럼을 제한하여 접근할 수 있도록 
할때에도 뷰를 사용합니다.

** 뷰를 만드는 방법

create view 뷰이름 as select 문

 

 

ex) 오늘날짜의 출판사별 총판매수량, 총판매금액을 출력
select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum
from book b, orders o
where b.bookid = o.bookid(+) and 
to_char(orderdate, 'yyyy/mm/dd') = to_char(sysdate,'yyyy/mm/dd')
group by publisher;
===> A

select distinct publisher, 0 cnt from book;
==> B

A의cnt + B의 cnt

select b.publisher, nvl(A.cnt,0) cnt, nvl(A.sum, 0) sum from 
(select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum
from book b, orders o
where b.bookid = o.bookid(+) and 
to_char(orderdate, 'yyyy/mm/dd') = to_char(sysdate,'yyyy/mm/dd')
group by publisher) A right outer join (select distinct publisher, 0 cnt from book) B
on A.publisher = B.publisher
order by nvl(A.cnt,0) desc;

==> 위와같이 조회할 일이 많다고 할 때에 
매번 sql를 작성하는 것은 번거로운 일입니다.

이런경우 뷰(View)를 만들어두면 조회를 쉽게 할 수 있어요.

create view today_sale 
as 
select b.publisher, nvl(A.cnt,0) cnt, nvl(A.sum, 0) sum from 
(select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum
from book b, orders o
where b.bookid = o.bookid(+) and 
to_char(orderdate, 'yyyy/mm/dd') = to_char(sysdate,'yyyy/mm/dd')
group by publisher) A right outer join (select distinct publisher, 0 cnt from book) B
on A.publisher = B.publisher
order by nvl(A.cnt,0) desc;

select * from today_sale;

ex) 도서명에 "축구"를 포함하고 있는 도서의 정보를 조회하는 
뷰를 생성해 봅니다.
뷰이름은 vw_book이라고 합니다.

create view vw_book
as 
select * from book
where bookname like '%축구%';

** view를 사용하는 목적
- 자주사용하는 복잡한 sql를 대신 보안유지상 사용자별로 조회할 수 있는 컬럼을 제한



** 뷰를통하여 insert, update, delete를 실험해 봅시다.

insert into vw_emp20 values(2000, '홍길동', '서울', '010-7777-7777', 20);
==> 뷰를 통하여 insert할 수 있어요.
실제로 레코드는 뷰를 생성할 때 사용한 모테이블에 추가됩니다.
그렇기 때문에 뷰생성시에 사용한 컬럼 이외의 칼럼들은 null을 허용하거나 default값이 설정되어 있어야 합니다.

update vw_emp20 set addr = '서울' where eno= 1010;
==> 뷰를 통하여 update를 할 수 있어요.
뷰 생성시에 사용한 모테이블의 내용이 수정됩니다.


delete vw_emp20 where eno = 1014;
==> 뷰를 통하여 delete를 수행 할 수 있어요.
실제 레코드가 있는 모테이블에서 삭제가 수행됩니다.

**뷰를생성시의 조건에 맞지 않는 레코드를 추가 할 수 있는지 실험 해 봅니다.

insert into vw_emp20 values(3000, '이순신', '서울', '010-9999-9999', 30);

===> 뷰를 생성시에 조건에 맞지 않는 레코드를 추가 할 수 있어요.
그러나 뷰에는 나타지않고 
모테이블에 추가 되었습니다.

**뷰를 생성시에 조건에 맞지 않는 값으로 레코드를 수정할 수 있는지 실험 해 봅니다.

update vw_emp20 set dno =30 where eno = 2000;

==> 뷰를 생성시 설정한 조건에 맞지 않은 값을 뷰를통해 update할 수 있어요.
모테이블의 내용이 변경되고 뷰에는 조건에 맞지 않기 때문에 나타나지 않아요.



** 뷰를 삭제하는 방법

drop view 뷰이름;

** 뷰를 생성시에 설정한 
조건에 맞지 않는 레코드를 추가할 수 없도록
조건에 맞지 않는 값으로 수정할 수 없도록 
하고싶어요!!

create view 뷰이름 as select ~~ 조건식 with check option;

 

with check option를 설정하여 뷰를 생성한 다음
조건에 맞지 않는 레코드를 추가하고 
조건에 맞지 않는 값으로 수정 해 봅니다.

create view vw_emp20
as 
select eno,ename,addr,phone,dno
from emp
where dno =20
with check option;

insert into vw_emp20 values(4000, '유관순', '서울', 010-0000-0000, 20);
insert into vw_emp20 values(5000, '문재인', '서울', 010-0000-0000, 30);

insert into vw_emp20 values(5000, '문재인', '서울', 010-0000-0000, 30)
            *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
==> 뷰 생성시에 with check option을 주었기 때문에 
조건에 맞지 않는 레코드를 추가 없어요!

update vw_emp20 set phone = '010-2222-2222' where eno = 4000;
update vw_emp20 set dno = 30 where eno = 1010;

SQL> update vw_emp20 set dno = 30 where eno = 1010;
update vw_emp20 set dno = 30 where eno = 1010
       *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

**조회(읽기)만 가능한 뷰를 생성

create view 뷰이름 as select ~~  with read only;

ex) vw_emp20을 삭제하고 읽기만 가능한 뷰로 만들어 
데이터를 추가,수정,삭제,조회를 실험해 봅니다.

create view vw_emp20
as
select eno, ename, addr, phone, dno
from emp
where dno = 20
with read only;

select * from vw_emp20;
insert into vw_emp20 values(4001, '김유신', '서울', '010-2222-2222', 20);
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

update vw_emp20 set addr = '인천' where eno = 1010;
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

delete vw_emp20 where eno = 4000;
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

**정리** view에 대하여 설명하시오.
실제로는 존재하지 않는 가상의 논리적인 테이블을 말하며 
복잡한 sql를 대신하거나
사용자별 접근권한을 설정하는 용도로 사용합니다.

뷰를 통해서 추가,수정,삭제가 가능하며

with check option를 설정하여 조건에 맞는 레코드만 추가,수정할 수 있도록 하고
with read only 옵션을 설정하여 읽기만 가능한 뷰를 만들 수 있어요.

 

 

 

 

 

시스템 뷰
==> 오라클이 제공하는 데이터사전을 말합니다.

user_obejcts 사용자가 만든 모든 객체의 정보를 갖고 있어요.
user_tables 사용자 가 만든 모든 테이블의 정보를 갖고 있어요.
user_constraints 사용자가 만든 모든 제약의 정보를 갖고 있어요.


==> 제약의 비활성화

alter table 테이블이름 disable constraint 제약명;

==> 제약의 활성화

alter table 테이블이름 enable constraint 제약명;

SYS_C008320     R                    ORDERS     ENABLED
SYS_C008319     R                    ORDERS     ENABLED
SYS_C008316     P                    BOOK       ENABLED

insert into book values(14, '즐거운 오라클', '쌍용미디어', 30000);

*
1행에 오류:
ORA-00001: 무결성 제약 조건(C##MADANG.SYS_C008316)에 위배됩니다

-------------------------------------------------------------------------------------------------------
alter table orders disable constraint SYS_C008320;
alter table orders disable constraint SYS_C008319;
alter table book disable constraint SYS_C008316;

 select constraint_name, constraint_type, table_name, status from user_constraints;

insert into book values(14, '즐거운 오라클', '쌍용미디어', 30000);

==> 제약을 활성화를 하려면
활성화 하려는 제약에대하여 
만족하는 상태로 만들어야 합니다.

alter table book enable constraint  SYS_C008316;
alter table orders enable constraint SYS_C008319;
alter table orders enable constraint SYS_C008320;


SQL> alter table book enable constraint SYS_C008316;
alter table book enable constraint SYS_C008316
*
1행에 오류:
ORA-02437: (C##MADANG.SYS_C008316)을 검증할 수 없습니다 - 잘못된 기본 키입니다
===> 활성화 하려는 제약에 대한 만족하지 않는 데이터 있어서 오류가 발생합니다.

delete book where bookname '재미있는 자바';

SQL> alter table book enable constraint SYS_C008316;

테이블이 변경되었습니다.

SQL> alter table orders enable constraint SYS_C008319;

테이블이 변경되었습니다.

SQL> alter table orders enable constraint SYS_C008320;

테이블이 변경되었습니다.



insert into book values(14, '재밌는 웹', '쌍용미디어', 35000);

SQL> insert into book values(14, '재밌는 웹', '쌍용미디어', 35000);
insert into book values(14, '재밌는 웹', '쌍용미디어', 35000)
*
1행에 오류:
ORA-00001: 무결성 제약 조건(C##MADANG.SYS_C008316)에 위배됩니다

===> 제약이 활성화 되어 동일한 도서번호의 레코드를 추가할 수 없어요