[Orcale SQL] VIEW

> # VIEW 객체

• 테이블과 유사한 객체

• SELECT 문의 실행결과 집합(가상의 테이블)

• 필요한 정보가 여러 테이블에 분산되어 있고 해정 정보를 추출허눈 쿼리가 자주 사용되는 경우

• 테이블의 컬럼 중 일부컬럼만 추출이 필요한 경우

• 특정자료의 접근은 제한하고 결과만 제공하고자 하는 경우(보안)

  (사용형식)
  CREATE [OR REPLACE][FORCE|NOFORCE] VIEW 뷰이름[(컬럼list)]
  AS
  SELECT 문
  [WITH CHECK OPTION][WITH READ ONLY];

  • REPLACE : 이미 같은 이름의 뷰가 존재하면 대치
  • FORCE : 기준 테이블이 없어도 뷰 생성, 기본은 NOFORCE
  • 컬럼list : 뷰에서 사용할 컬럼명(생략하면 SELECT문의 컬럼별칭이 뷰의 컬럼명이 되고
    컬럼 별칭이 없는 경우 SELECT문의 컬럼명이 뷰의 컬럼명이 됨
  • WITH CHECK OPTION: SELECT 문의 WHERE 절 조건을 위배하는 뷰에 대한 DML 명령은 수행될 수 없음(오류)
  • WITH READ ONLY: 읽기전용 뷰 생성
    ** 뷰에 대한 DML 명령이 제한되더라도 원본테이블에 대한 DML 명령은 제한없이 사용되고 그 결과는 뷰에 즉시 반영됨

사용예) 회원테이블에서 마일리지가 10000을 넘는 회원의 회원번호, 회원명, 마일리지, 직업을 조회하여 그 결과를 뷰로 저장하시오

  CREATE OR REPLACE VIEW N_MEM_MILE(MID, MNAME, MILEAGE, MJOB)
  AS 
    SELECT MEM_ID AS 회원번호, MEM_NAME AS 회원명, MEM_MILEAGE AS 마일리지, MEM_JOB AS 직업
      FROM MEMBER
     WHERE MEM_MILEAGE>=10000;
     
       CREATE OR REPLACE VIEW N_MEM_MILE
  AS 
    SELECT MEM_ID AS 회원번호, MEM_NAME AS 회원명, MEM_MILEAGE AS 마일리지, MEM_JOB AS 직업
      FROM MEMBER
     WHERE MEM_MILEAGE>=10000;
     
       CREATE OR REPLACE VIEW N_MEM_MILE
  AS 
    SELECT MEM_ID , MEM_NAME , MEM_MILEAGE , MEM_JOB
      FROM MEMBER
     WHERE MEM_MILEAGE>=10000;
     
     SELECT * FROM N_MEM_MILE;

사용예) 생성된 뷰(N_MEM_MILE)에서 'c001'회원의 마일리지를 5000으로 변경하시오

  UPDATE N_MEM_MILE
      SET MEM_MILEAGE = 5000
    WHERE MEM_ID='c001'; 

사용예) 회원 테이블에서 'c001'회원의 마일리지를 15000으로 변경하시오

  UPDATE MEMBER
      SET MEM_MILEAGE=15000
    WHERE MEM_ID='c001';

사용예) 회원테이블에서 마일리지가 20000이상인 회원의 회원번호, 회원명, 마일리지, 연락처(핸드폰번호)로 읽기전용 뷰를 생성하시오

  CREATE OR REPLACE VIEW V_MEM_MILE02
  AS 
     SELECT MEM_ID AS 회원번호, MEM_NAME AS 회원명, MEM_MILEAGE AS 마일리지, MEM_HP AS 핸드폰번호
       FROM MEMBER
      WHERE MEM_MILEAGE >=20000
      WITH READ ONLY ;
      

사용예) 뷰 V_MEM_MILE02에서 'r001' 회원을 삭제하시오 -- 컴파일에러. 읽기전용뷰라

      DELETE V_MEM_MILE02
       WHERE 회원번호='r001'; -- 뷰에서 적용된 그 컬럼명을 그대로 써줘야함

사용예) MEMBER 테이블에서 'r001' 회원의 마일리지를 변경하시오

    UPDATE MEMBER
       SET MEM_MILEAGE = 30000
     WHERE MEM_ID='r001';

 SELECT * FROM V_MEM_MILE02;

사용예) 사원테이블에서 급여가 3000이하인 사원의 사원번호, 사원명, 부서명, 직책명을 WITH CHECK OPTION 뷰로 생성하시오

  CREATE OR REPLACE VIEW V_EMP_SAL
  AS
    SELECT A.EMPLOYEE_ID, A.EMP_NAME, B.DEPARTMENT_NAME, A.SALARY, C.JOB_TITLE
      FROM HR.EMP A, HR.DEPT B, HR.JOBS C
     WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
       AND A.JOB_ID = C.JOB_ID
       AND A.SALARY <3000
  WITH CHECK OPTION;
  

사용예) 뷰 V_EMP_SAL에서 사원번호 130번 사원의 급여를 3100으로 변경하시오 --컴파일에러 뷰의 SELECT절 위배. 3000이하여야함.

  UPDATE V_EMP_SAL
     SET SALARY =3100
   WHERE EMPLOYEE_ID = 130;
   
   -- 테이블에 적용하기
  UPDATE EMP
     SET SALARY =3100
   WHERE EMPLOYEE_ID = 130;
   
   SELECT * FROM V_EMP_SAL;
   ROLLBACK;
   COMMIT;