[DBMS/Oracle] VIEW 객체

[DBMS/Oracle] VIEW 객체

VIEW 객체 

  - 뷰는 테이블과 유사한 객체
  - 기존 테이블이나 또 다른 뷰로부터 SELECT문에 의하여 생성
  - 사용
    . 필요한 자료가 여러 테이블에 분산 저장된 경우
    . 특정자료에 대한 접근을 제한하고자 할때

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

  . '(컬럼list)': 뷰에 적용될 걸럼명으로 생략되면 뷰 생성에 사용된 SELECT문의 SELECT절의
    별칭이 사용되고 별칭이 없는 경우 SELECT절의 컬럼명이 뷰의 컬럼명이 됨
  . 'WITH READ ONLY' : 읽기전용 뷰가되어 뷰를 대상으로 DML명령 사용금지 DML명령은 제한없이 사용 가능)
  . 'WITH CHECK OPTION' : 뷰 생성에 사용된 SELECT문의 WHERE 조건절을 위배하는 값으로 뷰의 값을 갱신할 수 없다
    (단, SELECT문의 WHERE 조건절을 만족할 때에는 변경 가능하며, 어느경우에나 원본 테이블은 제한없이 변경 가능하다)
  . 'WITH READ ONLY'과 'WITH CHECK OPTION'은 동시 사용할 수 없다.

 

사용예) 회원테이블에서 마일리지가 30000이상인 회원의 회원번호,회원명,마일리지로 뷰를 생성하시오.

-- MID,MNAME,MILE 
  CREATE OR REPLACE VIEW V_MILEAGE(MID,MNAME,MILE)
  AS
    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명,
           MEM_MILEAGE AS 마일리지
      FROM MEMBER
     WHERE MEM_MILEAGE>=3000
     
  SELECT * FROM V_MILEAGE;

-- 회원번호,회원명,마일리지
  CREATE OR REPLACE VIEW V_MILEAGE
  AS
    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명,
           MEM_MILEAGE AS 마일리지
      FROM MEMBER
     WHERE MEM_MILEAGE>=3000;

  SELECT * FROM V_MILEAGE;

-- MEM_ID,MEM_NAME,MEM_MILEAGE  
  CREATE OR REPLACE VIEW V_MILEAGE
  AS
    SELECT MEM_ID,MEM_NAME,MEM_MILEAGE
      FROM MEMBER
     WHERE MEM_MILEAGE>=3000;  
  
  SELECT * FROM V_MILEAGE;

 

**회원테이블의 '신용환'회원의 마일리지를 3500->1500으로 변경

  UPDATE MEMBER
     SET MEM_MILEAGE=1500
   WHERE MEM_NAME='신용환';
  
  SELECT MEM_ID,MEM_NAME,MEM_MILEAGE
    FROM MEMBER
   WHERE MEM_NAME='신용환';
  
  SELECT * FROM V_MILEAGE;
--ROLLBACK;
--COMMIT;

 

**뷰(V_MILEAGE)의 '오철희(k001)'회원의 마일리지를 3700->1000으로 변경 

  UPDATE V_MILEAGE
     SET MEM_MILEAGE = 1000
   WHERE MEM_ID='k001';
  
  SELECT * FROM V_MILEAGE;
  
  SELECT MEM_ID,MEM_NAME,MEM_MILEAGE
    FROM MEMBER
   WHERE MEM_ID='k001';   
   
--ROLLBACK;
--COMMIT;

 

(읽기 전용 뷰 생성)  

  CREATE OR REPLACE VIEW V_MILEAGE(MID,MNAME,MILE)
  AS
    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명,
           MEM_MILEAGE AS 마일리지
      FROM MEMBER
     WHERE MEM_MILEAGE>=3000
     WITH READ ONLY;

 

**뷰(V_MILEAGE)의 '오철희(k001)'회원의 마일리지를 3700->1000으로 변경  

  UPDATE V_MILEAGE
     SET MILE = 1000
   WHERE MID='k001';-- (X),뷰(V_MILEAGE)를 읽기 전용으로 생성했기 때문에 오류발생

 

**회원테이블에서의 '오철희(k001)'회원의 마일리지를 3700->1000으로 변경   

  UPDATE MEMBER
     SET MEM_MILEAGE=1000
   WHERE MEM_ID='k001';  
  
  SELECT * FROM V_MILEAGE;
  
--ROLLBACK;
--COMMIT;