SQL-View

View

---

다른 table로부터 유도된 이름을 가진 virtual table을 말한다.

• 기본 base table은 물리적으로 구성되어있다.
• 하지만 View는 반드시 물리적으로 구현되어 있는 것은 아니다.

 -> 뷰의 정의만 시스템 내에 저장하여 두었다가 가져다 쓰는 것

 -> 데이터는 저장되지 않고 View가 사용될 때만 만들어지는 table

<뷰 생성>

CREATE VIEW <뷰 이름> [(열_이름_리스트)]

         AS SELECT 문

    [WITH CHECK OPTION];

※주의※

• AS SELECT문은 기존의 SELECT 문과는 다르게 UNION이나 ORDER BY를 사용할 수 없다.
• [WITH CHECK OPTION]절은 이 뷰에 대한 갱신이나 삽입 연산을 실행할 때 뷰 정의 조건을 위반하면 실행이 거부된다.
• 뷰의 정의는 기본 테이블처럼 ALTER문을 이용하여 변경할 수 없다. 다만, 필요 없는 뷰는 제거하고 필요한 뷰를 정의하면 된다.

<뷰 제거>

DROP VIEW <뷰 이름> {RESTRICT | CASCADE};

RESTRICT 는 다른 곳에서 참조되고 있지 않는 한 DB에서 제거되어 없어진다.

CASCADE 는 해당 뷰 뿐만 아니라 이 뷰가 사용된 다른 모든 뷰나 제약 조건이 함께 제거된다.

수업 시간 코드

---

<기본 DB 틀>

drop table ENROL;
drop table STUDENT;
drop table COURSE;

create table STUDENT (
  sno number(3) primary key,
  sname nvarchar2(4) constraint sname_always_exists not null,
  year number(1) default 1,
  dept nvarchar2(5),
  unique (dept, sname),
  constraint year_check check (year >=1 and year <=4 )
);

 insert into STUDENT values (100, '나수영', 4, '컴퓨터');
 insert into STUDENT values (200, '이찬수', 3, '전기');
 insert into STUDENT values (300, '정기태', 1, '컴퓨터');
 insert into STUDENT values (400, '송병길', 4, '컴퓨터');
 insert into STUDENT values (500, '박종화', 2, '산공');
 
create table COURSE (
  cno char(4),
  cname nvarchar2(10) not null,
  credit number(1) not null,
  dept nvarchar2(4) not null,
  professor nvarchar2(4),
  primary key(cno),
  unique (cno,professor)
);

insert into COURSE values ('C123', 'C프로그래밍',  3, '컴퓨터', '김성국');
insert into COURSE values ('C312', '자료구조',    3, '컴퓨터', '황수관');
insert into COURSE values ('C324', '화일구조',    3, '컴퓨터', '이규찬');
insert into COURSE values ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
insert into COURSE values ('E412', '반도체',    3, '전자',   '홍봉진');
 
 
  create table ENROL (
  sno number(3) not null,
  cno char(4) not null,
  grade char,
  midterm integer,
  finterm integer,
  primary key (sno, cno),
  foreign key (sno) references STUDENT(sno)
 on delete cascade,
  foreign key (cno) references COURSE
 on delete cascade,
  check (grade in ('A', 'B', 'C', 'D', 'F'))
  );
 
insert into ENROL values (100, 'C413', 'A', 90, 95);
insert into ENROL values (100, 'E412', 'A', 95, 95);
insert into ENROL values (200, 'C123', 'B', 85, 80);
insert into ENROL values (300, 'C312', 'A', 90, 95);
insert into ENROL values (300, 'C324', 'C', 75, 75);
insert into ENROL values (300, 'C413', 'A', 95, 90);
insert into ENROL values (400, 'C312', 'A', 90, 95);
insert into ENROL values (400, 'C324', 'A', 95, 90);
insert into ENROL values (400, 'C413', 'B', 80, 85);
insert into ENROL values (400, 'E412', 'C', 65, 75);
insert into ENROL values (500, 'C312', 'B', 85, 80);

commit;

---

<예제>

select * from student;

select * from student where dept = '컴퓨터';
-- 이결과를 table 로 만들어주는 것을 view라고 한다.

create view comp_student as select * from student where dept = '컴퓨터';
--                           이 select 문의 결과가 table이 만들어진다.

select * from (select * from student where dept = '컴퓨터');
-- 이것과 원리가 똑같다.

select * from comp_student;

insert into comp_student values (600, '홍길동', 3, '컴퓨터');
insert into comp_student values (700, '쯔위', 2, 'IT');
-- 즉 base table에서 데이터가 저장되고 거기서 걸러진 다음 다시 comp_student가 생성된다.
-- insert를 해도 comp_student의 조건에 합당하지 않다.

drop view comp_student;
-- 있는 뷰를 삭제한다.
create view comp_student as select * from student where dept = '컴퓨터'
with check option;
insert into comp_student values (800, '공유', 3, 'IT');
--뷰가 with check option을 사용하면 comp_student 의 select 옵션을 두고 insert를 통과 시킨다.

select *from all_catalog;
select *from all_views where view_name = 'COMP_STUDENT';

select *from student natural join enrol;
select * from comp_student natural join enrol;

--과목 번호(cno), 중간고사 평균, 기말고사 평균을 보여주는 뷰를 작성
drop view test_enrol;
drop view course_avg;
create view course_avg as select cno, avg(midterm) as 중간평균, avg(finterm) as 기말평균  from enrol group by cno;
select cno, avg(midterm), avg(finterm) from enrol group by cno;

create view course_avg(cno, avg_mid, avg_fin) as select cno, avg(midterm) as 중간평균, avg(finterm)as 기말평균  from enrol group by cno;

select *from course_avg;

---

create view course_avg_with_cname
as select cno, cname ,credit, avg_mid, avg_fin from course natural join course_avg;

select * from course_avg_with_cname;

insert into course_avg values ('C312',90,95);
update comp_student set year =4 where sno = 600;
select* from student;
-- comp_student는 갱신 데이터 입력 등이 다 된다.
-- 하지만 course_avg 뷰는 insert가 되지 않는다.update도 되지 않는다. 왜??
-- course_avg는 base table에 매칭이 되지않는다.

다음과 같은 경우에는 변경이 허용되지 않는다.

1. 뷰의 열이 상수나 산술 연산자 또는 함수가 사용된 산술 식으로 만들어지면 변경이 허용되지 않는다.

2. 집계 함수(COUNT, SUM, AVG, MAX, MIN)가 관련되어 정의된 뷰는 변경할 수 없다.

3. DISTINCT, GROUP BY 또는 HAVING이 사용되어 정의된 뷰는 변경할 수 없다.

4. 두 개 이상의 테이블이 관련되어 정의된 뷰는 변경할 수 없다.

5. 변경할 수 없는 뷰를 기초로 정의된 뷰는 변경할 수 없다.

뷰의 장단점

장점

1. 뷰는 데이터의 논리적 독립성을 제공한다. 이유는 base table에 데이터 삽입, 갱신 하더라도 뷰에는 영향을 끼치지 않는다.

2. 데이터 접근 제어를 통하여 데이터의 보안을 제공할 수 있다.

3. 사용자의 데이터 관리를 간단하고 쉽게 해 준다. 이유는 필요한 데이터만 따로 처리할 수 있기 때문에

4. 여러 사용자의 상이한 응용ㅇ나 요구를 지원해 줄 수 있다.

단점

1. 뷰는 정의를 변경할 수 없다. 이유는 데이터를 물리적 구조로 저장하지 않기 때문이다. (drop시키고 다시 만든다.)

2. 변경(삽입, 삭제, 갱신 연산)에 제약이 많다. 등