[Oracle] Chap10 VIEW

백엔드 과정/Oracle(SQL)

[Oracle] Chap10 VIEW

mim 2022. 1. 27. 22:32

VIEW

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

실질적인 데이터를 저장하고 있지 않음

테이블을 사용하는 것과 동일하게 사용할 수 있다.

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

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

CREATE VIEW

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

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

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

VIEW 생성 권한 부여

<시스템 계정으로 실행>

GRANT CREATE VIEW TO C##EMPLOYEE;

Data Dictionary

데이터 딕셔너리

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

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

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

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

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

3개의 딕셔너리 뷰로 나뉨

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

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

SELECT * FROM ALL_TABLES;

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

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

SELECT
       UV.*
  FROM USER_VIEWS UV;

뷰에 별칭 부여

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

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

UPDATE EMPLOYEE E
SET E.EMP_NAME = '재재'
WHERE E.EMP_ID = '205';

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

뷰 삭제

DROP VIEW V_RESULT_EMP;

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

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), 2, '여', 1, '남')
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.HIRE_DATE)
  FROM EMPLOYEE E
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);

VIEW를 통한 DML 구문

-- 직급 테이블의 직급명, 직급코드를 담은 뷰 생성

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

1. INSERT

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

2. UPDATE

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

3. DELETE

DELETE 
  FROM V_JOB V
 WHERE V.JOB_CODE = 'J8';

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;

뷰 테이블에 정의되지 않은 컬럼에 INSERT

-- "JOB_NAME": 부적합한 식별자

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

뷰 테이블에 정의되지 않은 컬럼을 UPDATE

-- "JOB_NAME": 부적합한 식별자

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

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

-- "JOB_NAME": 부적합한 식별자

DELETE
 FROM V_JOB2
WHERE JOB_CODE = 'J8';

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

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

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

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

뷰 테이블에 정의된 컬럼이라도 JOB_NAME만 INSERT시 베이스컬럼의 JOB_CODE 가 NOT NULL 조건이므로

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

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

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

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

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

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 작업은 가상 열에서 허용되지 않습니다.

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;

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);

JOIN으로 만들어진 VIEW에 INSERT

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

INSERT
  INTO V_JOINEMP
(
  EMP_ID
, EMP_NAME
, DEPT_TITLE
)
VALUES
(
  801
, '박지윤'
, '인사관리부'
);

UPDATE도 불가

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

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

DELETE는 가능

DELETE
  FROM V_JOINEMP V
 WHERE V.DEPT_TITLE = '인사관리부';

DISTINCT를 포함한 경우

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

INSERT

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

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

UPDATE

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

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

DELETE

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

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

그룹 함수나 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

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

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

UPDATE

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

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

DELETE

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

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

VIEW 옵션

1. OR REPLACE

기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고 존재하지 않으면 새로 생성하는 옵션

2. FORCE

서브쿼리에 사용 된 테이블이 존재하지 않아도 뷰 생성

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

3. /NOFORCE/

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

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

4. WITH CHECK OPTION

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

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

UPDATE

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

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

5. WITH READ ONLY

DML 수행이 불가능하게 한다.

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

DELETE

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

DELETE
  FROM V_DEPT;

저작자표시 비영리 변경금지