Oracle - View

VIEW

VIEW란 SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블이다.

실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만

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

VIEW를 사용하는 목적은 복잡한 SELECT 쿼리문을 단순화하여 쉽게 사용하기위함이고

테이블의 진짜 모습을 감출 수 있어 보안상 유리하다.

 

하지만 단점으론 ALTER 구문을 사용할 수 없다. 왜냐하면 VIEW는 가상 테이블이므로 수정이 불가능하기 때문이다.

VIEW를 이용한 DML을 사용할 수는 있으나 제약이 따른다.

SINGLE TABLE VIEW에서만 부분적으로 DML 사용이 가능하다.

 

[ VIEW 예시 ]

 

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

 

실행하면 이러한 결과가 조회된다.

 

별칭을 사용한 예시

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

 

별칭으로 나타낸 결과 조회는 이러하다.

데이터 삽입 예시

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

 

데이터를 삽입 하기 전 

 

INSERT INTO V_JOB VALUES(‘J8’, ‘인턴’);

데이터를 삽입한 후

 

뷰 테이블이아닌 원본테이블에도 값이 삽입된다.

- 생성된 뷰를 가지고 DML구문(INSERT, UPDATE, DELETE) 사용이 가능하다.

- 생성된 뷰에 요청한 DML구문이 베이스 테이블도 변경된다.

 

 

 

 

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

 

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

 

[ 예시 ]

JOB_CODE 조회

 

CREATE OR REPLACE VIEW V_JOB2
AS 
SELECT JOB_CODE
FROM JOB;

INSERT INTO V_JOB2 VALUES(‘J8’, ‘인턴’); -- 포함되지 않은 컬럼

J8이 포함되지않아 INSERT를할 수 없다.

 

 

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

CREATE OR REPLACE VIEW V_JOB3
AS 
SELECT JOB_NAME
FROM JOB;

INSERT INTO V_JOB3 VALUES(‘인턴’); -- 포함되지 않음

'JOB_NAME'이 NOT NULL 제약조건이다.

- 뷰에 포함되지 않은 NOT NULL제약조건이 있는 컬럼이 존재하면 INSERT시 에러 발생

- 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(800, ‘정진훈’, 3000000, 36000000);

산술식으로 정의되있어서 안된다.

 

 

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

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

INSERT INTO V_GROUPDEPT VALUES(‘D10’, 6000000, 4000000);
DELETE FROM V_GROUPDEPT WHERE DEPT_CODE = ‘D1’;

- 그룹함수 또는 GROUP BY를 사용한 경우 INSERT/UPDATE/DELETE 시 에러가 발생한다.

 

 

5. DISTINCT를 포함한 경우

CREATE OR REPLACE VIEW V_DT_EMP
AS 
SELECT DISTINCT JOB_CODE -- DISTINCT 포함
FROM EMPLOYEE;

INSERT INTO V_DT_EMP VALUES(‘J9’);
DELETE FROM V_DT_EMP WHERE JOB_CODE = ‘J1’;

* DISTINCT를 사용한 경우 INSERT/UPDATE/DELETE 시 에러가 발생한다.

 

 

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

CREATE OR REPLACE VIEW V_JOINEMP
AS 
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID); -- JOIN 포함

INSERT INTO V_JOINEMP VALUES(888, ‘조세오’, ‘인사관리부’);

- 뷰 정의 시 JOIN을 사용한 경우 INSERT/UPDATE 시 에러 발생

- 하지만 DELETE는 가능

 

 

VIEW 옵션

VIEW를 설정하는 옵션들 OR REPLACE, FORCE/NOFORCE, WITH CHECK OPTION, WITH READ ONLY 가 있다.

 

[ 작성법 ]

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름[(별칭 [, 별칭 ...])]
AS 
서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY]

 

1. OR REPLACE 옵션

생성한 뷰가 존재하면 뷰를 갱신한다.

 

2. FORCE/NOFORCE 옵션

FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성한다.

NOFORCE 옵션이 기본 값으로 지정되어 있다.

 

3. WITH CHECK OPTION 옵션

옵션을 설정한 컬럼의 값을 수정 불가능하게 함(삭제는 가능)

 

4. WITH READ ONLY 옵션

뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함