[SQL] VIEW – 구조, 특징, 조작 불가능한 경우

VIEW

o  SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블 실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만 사용자는 테이블을 사용하는 것과 동일하게 사용 가능

 

o  가상의 테이블, SELECT문을 이용해서 실제 테이블에서 데이터를 가져와 사용한다.

o  VIEW도 하나의 ORACLE OBJECT이기 때문에 DDL로 생성, 수정, 삭제가 가능하다.

o  VIEW에 대한 생성권한이 있어야 생성할 수 있다.

사용 형식 : GRANT CREATE VIEW TO 계정명;

CREATE VIEW VIEW이름 AS SELECT문 작성

CREATE VIEW V_EMPALL AS SELECT *     FROM EMPLOYEE     JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID     JOIN JOB USING(JOB_CODE);   SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, SALARY FROM V_EMPALL;  --생성한 뷰를 통해 ROW값 조회

 

o  자주 사용되는 긴 쿼리문을 뷰로 등록하여 간단하게 사용할 수 있다.

CREATE VIEW V_EMP_AVG AS SELECT DEPT_CODE, FLOOR(AVG(SALARY)) AS 평균     FROM EMPLOYEE     GROUP BY DEPT_CODE     UNION     SELECT JOB_CODE, FLOOR(AVG(SALARY)) AS 평균     FROM EMPLOYEE     GROUP BY JOB_CODE     UNION     SELECT '총평균', FLOOR(AVG(SALARY)) AS 평균     FROM EMPLOYEE;   SELECT * FROM V_EMP_AVG;

 

o  VIEW테이블도 권한을 부여하여 다른 계정에서 이용할 수 있게 할 수 있다.

o  VIEW에 권한을 부여받은 사용자는 VIEW만 사용할 수 있을 뿐 실제 테이블은 조회할 수 없다.

GRANT SELECT ON H.V_EMP_AVG TO QWER; --DBA계정   SELECT * FROM H.V_EMP_AVG; --QWER계정 SELECT * FROM H.EMPLOYEE;  -- QWER계정 실제 테이블은 조회 불가능

 

o  DROP명령어를 통해 VIEW를 삭제한다.

DROP VIEW V_EMPALL;

  

VIEW 구조

o  뷰 정의 시 사용한 쿼리 문장이 TEXT컬럼에 저장되어 있으며 뷰가 실행할 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서 결과를 보여주는 구조

o  그렇기에 실제 테이블 값이 변경되면 뷰의 값도 같이 변경된다.

SELECT * FROM USER_VIEWS;

 

VIEW 특징

o  가상컬럼을 VIEW로 이용할 수 있다. 연산에 의해 계산된 컬럼 등. 단, 무조건 별칭부여를 해야한다.

CREATE VIEW V_EMPSAL AS SELECT EMP_ID,EMP_NAME,(SALARY+SALARY*NVL(BONUS,0))*12 AS SAL FROM EMPLOYEE;

 

o  VIEW의 데이터를 수정, 삽입, 삭제 즉, DML은 가능 하나 제약조건이 있다. -> 가상 컬럼은 불가능함

UPDATE V_EMPSAL SET SAL=5000000 WHERE EMP_NAME='방명수'; --가상 컬럼(SAL)은 수정이 불가하기에 에러 UPDATE V_EMPSAL SET EMP_NAME='CHAN' WHERE EMP_NAME='전지연';

  

DML 명령어로 VIEW 조작이 불가능한 경우

1.  뷰 정의에 포함되지 않은 컬럼을 조작하는 경우

SELECT * FROM V_EMPSAL; UPDATE V_EMP_SAL SET EMAIL='KKK@KKK.COM' WHERE EMP_NAME='CHAN'; --에러, 뷰에 지정한 컬럼만 수정 가능하다. (EMAIL 컬럼은 VIEW에 미포함)

 2.  뷰에 포함되지 않은 컬럼 중에 베이스가 되는 컬럼의 NOT NULL 제약조건이 지정된 경우

CREATE VIEW V_EMP AS SELECT EMP_ID, EMP_NO, EMP_NAME, EMAIL, PHONE, JOB_CODE, SAL_LEVEL FROM EMPLOYEE; SELECT * FROM V_EMP; INSERT INTO V_EMP VALUES('666','660611-1266666','육육','6@66.COM','01012345678','J2','S4'); -- 작성한 컬럼 항목외에 다른 컬럼들은 모두 NULL로 채워지기에 NOT NULL제약 조건이 있으면 생성이 불가능하다.

3.  산술 표현식으로 정의된 경우

4.  그룹함수나 GROUP BY절을 포함한 경우

5.  DISTINCT를 포함한 경우

6.  JOIN을 이용해 여러 테이블을 연결한 경우 * 예외적으로 삽입되는 경우도 있다.