04/21 VIEW

1. VIEW

 

- SELECT 문을 저장할 수 있는 객체이다. (논리적인 가상 테이블)
- 데이터를 저장하고 있지 않으며 테이블에 대한 SQL만 저장되오 있으며 
- VIEW 접근할 때 SQL을 수행하면서 결괏값을 가져온다.

- 가상 테이블로 실제 데이터가 담겨있는 것은 아니다.

CREATE [OR REPLACE] VIEW 뷰명
AS 서브쿼리;

 

• 예시

CREATE OR REPLACE VIEW V_EMP
AS 
SELECT EMP_ID, EMP_NAME,DEPT_TITLE ,SALARY , NATIONAL_NAME
FROM EMP E, DEPT D, LOCATION L, NATIONAL N
WHERE E.DEPT_CODE = D.DEPT_ID(+)
AND D.LOCATION_ID = L.LOCAL_CODE(+)
AND L.NATIONAL_CODE = N.NATIONAL_CODE(+);

 

Oracle Sql Developer 상에 있는 '뷰'에서 확인이 가능하다.

 

VIEW를 생성할 때 별칭을 달았을 경우 그 별칭으로 조회해야 한다.

CREATE OR REPLACE VIEW V_EMP
AS 
SELECT EMP_ID 사번, EMP_NAME 이름,DEPT_TITLE 부서명,SALARY 급여, NATIONAL_NAME 국가명
FROM EMP E, DEPT D, LOCATION L, NATIONAL N
WHERE E.DEPT_CODE = D.DEPT_ID(+)
AND D.LOCATION_ID = L.LOCAL_CODE(+)
AND L.NATIONAL_CODE = N.NATIONAL_CODE(+); 

SELECT * FROM V_EMP WHERE 근무국가명 = '한국' -- 별칭으로 조회

 

 

위와 같은 방법으로 칼럼 옆에 별칭을 붙이는 방법도 있지만 쌍 따옴표(" ")를 이용하여 뷰에 별칭을 붙이는 또 다른 방법이 있다.

CREATE VIEW V_EMP_02("사번","사원명","성별","근무년수")
AS
SELECT EMP_ID , EMP_NAME , 
DECODE(SUBSTR(EMP_NO,8,1),1,'남자',2,'여자',3,'남자',4,'여자') ,
TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(HIRE_DATE,'YYYY') 년수
FROM EMP;

 

 

2. VIEW를 이용해서 DML(INSERT, UPDATE, DELETE) 사용

뷰를 통해 데이터를 변경하게 되면 실제 데이터가 담긴 기본 테이블에도 적용된다.

 

 

먼저 테이블을 복사하여 뷰를 만든다.

CREATE VIEW V_JOB
AS SELECT * FROM JOB;

 

• VIEW를 통해 테이블에 INSERT

INSERT INTO V_JOB VALUES('J8','알바');

 

• VIEW를 통해 테이블에 UPDATE

UPDATE V_JOB SET JOB_NAME = '인턴' WHERE JOB_CODE = 'J8';

 

• VIEW를 통해 테이블에 있는 데이터 DELETE

DELETE V_JOB WHERE JOB_CODE = 'J8';

 

 

3. DML 구문으로 VIEW 조작이 불가능한 경우

 

3.1  뷰 정의에 포함되지 않는 칼럼을 조작하는 경우 오류

• 뷰 생성

CREATE OR REPLACE VIEW V_JOB
AS SELECT JOB_CODE FROM JOB;

 

• 조작

SELECT * FROM V_JOB;
INSERT INTO V_JOB VALUES('J8'); -- 오류없음

INSERT INTO V_JOB VALUES('J8','인턴'); -- 오류
UPDATE V_JOB SET JOB_NAME = '알바' WHERE JOB_CODE = 'J8'; -- 오류

 

3.2 뷰에 포함되지 않는 컬럼 중 기본 테이블에 NOT NULL 제약조건이 지정된 경우

• 뷰 생성

CREATE OR REPLACE VIEW V_JOB
AS SELECT JOB_NAME FROM JOB;

 

• 조작

뷰는 가상의 테이블이므로 실제로 INSERT는 원래의 테이블에 적용된다. JOB테이블의 JOB_CODE 컬럼은 체크제약조건과 NOT NULL로 설정되어 있기 때문에 오류가 나게 된다.

 

밑의 코드는 INSERT INTO JOB VALUES(NULL, '알바')와 같다고 보면 된다.

INSERT INTO V_JOB VALUES('알바'); -- 오류

-- INSERT INTO JOB VALUES(NULL,'알바')와 같은 쿼리라고 생각하면된다.

 

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

 

• 뷰 생성

CREATE OR REPLACE VIEW V_EMP_SAL
AS 
SELECT EMP_ID , EMP_NAME , SALARY , SALARY * 12 연봉 -- 별칭을 지정하지 않으면 오류 (ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다)
FROM EMP;

 

• 조작

원래 EMP 테이블엔 '연봉'이란 컬럼은 존재하지 않아서 아래의 코드는 오류가 난다.

UPDATE V_EMP_SAL SET 연봉 = 8000000 WHERE EMP_ID = 200;

 

 

3.4 또한 DISTINCT가 포함된 경우와 JOIN을 이용해 여러 테이블을 연결한 경우  VIEW를 생성할 순 있으나  DML문을 이용해 데이터를 조회할 수 있다.

 

 

 

4. VIEW의 구조

VIEW는 여러 옵션과 함께 사용할 수 있다.

 

- OR REPLACE : 기존에 동일한 뷰가 있으면 덮어쓰고, 없으면 신규로 생성
- FORCE: 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰가 생성된다,

- NOFORCE: 서브쿼리에 기술된 테이블이 존재해야만 뷰가 생성 된다. (default)
- WITH CHEK OPTION: 서브쿼리에 기술된 조건에 부합하지 않는 값으로 수정하는 경우 오류를 발생시킨다.
- WITH READ ONLY : 뷰에 대한 조회만 가능하다. (DML 사용불가)

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY];

 

• OR REPLACE

기존에 동일한 뷰가 있으면 덮어쓰고, 없으면 신규로 생성한다.

CREATE OR REPLACE VIEW V_EMP_01
AS SELECT EMP_NAME, SALARY, HIRE_DATE FROM EMP;
SELECT * FROM V_EMP_01;

 

•  FORCE/NOFORCE

기본값이 NOFORCE여서 에러가 뜬다.

CREATE VIEW V_EMP_02
AS SELECT TCODE, TNAME, TCONTEXT FROM TT; -- 존재하지 않는 테이블(ORA-00942: 테이블 또는 뷰가 존재하지 않습니다)

 

FORCE: 테이블이 없어도 뷰를 생성

CREATE  FORCE VIEW V_EMP_02
AS SELECT TCODE, TNAME, TCONTENT FROM TT;

SELECT * FROM V_EMP_02; -- 조회는 되지 않는다.

 

• WITH CHECK OPTION

EMP테이블에서 급여가 300만원 이상인 사원들을 조회하여 VIEW로 만들고 WITH CHECK OPTION을 주었다.

CREATE VIEW V_EMP_03
AS
SELECT * FROM EMP WHERE SALARY >= 3000000
WITH CHECK OPTION;

 

이 때 뷰에 INSERT를 할 때 서브쿼리의 조건에 따라 삽입여부를 결정한다.

-- 200만원일 땐 오류 : 체크제약조건에 의해 거부(변경불가)
UPDATE V_EMP_03 SET SALARY = 2000000 WHERE EMP_ID = 200;
-- 300이상일 땐 업데이트
UPDATE V_EMP_03 SET SALARY = 4000000 WHERE EMP_ID = 200;

 

• WITH READ ONLY (읽기 전용) : 조회만 가능

CREATE VIEW V_DEPT_01
AS SELECT * FROM DEPT 
WITH READ ONLY;


INSERT INTO V_DEPT_01 VALUES('D0','해외영업4부','L5'); -- 읽기 전용인 경우 DML 작업 불가능