SQL-VIEW

* View

- 물리적인 공간을 직접적으로 차지하는 segment는 아니다. 지금까지 배운 segment는 index와 table

- 단독으로 존재가 불가능해서 기존 table이 있어야한다.  

 

뷰의 종류
1. 단순 뷰(simple view)
- 단일 table로부터 만들어진 view
- DML 문장 수행이 가능하다.

- 거의 사용하지 않는다. 

 

2. 복합 뷰(complex view)
- 다중 table로부터 만들어진 view
- 함수나 수식 등을 포함한다.
- DML 문장 수행이 불가능하다

 

뷰의 생성과 확인

CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰 (컬럼 ... )

// CREATE는 새로, REPLACE는 존재할 경우 교체. segment가 아니기때문에 alter란 개념이 없다.

// FORCE옵션을 사용하면 table이 없어도 생성 가능하지만 사용하지 않는다. default는 NOFORCE
AS (SELECT 문장 : sub query)
// sub query가 view를 생생하는 실제 구문 
[WITH CHECK OPTION [CONSTRAINT constrant_name]] // 단일 view일때만 사용
[WITH READ ONLY [CONSTRAINT constrant_name]]; // 단일 view일때만 사용

 

SELECT view_name, text FROM user_views; // text에서 subquery문의 사용 가능. user_view에 view가 저장되어 있음.

 

SET LONG 1000

 

[예제 2] 각 일반화학 과목의 학과별 기말고사 평균을 검색하고 뷰로 생성.

SELECT c.cno, cname, major, ROUND(AVG(result))
FROM student s, course c, score r
WHERE s.sno=r.sno AND r.cno=c.cno
AND cname='일반화학'
GROUP BY c.cno, cname, major; // 일반적인 검색 구문

CREATE OR REPLACE VIEW ma_result (과목번호, 과목명, 학과, 기말고사평균)
AS SELECT c.cno, cname, major, ROUND(AVG(result))
FROM student s, course c, score r
WHERE s.sno=r.sno AND r.cno=c.cno
AND cname='일반화학'
GROUP BY c.cno, cname, major; // 위와 같은 검색을 VIEW를 이용해 만든 구문

 

SET LONG 1000

SELECT view_name, text FROM user_views;

SELECT * FROM tab WHERE tabtype = 'VIEW'; // view의 목록 출력

DESC ma_result;

SELECT * FROM ma_result; // view도 일반 table처럼 사용 가능. 데이터를 갖고있는게 아니라 가지고 온다.

 

[예제 3]WITH CHECK OPTION을 이용 뷰를 생성해 보자.

CREATE VIEW st_ch
AS SELECT sno, sname, syear, avr
FROM student
WHERE syear = 1; // 1학년 학생의 정보를 출력하는 view 생성. 단일 뷰.

INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0); // 검색은 1학년만 되지만 2학년의 정보 입력은 가능하다.

SELECT * FROM st_ch WHERE sname = '시현'; // 2학년이기때문에 검색이 되지 않는다.

ROLLBACK; // 잘못된 권한이기때문에 롤백함.

CREATE OR REPLACE VIEW st_ch
AS SELECT sno, sname, syear, avr
FROM student
WHERE syear = 1
WITH CHECK OPTION CONSTRAINT view_st_ch_ck;

 

INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0); // 뷰를 통해 검색되지 않는 데이터는 입력 불가

 

1. 사원의 연봉을 검색할 수 있는 뷰를 생성한다.

CREATE OR REPLACE VIEW y_sal (사번, 이름, 연봉)

AS SELECT eno, ename, sal*12+nvl(comm, 0)

FROM emp;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

2. 학생의 학점 4.5 만점으로 환산된 정보를 검색할 수 있는 뷰를 생성.

CREATE OR REPLACE VIEW st_avr (학번, 이름, 환산_학점)

AS SELECT sno, sname, TO_CHAR(AVG(avr*4.5/4.0), '0.99')

FROM student

GROUP BY sno, sname;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

3. 각 과목별 평균 점수를 검색할 수 있는 뷰를 생성.

CREATE OR REPLACE VIEW avg_result (과목번호, 과목명, 평균점수)

AS SELECT c.cno, cname, TO_CHAR(AVG(result), '00.99')

FROM score r, course c

WHERE r.cno=c.cno

GROUP BY c.cno, cname;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

4. 각 직원과 관리자의 이름을 검색할 수 있는 뷰를 생성.

CREATE OR REPLACE VIEW emp_mgr (사번, 이름, 관리자번호, 관리자이름)

AS SELECT e1.eno, e1.ename, e2.eno, e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.eno;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

 

5. 각 과목별로 평가 점수가 F인 학생을 검색할 뷰를 생성.

CREATE OR REPLACE VIEW f_result (과번, 과목명, 학번, 이름, 평가점수)

AS SELECT c.cno, cname, s.sno, sname, grade

FROM student s, score r, course c, scgrade

WHERE s.sno = r.sno and r.cno = c.cno

AND result between loscore and hiscore

AND grade = 'F'

ORDER BY cname;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

6. 각 과목별로 기말고사가 일등인 학생의 명단을 검색할 뷰를 생성.

CREATE OR REPLACE VIEW course_first (과번, 과목명, 학번, 일등학생이름, 점수)
AS SELECT c.cno, cname, s.sno, sname, result
FROM student s, score r, course c
WHERE s.sno = r.sno and r.cno = c.cno
AND (c.cno, result) IN (SELECT c.cno, MAX(result)
                              FROM score r, course c
                              WHERE r.cno = c.cno
                              GROUP BY c.cno);

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력

7. 물리학과 교수의 과목을 수강하는 학생의 명단을 검색할 뷰를 생성.

CREATE OR REPLACE VIEW physical_student (교수학과, 교수이름, 과번, 과목명, 학번, 학생이름)
AS SELECT section, pname, c.cno, cname, s.sno, sname
FROM student s, score r, course c, professor p
WHERE s.sno = r.sno and r.cno = c.cno and c.pno = p.pno
AND section = '물리'
ORDER BY pname, cname;

 

select view_name, text from user_views; // 뷰의 목록과 내용 출력

select * from tab where tabtype = 'VIEW'; // 뷰의 목록 출력