Oracle View

1. 뷰의 생성

// STUDENT테이블의 컴퓨터학과 학생들로 컴퓨터학과 학생 (CSTUDENT)라는 뷰 생성
SQL> CREATE OR REPLACE VIEW cstudent(s_id, s_name, s_year)
AS
SELECT s_id, s_name, s_year
FROM student
WHERE s_major = '컴퓨터학과';

SQL> SELECT * FROM cstudent;

SQL> CREATE OR REPLACE VIEW MAJOR_SIZE
AS
SELECT s_major, COUNT(*) AS st_num
FROM student
GROUP BY S_MAJOR;

SQL> SELECT * FROM MAJOR_SIZE;

SQL> CREATE OR REPLACE VIEW ENROLL_2004(s_id, s_nmame, c_id)
AS
SELECT s.s_id, s.s_name, e.c_id
FROM student s, enroll e
WHERE s.s_id = e.s_id AND e_year = '2004';

2. USER_VIEWS 테이블
// 뷰의 구조 및 이름 확인

SQL> COL view_name FROM a15
SQL> COL text_length FORMAT 99,990
SQL> COL text FORMAT a40
SQL> SELECT view_name, text_length, text FROM user_views;

VIEW_NAME	TEXT_LENGTH	TEXT
MAJOR_SIZE	64	SELECT s_major, COUNT(*) AS st_num FROM student GROUP BY S_MAJOR
ENROLL_2004	98	SELECT s.s_id, s.s_name, e.c_id FROM student s, enroll e WHERE s.s_id = e.s_id AND e_year = '2004'

3. 뷰를 통한 데이터 변경
// 뷰가 하나의테이블을 사용하고, 적절한 권한이 있다면
// INSERT, UPDATE, DELETE문을 사용하여 뷰가 참조하는 테이블의 데이터 변경 가능

SQL> UPDATE cstudent
SET s_name='cherrykyun'
WHERE s_id='20051234';

SQL> SELECT s_id, s_name, s_year
FROM cstudent
WHERE s_id = '20051234';

SQL> SELECT s_id, s_name, s_year
FROM student
WHERE s_id = '20051234';

4. WITH CHECK OPTION절 사용
// 뷰를 통해 수행되는 INSERT / UPDATE는 삽입되거나 갱신되는 데이터에 대해 무결성 제약조건과 데이터 검증 체크를 한다.

SQL> CREATE OR REPLACE VIEW
cstudent_year4(s_id, s_name, s_year)
AS
SELECT s_id, s_name, s_year
FROM student
WHERE s_major='컴퓨터학과' AND s_year=4
WITH CHECK OPTION CONSTRAINT
CSTUDENT_YEAR4_CK;

SQL> UPDATE cstudent_year4
SET s_year=3
WHERE s_id='20051235';

ORA-01402: view WITH CHECK OPTION where-clause violation  

0.03 seconds

5. READ ONLY옵션 사용

// 뷰에서 삽입, 갱신, 삭제가 불가능해진다.
SQL> CREATE OR REPLACE VIEW mstudent(s_id, s_name, s_year)
AS
SELECT s_id, s_name, s_year
FROM student
WHERE s_major='멀티미디어학과'
WITH READ ONLY;

SQL> SELECT * FROM MSTUDENT;

SQL> DELETE FROM mstudent
WHERE s_id='200203451';

ORA-01752: cannot delete from view without exactly one key-preserved table

6. 뷰 삭제

SQL> DROP VIEW mstudent;