[Oracle(SQL)] VIEW

VIEW(뷰)

- SELECT 쿼리문을 저장한 객체이다.

- 실질적인 데이터를 저장하고 있지 않은 논리적인 테이블

- 테이블을 사용하는 것과 동일하게 사용 가능

- 1) 복잡한 SELECT문을 다시 작성할 필요가 없음

- 2) 민감한 데이터를 숨길 수 있음

CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리

 

사번, 이름, 직급명, 부서명, 근무지역을 조회하고, 그 결과를 V_RESULT_EMP라는 뷰를 생성해서 저장하세요

CREATE OR REPLACE VIEW V_RESULT_EMP
AS
SELECT E.EMP_ID
	 , E.EMP_NAME
     , J.JOB_NAME
     , D.DPET_TITLE
     , L.LOCAL_NAME
  FROM EMPLOYEE E
  LEFT JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  LEFT JOIN DEPARTMENT D ON(D.DEPT_CODE = D.DEPT_ID)
  LEFT JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE);

 

VIEW 생성 권한 부여

<시스템 계정에서 실행>

GRANT CREATE VIEW TO C##EMPLOYEE;

사번 205 직원의 정보가 조회됨

SELECT
       V.*
  FROM V_RESULT_EMP V
 WHERE V.EMP_ID = '205';

 

데이터 딕셔너리(Data Dictionary)

- 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블

- 사용자가 테이블을 생성하거나, 사용자를 변경하는 등의 작업을 할 때

  데이터베이스 서버에 의해 자동으로 갱신되는 테이블

- 사용자는 데이터 딕셔너리 내용을 직접 수정하거나 삭제할 수 없음

 

- 원본테이블을 커스터마이징해서 보여주는 원본 테이블의 가상테이블객체(VIEW)

 

- 3개의 딕셔너리 뷰로 나뉨

- 1) DBA_XXX : 데이터베이스 관리자만 접근이 가능한 객체 등의 정보 조회

- 2) ALL_XXX : 자신의 계정 + 권한을 부여받은 객체의 정보 조회

- 3) USER_XXX : 자신의 계정이 소유한 객체 등에 관한 정보 조회

 

뷰에 대한 정보를 확인하는 데이터 딕셔너리

SELECT
       UV.*
  FROM USER_VIEWS UV;

 

뷰에 대한 별칭 부여

CREATE OR REPLACE VIEW V_EMP
(
  사번
, 이름
, 부서
)
AS
SELECT E.EMP_ID
     , E.EMP_NAME
     , E.DEPT_CODE
  FROM EMPLOYEE E;

SELECT
	   V.*
  FROM V_EMP V;

 

베이스테이블의 정보가 변경되면 VIEW도 같이 변경된다.

(베이스테이블의 205사원의 이름 정중하에서 정중앙으로 변경)

UPDATE
       EMPLOYEE E
   SET E.EMP_NAME = '정중앙'
 WHERE E.EMP_ID = '205';

베이스테이블 205사원 정중앙으로 변경됨

 SELECT
       E.*
  FROM EMPLOYEE E
 WHERE E.EMP_ID = '205';

뷰 테이블에도 똑같이 변경된 것 확인

SELECT
       V.*
  FROM V_RESULT_EMP V
 WHERE V.EMP_ID = '205';

 

뷰 서브쿼리 안에 연산의 결과도 포함할 수 있다.

이 때는 반드시 별칭을 부여해서 생성해야 한다.

CREATE OR REPLACE VIEW V_EMP_JOB
(
  사번
, 이름
, 직급
, 성별
, 근무년수
)
AS
SELECT E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
     , DECODE(SUBSTR(E.EMP_NO, 8, 1), 1, '남', '여')
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.HIRE_DATE)
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);

 

뷰를 통한 DML 구문 수행

CREATE OR REPLACE VIEW V_JOB
AS
SELECT J.JOB_CODE
     , J.JOB_NAME
  FROM JOB J;

SELECT
       V.*
  FROM V_JOB V;

 

J8 인턴이라는 새로운행 입력

INSERT
  INTO V_JOB
(
  JOB_CODE
, JOB_NAME
)
VALUES
(
  'J8'
, '인턴'
);

 

베이스테이블, 뷰 테이블 모두 J8 인턴 값이 삽입된게 확인된다.

SELECT
       V.*
  FROM V_JOB V;

SELECT
       J.*
  FROM JOB J;

 

J8 인턴에서 알바로 변경

UPDATE
       V_JOB V
   SET V.JOB_NAME = '알바'
 WHERE V.JOB_CODE = 'J8';

 

베이스테이블, 뷰 테이블 모두 J8 인턴에서 알바로 변경됨

SELECT
       V.*
  FROM V_JOB V;

SELECT
       J.*
  FROM JOB J;

 

DML 명령어로 조작이 불가능한 경우
- 1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
- 2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정 된 경우
- 3. 산술표현식으로 정의된 경우
- 4. JOIN을 이용해 여러 테이블을 연결한 경우
- 5. DISTINCT 포함한 경우
- 6. 그룹함수나 GROUP BY 절을 포함한 경우

 

위의 경우들을 위해 뷰 테이블 생성

CREATE OR REPLACE VIEW V_JOB2
AS
SELECT J.JOB_CODE
  FROM JOB J;

SELECT
       V.*
  FROM V_JOB2 V;

 

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

 

- JOB_NAME 부적합한 식별자 오류

CREATE OR REPLACE VIEW V_JOB3
AS
SELECT J.JOB_NAME
  FROM JOB J;
 
SELECT 
       V.*
  FROM V_JOB3 V;

INSERT
  INTO V_JOB2
(
  JOB_CODE
, JOB_NAME
)
VALUES
(
  'J8'
, '인턴'
);

UPDATE
       V_JOB2 V
   SET V.JOB_NAME = '인턴'
 WHERE V.JOB_CODE = 'J7';

 

뷰 정의에 사용된 컬럼(JOB_CODE)만 사용하므로 삽입가능

INSERT
  INTO V_JOB2
(
  JOB_CODE
)
VALUES
(
  'J8'
);

 

뷰 정의에 사용된 컬럼만 사용하여 DELETE 가능

DELETE
  FROM V_JOB2
 WHERE JOB_CODE = 'J8';

 

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

CREATE OR REPLACE VIEW V_JOB3
AS
SELECT J.JOB_NAME
  FROM JOB J;
 
SELECT 
       V.*
  FROM V_JOB3 V;

 

- JOB_CODE 뷰 정의에 없어 부적합한 식별자 오류

INSERT
  INTO V_JOB3
(
  JOB_CODE
, JOB_NAME
)
VALUES
(
  'J8'
, '인턴'
);

 

- JOB_CODE에는 NULL이 삽입 될 수 없어 오류

INSERT
  INTO V_JOB3
(
  JOB_NAME
)
VALUES
(
  '인턴'
);

 

- 뷰에 정의 된 컬럼만을 사용한 UPDATE 수행 가능

UPDATE
       V_JOB3 V
   SET V.JOB_NAME = '인턴'
 WHERE V.JOB_NAME = '사원';

 

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

CREATE OR REPLACE VIEW EMP_SAL
AS
SELECT E.EMP_ID
     , E.EMP_NAME
     , E.SALARY
     , (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 연봉
  FROM EMPLOYEE E;

 

- 산술표현식으로 연산된 가상 컬럼 연봉에는 INSERT 불가

INSERT
  INTO EMP_SAL
(
  EMP_ID
, EMP_NAME
, SALARY
, 연봉
)
VALUES
(
  '800'
, '정진훈'
, 3000000
, 4000000
);

 

- 산술표현식으로 연산 된 가상 컬럼 연봉은 UPDATE 불가  

UPDATE
       EMP_SAL ES
   SET ES.연봉 = 80000000
 WHERE ES.EMP_ID = '200';

 

- DELETE의 조건으로는 사용 가능

DELETE
  FROM EMP_SAL ES
 WHERE ES.연봉 = 124800000;

 

4. JOIN을 이용해 여러 테이블을 연결한 경우

CREATE OR REPLACE VIEW V_JOINEMP
AS
SELECT E.EMP_ID 
     , E.EMP_NAME
     , D.DEPT_TITLE
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID);

 

- 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.

INSERT
  INTO V_JOINEMP
(
  EMP_ID
, EMP_NAME
, DEPT_TITLE
)
VALUES
(
  888
, '조민영'
, '인사관리부'
);

 

- 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

UPDATE
       V_JOINEMP V
   SET V.DEPT_TITLE = '인사관리부';

 

5. DISTINCT를 포함한 경우

CREATE OR REPLACE VIEW V_DT_EMP
AS
SELECT DISTINCT E.JOB_CODE
  FROM EMPLOYEE E;

 

- 뷰에 대한 데이터 조작이 부적합합니다

INSERT
  INTO V_DT_EMP
(
  JOB_CODE
)
VALUES
(
  'J9'
);

 

- 뷰에 대한 데이터 조작이 부적합합니다

UPDATE
       V_DT_EMP V
   SET V.JOB_CODE = 'J9'
 WHERE V.JOB_CODE = 'J7';

 

- 뷰에 대한 데이터 조작이 부적합합니다

DELETE
  FROM V_DT_EMP V
 WHERE V.JOB_CODE = 'J7';

 

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

CREATE OR REPLACE VIEW V_GROUPDEPT
AS
SELECT E.DEPT_CODE
     , SUM(E.SALARY) 합계
     , AVG(E.SALARY) 평균
  FROM EMPLOYEE E
 GROUP BY E.DEPT_CODE;

 

- 가상 열은 사용할 수 없습니다

INSERT
  INTO V_GROUPDEPT
(
  DEPT_CODE
, 합계
, 평균
)
VALUES
(
  'D0'
, 60000000
, 4000000
);

 

- 뷰에 대한 데이터 조작이 부적합합니다

UPDATE
       V_GROUPDEPT V
   SET V.DEPT_CODE = 'D10'
 WHERE V.DEPT_CODE = 'D1';

 

- 뷰에 대한 데이터 조작이 부적합합니다

DELETE
  FROM V_GROUPDEPT V
 WHERE V.DEPT_CODE = 'D1';

---

VIEW 옵션

- OR REPLACE : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고 존재하지 않으면 새로 생성하는 옵션
- FORCE 옵션 : 서브쿼리에 사용 된 테이블이 존재하지 않아도 뷰 생성

CREATE OR REPLACE FORCE VIEW V_EMP
AS
SELECT TCODE
     , TNAME
     , TCONTENTS
  FROM TT;

 

- NOFORCE 옵션 : 서브쿼리에 테이블이 존재해야만 뷰 생성함(기본값)

CREATE OR REPLACE /*NOFORCE*/ VIEW V_EMP2
AS
SELECT TCODE
     , TNAME
     , TCONTENTS
  FROM TT;

 

- WITH CHECK OPTION : 조건절에 사용 된 컬럼의 값을 수정하지 못하게 한다.

CREATE OR REPLACE VIEW V_EMP3
AS
SELECT E.* 
  FROM EMPLOYEE E
 WHERE MANAGER_ID  = '200'
  WITH CHECK OPTION;

 

- 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

UPDATE
       V_EMP3
   SET MANAGER_ID = '900'
 WHERE MANAGER_ID = '200';

 

- WITH READ ONLY : DML 수행이 불가능하게 한다.

CREATE OR REPLACE VIEW V_DEPT
AS
SELECT D.*
  FROM DEPARTMENT D
  WITH READ ONLY;

 

- 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

DELETE
  FROM V_DEPT;