[days12] View

VIEW

1. 테이블을 보기 위한 창문 - 창문으로 보이는 만큼만 보겠다(보안적인 측면)

2. View == select

 

3.  View : insert, update, delete도 상황에 따라 가능하긴 하지만 궁극적으로 select를 위한 것

4. 한 개 이상의 기본테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블(virtual table)

5. 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체 데이터 중에서 일부만 접근할 수 있도록 제한하기 위한 기법이다.

6. 뷰는 [데이터 딕셔너리 테이블]에 [뷰에 대한 정의]만 저장

7. 테이블에서 파생된 객체

8. 뷰를 이용하면 중요한 데이터를 보호하기 위한 보안(security) 목적과 사용자에게 편의성(flexibility)를 제공하기 위한 목적을 달성하기 위해서이다.

9. DML, 제약조건 설정 가능

10. 뷰는 기본 테이블에서 정의된 모든 무결성 제약조건을 상속 + 제약조건 설정(9.번)

11. 뷰에 대한 정의는 user_views 데이터 딕셔너리 테이블을 통해서 조회할 수 있다.
    뷰는 실제 저장공간을 가지는 것이 아니라 데이터 딕셔너리에 정의해서 정의 부분만 가진다.

  SCOTT이 소유하고 있는 View정보 조회
    select *
    from user_views;

12. 뷰의 종류(기준 : 테이블 수)
    1) 단순뷰(1)
    2) 복합뷰(2이상)

 

13. 뷰 생성

【형식】
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
[(alias[,alias]...]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];

create or replace view panView 
--or replace 습관적으로 붙혀라 : 있으면 수정하고 없으면 생성하겠다
--FORCE 기본 테이블의 유무에 상관없이 뷰를 생성 (디폴트)
--NOFORCE 기본 테이블이 있을 때만 뷰를 생성
as 
    select b.b_id, title, price, g.g_id, g_name, p_date, p_su
    from book b join danga d on b.b_id = d.b_id 
                join panmai p on p.b_id = b.b_id
                join gogaek g on g.g_id = p.g_id;
--WITH CHECK OPTION 뷰에 의해 access될 수 있는 행(row)만이 삽입, 수정 가능 
--WITH READ ONLY DML 작업을 제한(단지 읽는 것만 가능) insert, update, delete 불가, select만 가능

--View를 생성하고자 하니까 권한 없다 에러
--ORA-01031: insufficient privileges 권한 불충분
--sys-> scott 계정생성 -> 접속(Connect) create session 권한 에러
--role : resource, connect 롤2개부여했음 ( 이 안에 권한들이 뭉터기로 있음)
create view 권한이 있어야 한다. =>부여 (DCL - grant, revoke)
항상 권한을 부여할땐 권한을 가진 사람이 해야한다 -> sys로 가자

grant create view to scott;

부여한 후 권한 확인 작업

select *
from user_sys_privs; 

14. 뷰 사용

select *
from panview; 

15. 뷰 소스 확인

select text
from user_views
where view_name = 'PANVIEW';

16. 뷰 삭제

drop view panview;

 

17. 뷰 수정

create or replace view panView 
as 
    select b.b_id, title, price, g.g_id, g_name, p_date, p_su
            ,p_su*price amt
    from book b join danga d on b.b_id = d.b_id 
                join panmai p on p.b_id = b.b_id
                join gogaek g on g.g_id = p.g_id
    order by p_date desc; --만든 후에 수정하고 다시한번더 실행하면 됨
--View PANVIEW이(가) 생성되었습니다.

 

(문제)

-- 년도별 월별 고객코드 고객명 판매금액합(년도별, 월) 조회
--      년 asc, 월 asc
--      뷰를 생성 (gogaekView)

create or replace view gogaekView
as
    select to_char(p_date, 'yyyy') p_year, to_char(p_date, 'mm') p_month, g.g_id, g.g_name, sum(p_su*price) amt
    from panmai p join gogaek g on p.g_id = g.g_id
            join danga d  on p.b_id = d.b_id
    group by to_char(p_date, 'yyyy'), to_char(p_date, 'mm'), g.g_id, g.g_name
    order by p_year asc,p_month asc;
    
    
    
select *
from gogaekView;

18. view를 사용해 DML 사용

CREATE TABLE testa (
   aid NUMBER             PRIMARY KEY
    ,name VARCHAR2(20) NOT NULL
    ,tel VARCHAR2(20)    NOT NULL   -- NN 제약조건 설정...
    ,memo VARCHAR2(100)
);


INSERT INTO testa (aid, NAME, tel) VALUES (1, 'a', '1');
INSERT INTO testa (aid, name, tel) VALUES (2, 'b', '2');
INSERT INTO testa (aid, name, tel) VALUES (3, 'c', '3');
INSERT INTO testa (aid, name, tel) VALUES (4, 'd', '4');
commit;

select * from testa;


CREATE TABLE testb (
   bid NUMBER PRIMARY KEY
    ,aid NUMBER 
        CONSTRAINT fk_testb_aid
        REFERENCES testa(aid)
        ON DELETE CASCADE  -- testa(aid) 삭제를 하면 testb( aid ) 행 자동(강제) 삭제
    ,score NUMBER(3)
);

INSERT INTO testb (bid, aid, score) VALUES (1, 1, 80);
INSERT INTO testb (bid, aid, score) VALUES (2, 2, 70);
INSERT INTO testb (bid, aid, score) VALUES (3, 3, 90);
INSERT INTO testb (bid, aid, score) VALUES (4, 4, 100);
commit;

select * from testb;

-------조인해보자
select a.aid, name, tel, memo, bid, score
from testa a join testb b on a.aid=b.aid;
--AID NAME TEL MEMO BID SCORE
--1 	a	1		1	80
--2	    b	2		2	70
--3	    c	3		3	90
--4	    d	4		4	100
--뷰를 통해 실제 테이블에 DML 작업
create or replace view aView --테이블 하나로부터 만들어짐 ->단순뷰
as 
    select aid, name, tel--, memo 메모는 null 허용
    from testa;
--View AVIEW이(가) 생성되었습니다.
select * from aView; --이 view를 통해 Insert

insert into aView (aid, name, memo) values (5, 'e', null);
--ORA-01400: cannot insert NULL into ("SCOTT"."TESTA"."TEL")
insert into aView (aid, name ,tel) values (5, 'e', '5');
--1 행 이(가) 삽입되었습니다.
commit;

 

--점수가 90점 이상인 뷰를 조회하는 뷰
create or replace view bview
as
    select bid, aid, score
    from testb
    where score>=90;
select * from bview;
--update    bid=3 score=90 -> 70으로 변경
update bview
set  score=70
where bid=3;
--4	4	100 (70점이 돼서 빠져버림)
rollback;

--수정
create or replace view bview
as
    select bid, aid, score
    from testb
    where score>=90
    with check option constraint ck_bview; --제약조건명
select * from bview;
--update
update bview
set  score=70
where bid=3;
--ORA-01402: view WITH CHECK OPTION where-clause violation
--뷰를 생성할때 90점 이상인 것만 가져왔기 때문에 insert를 하던 update를 하던
--90점 이상으로만 가능하다.
update bview
set  score=95
where bid=3;
--1 행 이(가) 업데이트되었습니다.