[Oracle-SQL Developer] View

 

 

 

 

 

 

 

 

---

 

 

 

 

View 뷰

 

CREATE [OR REPLACE] VIEW 뷰이름 AS SELECT문

 

view, 뷰
- DB Object 중 하나(테이블, 시퀀스, 제약사항, 뷰) > CREATE, DROP
- 가상 테이블, 테이블의 복사본, 뷰 테이블 등
-  select문을 저장한 객체

    > 뷰 목적(효과)
    1. 자주 반복되는 쿼리나, 긴 문장의 쿼리를 식별자를 붙여 저장한뒤 간편하게 사용 할 수 있음
    2. 보안 

 

View 사용 시 주의사항

1. SELECT > 실행O 
2. INSERT > 실행O 
3. UPDATE > 실행O
4. DELETE > 실행O 

※SELECT제외하고 전부 사용 금지

 

---

 

예시

 

 

 create or replace view vwEachTeacherLclass
 as
 select 
    distinct t.name as "교사명",
    c.name as "과정명", --
    lc.startclassdate as "개강",
    lc.finishclassdate as "종강",
    cr.name "강의실",
 (select 
    count(distinct(student_seq)) 
from tblteacher t inner join tbllsubject ls on t.teacher_seq = ls.teacher_seq
                  inner join tbllclass lc on lc.lclass_seq = ls.lclass_seq
                  inner join tblsugang su on su.lclass_seq = lc.lclass_seq
    where t.teacher_seq =11) as "등록인원수",
    case
        when lc.startclassdate <= sysdate and lc.finishclassdate >= sysdate then '진행중'
        when lc.startclassdate > sysdate then '예정'
        when lc.startclassdate < sysdate then '종료'
    end as "강의진행상태"
from tblclass c
    inner join tbllclass lc
        on c.class_seq = lc.class_seq
            inner join tbllsubject ls
                on ls.lclass_seq = lc.class_seq
                    inner join tblteacher t
                        on t.teacher_seq = ls.teacher_seq
                            inner join tblclassroom cr
                                on cr.classroom_seq = lc.classroom_seq                                    
                 where ls.teacher_seq = 11
                    order by "개강" asc;

 

 

 

 

---