Chapter 6 SQL - View

뷰(View)

• 다른 테이블(기본 테이블, base table)로 유도된 가상의 테이블(virtual table)
  • 생성된 뷰는 일반 테이블처럼 사용할 수 있음
• 기본 테이블은 데이터(레코드)가 실제 데이터베이스에 저장되지만 뷰의 경우에는 정의(definition)만 저장됨
  • 데이터는 저장되지 않고 뷰가 사용될 때만 만들어지는 테이블
  • 실제로는 뷰를 사용하는 SQL문에서 뷰 대신 뷰 정의에 해당하는 SQL문으로 대치됨
• 뷰를 기반으로 새로운 뷰를 정의할 수도 있음

 

뷰의 정의(View Definition)

create view <뷰 이름> [컬럼 이름 리스트]
as select 문
[with check option];

• select 문은 union 또는 order by를 포함할 수 없음
• 컬럼명을 지정하지 않으면 select 결과의 컬럼명과 동일

뷰의 확인(Oracle에서)

• all_catalog: 테이블, 뷰, 시퀀스 확인
• all_views: 뷰의 정의 확인

 

뷰의 삭제

drop view <뷰 이름> [restrict | cascade]

restrict의 경우에는 뷰가 다른 곳에서 사용되지 않을 때만 삭제됨

cascade의 경우에는 해당 뷰를 사용하여 유도된 뷰들도 같이 삭제됨

※ Oracle의 경우에는 restrict 기능이 없으며 cascade 옵션 대신 cascade constraints로 사용해야 하며 삭제되는 view와 관련된 제약조건들(constrraint)를 함께 삭제하라는 의미임

 

실습 - 뷰 정의

STUDENT 테이블에서 '컴퓨터'과에 속한 학생들만으로 구성된 뷰 CSTUDENT를 다음과 같이 정의하고, SELECT 명령을 사용하여 데이터를 확인하라. 또한 카탈로그 정보를 통해 View 정보를 확인하라.

create view CSTUDENT	-- View(Virtual Table)
as select *
    from STUDENT	-- Base Table
    where dept='컴퓨터';
    
select * from cstudent;

View를 테이블처럼 사용 가능

테이블이 어딘가에 저장되는 것이 아니라 테이블이 들어가야 할 자리에 select 문이 들어간 것

View update

뷰와 베이스 테이블 모두 update 됨

View는 base table에서 select 된 결과를 보여주는 것이지 별도의 새로운 테이블이 생성되는 것은 아니다.

all_catalog

테이블명 키워드명, 컬럼명은 대소문자를 가리지 않지만 데이터 값은 대소문자를 가린다.

예를 들어 데이터베이스 이름이 'DB123456789'라면 owner='db123456789'라고 썼을 때 에러가 발생한다.

all_views

 

실습 문제 - with check option의 기능

• 아래와 같은 SQL문을 이용하여 CSTUDENT_WITH_CHECK를 각각 정의한 다음

create view cstudent_with_check
as
select *
from student where dept='컴퓨터'
with check option;

• 다음 명령을 차례로 각각 실행한 다음 그 결과를 확인하고 결과에 차이가 나타나는 이유를 생각해 보라.

insert into cstudent_with_check values (600, '홍길동', 1, 'IT');
insert into cstudent values (600, '홍길동', 1, 'IT');

insert into cstudent_with_check values (600, '홍길동', 1, 'IT');

우선 첫 번째 명령을 실행하면 에러가 난다.

view 정의에 with check option을 추가하면 view에 레코드를 추가하거나 업데이트할 때 조건을 만족하는지 확인하기 때문이다.

insert into cstudent values (600, '홍길동', 1, 'IT');

그리고 두 번째 명령을 실행하면 에러가 나지는 않으나

cstudent의 정의와 맞지 않기 때문에 cstudent를 select 했을 때 출력되지 않는다.

대신 student 테이블에 레코드가 추가되었다.

애초에 cstudent는 테이블이 아닌 뷰이기 때문에 레코드를 출력하는 sql문일 뿐이다.

따라서 뷰에 레코드를 insert 하거나 update하는 것은 말이 안 되고 대신 베이스테이블이 변경된다.

 

실습 - 다양한 뷰의 정의

-- 학과 별로 학생수를 알려주는 View 정의
create view DEPTSIZE
as
select dept, count(*) as num
from student
group by dept;

-- 학생의 이름, 학년, 학과 정보를 모은 뷰
create view STUDENT_NAME
as
select sname, year, dept
from student;

-- 'A' 학점을 받은 학생의 학번, 이름, 과목번호를 보여주는 뷰
create view HONOR_STUDENT
as
select sno, sname, cno
from student natural join enrol
where grade='A';

 

실습 - 뷰를 이용해서 뷰를 생성

• CSTUDENT 뷰를 사용하여 CSTUDENT_SENIOR 뷰를 생성(유도)한다.

create view cstudent_senior
as
select *
from cstudent
where syear = 4;

 

실습 - Base Table에 데이터 추가

• 다음과 같이 Base Table인 STUDENT에 새로운 행을 추가하고 CSTUDENT와 CSTUDENT_SENIOR 뷰를 각각 select 해보라.

insert into student values (700, '이몽룡', 4, '컴퓨터');
insert into student values (800, '성춘향', 1, '컴퓨터');

베이스테이블이 변경되었으므로 뷰의 출력도 바뀌었다.

 

실습 - 뷰의 삭제

• 아래 SQL 문을 순서대로 실행하고 각 명령의 실행결과를 검토하라.

drop view cstudent;
select * from cstudent_senior;

create view CSTUDENT
as *
from STUDENT where dept='컴퓨터';

select * from cstudent_senior;

base table로 사용된 view가 삭제되었기 때문에 유도된 테이블인 cstuden_senior를 사용할 수 없게 되었다.

drop view cstudent restrict;

※ 오라클에서는 지원하지 않지만 이렇게 하면 해결된다.

그리고 다음 명령을 차례대로 실행하면 삭제하기 전과 같이 정상적으로 동작한다.

 

실습 - 뷰에 대한 변경

• 앞서 정의한 CSTUDENT 뷰와 DEPTSIZE 뷰에 대해 아래 SQL문을 실행하고, 결과를 검토하라.

student_name은 앞서 만든 뷰이다.

뷰는 자체적으로 테이블을 갖고 있는 것이 아니라 베이스 테이블의 데이터를 다루기 때문에 레코드를 insert 할 때 베이스 테이블의 형식에 맞춰야 한다.

update student_name set year=4 where dept='컴퓨터';

'컴퓨터'과 학생들의 학년이 4로 바뀌었다.

num은 view를 생성할 때 특정 열에 지어준 이름일 뿐, 베이스테이블에 존재하지 않기 때문에 변경할 수 없다.

정상적으로 실행이 되었고 결과를 검토해보면

student 테이블에는 sno=100인 레코드가 지워지지 않았고

enrol 테이블에만 sno=100인 레코드가 지워졌다.

아마 honor_student를 정의할 때 where절의 조건이 enrol에만 해당이 되기 때문이지 않을까?

∴ 뷰를 통해서 데이터를 변경하는 것은 상당히 모호함. 되는 경우도 있고 안 되는 경우도 있음.

 

뷰의 특징

• 뷰는 일반적으로는 변경이 불가능하지만 제한적으로 변경 가능한 뷰도 있음 (변경: 삽입, 삭제, 갱신)
• 뷰에 INSERT를 하려면 키가 포함되어 있어야 함(필요 조건)
• 뷰에 UPDATE 또는 DELETE를 하려면 기반테이블에서 갱신 또는 삭제 대상이 되는 행을 식별할 수 있어야 함
  • 서로 다른 테이블들을 조인한 결과물 또는 통계 정보를 뷰로 정의한 경우에는 갱신이 불가능할 수 있음

뷰의 장단점

장점	단점
데이터 독립성을 제공한 한 방법	정의를 변경할 수 없음
뷰에 대한 접근 제어를 통해 보안성 제공	변경(삽입, 삭제, 갱신)에 제한이 있음
데이터의 관리가 용이
사용자 별로 서로 다른 데이터를 보여줄 수 있음

create view cstudent
as
select *
from student
where dept='컴퓨터';

select * from cstudent;
select * from cstudent where year=4;

select * from (select *
from student
where dept='컴퓨터') where year=4;

update cstudent set year=2 where sno=300;

select * from student;

select * from all_catalog;
select * from all_catalog where owner='DB201811658';

select * from all_views where owner='DB201811658';

create view cstudent_with_check
as
select *
from student where dept='컴퓨터'
with check option;

select * from cstudent_with_check;

insert into cstudent values (600, '홍길동', 1, 'IT');

select * from cstudent;
select * from student;
select * from cstudent_with_check;

create view DEPTSIZE
as
select dept, count(*) as num
from student
group by dept;

select * from DEPTSIZE;

create view STUDENT_NAME
as
select sname, year, dept
from student;

select * from student_name;

create view honor_student
as
select sno, sname, cno
from student natural join enrol
where grade='A';

select * from honor_student;

create view cstudent_senior
as
select *
from cstudent
where year=4;

select * from cstudent_senior;

insert into student values (700, '이몽룡', 4, '컴퓨터');
insert into student values (800, '성춘향', 1, '컴퓨터');

select * from student;
select * from cstudent;
select * from cstudent_senior;

drop view cstudent;

select * from all_views where owner='DB201811658';

create view cstudent
as
select *
from student
where dept='컴퓨터';

select * from cstudent_senior;

select * from student_name;

insert into student_name values ('심청', 1, '전자');

update student_name set year=4 where dept='컴퓨터';

select * from student;

select * from deptsize;

update deptsize set num=10;

delete honor_student where sno=100;

select * from student;

select * from enrol;