09. VIEW

코딩 펭귄 2022. 8. 13. 15:16

📅 2021.09.27

VIEW

SELECT 쿼리의 실행 결과(Result Set)를 화면에 저장한 논리적 가상 테이블이다. 실제 테이블과는 자르게 실질적 데이터를 저장하고 있지는 않지만, 테이블과 동일하게 사용 가능하다.
VIEW 생성문
CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리
VIEW를 생성하기 위해서는 관리가 계정(SYSTEM 계정)에 접속하여 다음과 같이 권한을 먼저 부여해 주어야 한다.
GRANT CREATE VIEW TO 유저이름;

VIEW를 생성할 때 서브쿼리의 SELECT 절에 함수가 사용된 경우 반드시 별칭을 지정하여야 생성 가능하다.

-- SELECT에서 별칭 지정
CREATE OR REPLACE VIEW V_EMP_JOB AS
SELECT EMP_ID 사번, EMP_NAME "사원 명", JOB_NAME "직급 명", 
       DECODE(SUBSTR(EMP_NO, INSTR(EMP_NO, '-') + 1, 1), 1, '남', '여') 성별,
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수 
FROM EMPLOYEE 
     JOIN JOB USING(JOB_CODE);

-- CREATE에서 별칭 지정
CREATE OR REPLACE VIEW V_EMP_JOB(사번, "사원 명", 직급, 성별, 근무년수) AS 
SELECT EMP_ID, EMP_NAME, JOB_NAME, 
       DECODE(SUBSTR(EMP_NO, INSTR(EMP_NO, '-') + 1, 1), 1, '남', '여'),
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)  
FROM EMPLOYEE 
     JOIN JOB USING(JOB_CODE);

VIEW는 가상 테이블이기 때문에 실제 존재하는 테이블에 값을 저장한 후 불러오는 것이 아니라 서브쿼리를 컬럼에 저장해 놓은 후 VIEW를 SELECT할 때마다 해당 서브쿼리를 다시 실행시키는 것이다. 따라서 본 테이블과 연결되어 있기 때문에 VIEW에서 DML 명령어인 INSERT, UPDATE, DELETE를 사용하는 경우 본 테이블에도 영향을 미친다.
사용자 정의 뷰 확인 데이터 딕셔너리
SELECT * FROM USER_VIEWS;
DML 명령어로 VIEW 조작이 불가능한 경우
1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
```
CREATE OR REPLACE VIEW V_JOB2
AS SELECT JOB_CODE FROM JOB;

INSERT INTO V_JOB2 VALUES('J8', '인턴');
-- SQL 오류 : 값의 수가 너무 많습니다
```
  V_JOB2에는 JOB_CODE 컬럼만 존재하기 때문에 JOB 테이블에는 JOB_CODE, JOB_NAME 두 개의 컬럼이 존재하더라도 값을 추가할 수 없다.
  뷰 정의에 포함되지 않은 컬럼을 INSERT/UPDATE하는 경우 에러가 발생하며, 뷰 정의에 포함되지 않은 조건으로 DELETE를 하는 경우에도 에러가 발생한다.
2. 뷰에 포함되지 않은 컬럼에 NOT NULL 제약 조건이 지정된 경우
```
CREATE OR REPLACE VIEW V_JOB3
AS SELECT JOB_NAME FROM JOB;

INSERT INTO V_JOB3 VALUES('인턴');
-- SQL 오류 : NULL을 ("KH"."JOB"."JOB_CODE") 안에 삽입할 수 없습니다
```
  V_JOB3에는 JOB_NAME 컬럼만 존재하기 때문에 값을 삽입하는 경우 뷰에 존재하지 않는 컬럼에는 NULL이 들어가게 된다. 따라서 뷰에 존재하지 않는 컬럼에 NOT NULL 제약 조건이 설정되어 있다면 값을 추가할 수 없다.
  INSERT 혹은 NULL 값으로 UPDATE 시 에러가 발생하나, DELETE의 경우 제약 조건을 위반하는 행위가 아니기 때문에 사용 가능하다.
3. 산술 표현식으로 정의된 경우
```
CREATE OR REPLACE VIEW EMP_SAL
AS
SELECT EMP_ID, EMP_NAME, SALARY, (SALARY + (SALARY * NVL(BONUS, 0))) * 12 연봉
FROM EMPLOYEE;

INSERT INTO EMP_SAL VALUES(600, '김개발', 3000000, 36000000);
-- SQL 오류 : 가상 열은 사용할 수 없습니다
```
  연봉은 SALARY와 BONUS를 사용하여 산출된 계산값이기 때문에 지정된 숫자로 추가하는 경우 이를 인식하지 못한다.
  산술 표현식으로 정의된 컬럼이 존재하는 경우 INSERT/UPDATE를 이용하여 특정 값을 추가/수정하면 에러가 발생한다. DELETE의 경우는 가능하다.
4. 그룹함수 또는 GROUP BY절을 포함한 경우
```
CREATE OR REPLACE VIEW V_GROUPDEPT
AS
SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE;

INSERT INTO V_GROUPDEPT VALUES('D10', 600, 400);
-- SQL 오류: 가상 열은 사용할 수 없습니다
```
  평균과 합계는 각 데이터를 이용하여 계산된 값인데, 이를 무시하고 특정 값으로 데이터를 추가/수정하려고 하면 에러가 발생한다.
  데이터를 삭제하는 경우에도 해당 데이터 삭제 시 해당하는 부분을 모두 삭제하여야 하기 때문에 에러가 발생한다.
5. DISTINCT를 포함한 경우
```
CREATE OR REPLACE VIEW V_DT_EMP
AS 
SELECT DISTINCT JOB_CODE
FROM EMPLOYEE;

INSERT INTO V_DT_EMP VALUES('J9'); 
-- SQL 오류: 뷰에 대한 데이터 조작이 부적합합니다
```
  DISTINCT를 이용하는 경우 전체를 다 가지고 오는 것이 아니라 중복을 제거하여 일부 데이터만 가지고 온 것이기 때문에 어떤 데이터를 삽입/수정/삭제할 것인지 알 수 없다. 따라서 INSERT/UPDATE/DELETE 시 에러가 발생한다.
6. JOIN을 이용해 여러 테이블을 연결한 경우
```
CREATE OR REPLACE VIEW V_JOINEMP
AS
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
     LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

INSERT INTO V_JOINEMP VALUES(700, '김개발', '기술개발부');
-- SQL 오류: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
```
  여러 테이블을 한 번에 수정/삽입할 수 없기 때문에 JOIN을 이용한 경우 INSERT/UPDATE가 불가능하지만, DELETE는 사용 가능하다.

VIEW 옵션

OR REPLACE: 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고, 존재하지 않는 경우 새롭게 생성한다.
FORCE/NOFORCENOFORCE: 서브쿼리에 사용된 테이블이 존재해야만 뷰를 생성하며, 기본 값이다.
FORCE: 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰를 생성한다. FORCE 사용 시 '컴파일 오류와 함께 뷰가 생성되었습니다'라는 문구가 출력된다. 아직 테이블이 만들어져 있지 않지만 해당 뷰를 사용하려는 경우 미리 생성할 때 사용한다.
WITH CHECK OPTION: 옵션을 설정한 컬럼을 수정 불가능하게 한다.
WITH READ ONLY: 해당 뷰에 대항 조회만 가능하고 삽입, 수정, 삭제는 불가능하게 한다.
사용 순서
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름[(alias [, alias]...)]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];

저작자표시 비영리 동일조건 (새창열림)