SQL 7일차(1)

<VIEW 뷰> 
   

    자주 사용하는 SELECT 문을 하나의 가상 테이블처럼 저장해 놓은 객체.
    실제 데이터를 저장하지 않고, 정의된 SELECT 문을 실행해서 데이터를 조회. 
    복잡한 조인쿼리를 매번 작성하지 않고 간단하게 재사용 가능.

 

한국에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무 국가 조회

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL USING (NATIONAL_CODE)
WHERE NATIONAL_NAME = '한국';

한국말고 러시아, 일본, 중국 등 국가 하나만 변경하는데 JOIN을 매번 사용하기 때문에 성능 저하가 생기고 문장이 길어진다.

 

1. VIEW 생성방법

     [표현법]
          CREATE VIEW 뷰명
          AS 서브쿼리

CREATE VIEW VW_EMP
AS (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
        FROM EMPLOYEE
        JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
        JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
        JOIN NATIONAL USING (NATIONAL_CODE));

 

뷰 호출

SELECT *
FROM VW_EMP;

 

--한국에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무 국가 조회
SELECT *
FROM VW_EMP
WHERE NATIONAL_NAME = '한국';

--러시아에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무 국가 조회
SELECT *
FROM VW_EMP
WHERE NATIONAL_NAME = '러시아';

단순히 WHERE 조건에서 한국, 러시아만 적으면 된다.

 

CREATE OR REPLACE VIEW 뷰명

뷰가 없을 때는 생성하고 이미 존재한다면 수정할 수 있음.

CREATE OR REPLACE VIEW VW_EMP
AS (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME, BONUS
        FROM EMPLOYEE
        JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
        JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
        JOIN NATIONAL USING (NATIONAL_CODE));

 

뷰 컬럼에 연산식, 함수식이 있는 경우 무조건 별칭을 지정해야 함

CREATE OR REPLACE VIEW VW_EMP_JOB
AS (SELECT EMP_ID, EMP_NAME, JOB_NAME,
            DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') AS GENDER,
            EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS SERVICEYEAR
        FROM EMPLOYEE
        JOIN JOB USING(JOB_CODE));

 

생성된 뷰를 통해서 DML(INSERT, UPDATE, DELETE) 사용 가능 하지만,

뷰에 INSERT, UPDATE를 하면 안되는 상황
    
    1) 뷰에 없는 컬럼을 조작할 때
    2) 뷰에 정의되어 있지 않은 컬럼 중에 NOT NULL 제약조건이 있는 경우
    3) 산술/함수식을 사용한 경우
    4) 그룹함수나 GROUP BY절을 포함한 경우
    5) DISTINCT 구문이 포함된 경우
    6) JOIN을 이용해서 다중 테이블을 지정한 경우
    
    -> 대부분 뷰는 조회를 목적으로 사용하기 때문에, 사용하지 말고 테이블을 변경하자.

 

VIEW 생성 옵션

   [표현법]
        CREATE [OR REPLACE] [FORCE | NOFROCE] VIEW 뷰명
        AS 서브쿼리
        [WITH CHECK OPTION]
        [WITH READ ONLY]
        
        FORCE | NOFORCE
            - FORCE : 뷰 정의시 참조테이블이 없어도 뷰를 생성가능하게 함.
            - NOFORCE : 서브쿼리에 기술된 테이블이 존재할 때만 뷰 생성가능.
            
        WITH CHECK OPTION : 뷰의 WHERE 조건을 강제하여 조건에 맞는 데이터만 삽입/수정 가능하게 함

 

서브쿼리에 기술된 테이블이 존재하지 않아도 뷰를 우선 생성

CREATE OR REPLACE FORCE VIEW VW_EMP
AS (SELECT TCODE, TNAME, TCONTENT
        FROM TT);

 

CREATE TABLE TT(
    TCODE NUMBER,
    TNAME VARCHAR2(20),
    TCONTENT VARCHAR2(30)
);

뷰를 먼저 만들고 없던 TT 테이블을 만들면, 뷰가 정상적으로 실행이 된다.

 

VIEW CHECK OPTION

CREATE OR REPLACE VIEW VW_EMP
AS (SELECT *
        FROM EMPLOYEE
        WHERE SALARY >= 3000000)
WITH CHECK OPTION;

뷰를 다시 생성하고 CHECK OPTION을 추가한다.

 

UPDATE VW_EMP
SET SALARY = 2000000
WHERE EMP_ID = 200;

뷰의 조건 안에서 동작하도록 강제함.

 

WITH READ ONLY : 뷰는 읽기 전용으로 사용

CREATE OR REPLACE VIEW VW_EMP
AS (SELECT *
        FROM EMPLOYEE
        WHERE BONUS IS NOT NULL)
WITH READ ONLY;

 

DELETE 
FROM VW_EMP
WHERE EMP_ID = 200;

뷰에서 사원번호가 200인 사원의 ROW를 삭제하려고 하면 오류가 발생한다.