13_SQL VIEW의 사용

 

View란?

   - 가상의 테이블

   - 실제 Table 에는 데이터가 있지만, View 에는 데이터는 없고 SQL만 저장

   - View에 접근할때는 View에 들어있던 SQL의 수행된 결과를 가져오는 것

 

 

 

 

 

 

View를 사용하는 목적

• 보안성 : 특정 테이블의 컬럼들이 보여지면 안되는 경우
• 편의성 : 여러개의 테이블을 JOIN하는게 번거로울 때 view로 만들어 놓으면 편리

 

 

 

 

 

View의 사용

1. 뷰의 생성

 

   (1) View 사용 권한부여

       오라클의 경우 View 를 생성하려면 권한을 부여 받아야 한다

       시스템 계정으로 create view 권한을 view를 사용할 계정에 부여하자. 

        >>  GRANT CREATE VIEW TO [USER]; 

        >>  GRANT CREATE PROCEDURE TO [USER]; 

 

        부여받은 권한에 대해서는 SYSTEM 계정으로 확인 가능

        >>  SELECT PRIVILEGE FROM DBA_SYS_PRIVS
              WHERE GRANTEE ='계정ID(대문자)';           

 

 

   (2) View 생성 구문

       >>  CREATE [OR REPLACE] VIEW 뷰이름 [컬럼명] 
             AS                                                                  

             (Sub Query)                                                        

            * OR REPLACE 는 혹시 이전에 같은 이름의 view 가 있으면 삭제하고 새로 만들라는 의미

 

        View 생성시 별도의 컬럼이름 설정 가능

        >>  CREATE OR REPLACE VIEW v_prof(pfno, nm, em, hp)   

              AS                                                                                       

              SELECT profno, name, email, hpage FROM t_professor; 

           * t_professor 테이블의 컬럼 4개를 복사해서 뷰를 생성하는 구문

 

 

 

2. 뷰의 삭제

     DROP VIEW [VIEW 이름]; 

 

 

 

3. 뷰의 조회 

   >>  SELECT * FROM  [뷰 이름];    

   >>  SELECT tname FROM tab;     -- 테이블 목록을 출력하는 명령어로 뷰도 조회가능

 

   * 특정 사용자가 가진 View 조회

   >>  SELECT owner AS schema_name, view_name 

         FROM sys.all_views                                            

         WHERE owner = '사용자이름(대문자)'                

         ORDER BY owner, view_name;                         

 

 

 

 

 

 

 

View의 종류

1) 단순 View (Simple View)

     : View를 생성할 서브쿼리에 join 조건없이 1개의 테이블로 만들어지는 간단한 View

 

     Ex1)  t_professor 테이블의 profno, name, email, hpage 칼럼만 사용하는 view를 생성하라.

              (단 view 의 이름은 v_prof)

 

             =  CREATE OR REPLACE VIEW v_prof                                   

                  AS                                                                                       
                  SELECT profno, name, email, hpage FROM t_professor; 

 

Ex1) v_prof 뷰 생성 결과

Ex1) v_prof 뷰 조회

 

2) 복합 View (Complex View)

     : View SQL 의 서브쿼리에 여러개의 테이블이 Join 되어 생성

 

     Ex2)  t_professor, t_department 테이블을 join 하여 교수번호, 교수이름, 소속학과명을

              조회하는 view를 생성하라  (테이블 이름은 v_prof_dept)

 

            =  CREATE OR REPLACE VIEW v_prof_dept                                                
                 AS                                                                                                                
                 SELECT p.profno "교수번호", p.name "교수명", d.dname "소속학과"      
                 FROM t_professor p, t_department d WHERE p.deptno = d.deptno;   

 

Ex2) v_prof_dept 뷰 생성 결과

 

Ex2) v_prof_dept 뷰 조회

 

3) 인라인 View (Inline View)

     : View 는 한번 만들어 놓으면 계속 사용할수 있다.

       그러나 1회용으로만 사용할 경우는 FROM 절의 서브쿼리 형태로 만들수 있다. 이를 인라인 뷰라 한다.

 

      Ex3)  t_student, t_department 테이블을 join해서 학과별로 학생들의 최대키와 최대몸무게, 학과 이름을 출력하라

           

              ① Group by를 이용해서 학과별 최대키와 최대몸무게를 구해보자 

                  최대키와 최대몸무게는 값을 알아보기 쉽게 별칭설정(max_height / max_weight)

 

                  =   SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight    

                       FROM t_student  GROUP BY deptno1; 

 

                  ②  위에서 작성한 쿼리문을 하나의 테이블이라고 생각해보자.

                       그럼 우리는 '학과별 학생들의 최대키와 최대 몸무게' 데이터가 들어있는 테이블을 얻었으니,

                      t_department 테이블과 join해서 학과명을 얻으면 된다.

 

                       두 테이블을 구분하기 위해서 각 테이블에 별칭을 붙여주고 ( ①의 쿼리문 = s, t_department 테이블 = d)

                       각 필드가 어느 테이블의 자료인지 소속을 표현해주자. * 단, 두 테이블의 '학과번호'가 동일한지 조건 검사 필수

 

                   =   SELECT  d.dname "학과명", s.max_height "최대키",  s.max_weight "최대몸무게"     

                        FROM  ( SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight 

                        FROM t_student  GROUP BY deptno1 ) s , t_department d                                      

                        WHERE s.deptno1 = d.deptno;                                                                                     

                 

Ex3