VIEW

SELECT문의 결과인 RESULT SET을 하나의 테이블처럼 활용하게 하는 것을 말한다. 실제 테이블과 다르게 데이터를 저장하고 있지 않기 때문에 보여지는 용도로 사용한다. JOIN한 SELECT문을 자주 사용해야 할 때 VIEW로 만들어 놓고 해당 VIEW만 조회해서 사용할 수 있다.

CREATE [옵션] VIEW 뷰명칭 AS SELECT문; 형태로 작성하며 VIEW도 하나의 객체이기 때문에 CREATE를 사용해서 생성해준다.

CREATE VIEW V_EMP
AS SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID;
-->해당 계정에 VIEW 생성 권한이 없으면 권한 불충분 오류가 발생한다.

--VEIW 생성 권한을 부여
--SYSTEM||SYS AS SYSDBA(최고관리자 계정) 계정으로 권한을 부여한다
GRANT CREATE VIEW TO BS; //BS계정에 권한 부여
CREATE VIEW V_EMP
AS SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID;

--생성된 VIEW TABLE 이용
SELECT * FROM V_EMP; -->JOIN한 SELECT문이 조회

--부서별 직책별 급여의 평균을 구하는 SELECT문
SELECT DEPT_CODE, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE
UNION
SELECT JOB_CODE, AVG(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE;
-->자주 사용해야되면 매번 위 문장을 작성해줘야한다.

--이 때 VIEW TABLE을 만들어서 이름으로 불러올 수 있다.
CREATE VIEW V_AVG_DEPTJOB
AS SELECT DEPT_CODE, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE)
UNION
SELECT JOB_CODE, AVG(SALARY) FROM EMPLOYEE GROUP BY ROLLUP(JOB_CODE);
-->VIEW TABLE을 만들었을 때 컬럼이 되는 것은 별칭을 부여해줘야한다.

--급여가 300만원 이상이고 부서코드가 존재하는 사원의 정보
SELECT * FROM V_AVG_DEPTJOB
WHERE AVG_SALARY>=3000000 AND DEPT_CODE IS NOT NULL;
--VIEW TABLE 조회
SELECT * FROM USER_VIEWS;

VIEW 특징

DML구문은 사용이 가능하긴 하지만 실제 테이블의 데이터가 수정되기 때문에 웬만하면 사용하지 않는 게 좋다.

1. 가상 컬럼은 수정이 불가능하다. 존재하지 않는 컬럼은 수정 자체가 안 되기 때문이다.
   →DEPT_CODE의 경우 수정이 되지만 AVG_SALARY같은 경우 수정이 불가
2. 단일 테이블로 만들어진 VIEW는 데이터 삽입이 가능하다. VIEW에서 값을 넣은 것 이외의 컬럼에는 NULL값을 삽입한다. 때문에 NOT NULL 제약 조건이 있으면 불가능하다. 새로운 컬럼을 추가하려고 했을 때 NULL 값이 존재하는 ROW들이 생기면 오류가 났던 것과 같은 원리다. DEFAULT 값을 지정하면 사용 가능하다.
3. JOIN, UNION 등으로 연결된 VIEW는 입력이 불가능하다.
4. DELETE문 사용이 가능하다. 실제 테이블에 존재하는 값을 찾아와서 삭제할 수 있다. JOIN, 집합연산자를 쓴 VIEW에서도 실제 데이터를 찾아서 삭제할 수 있다.

--DML구문 사용
UPDATE V_EMP SET EMP_NAME='LEEJI' WHERE EMP_NAME='월드컵';
SELECT * FROM EMPLOYEE; -->뷰에 연결된 테이블 조회
-->V_EMP 뷰를 수정했지만 해당 뷰에 연결된 실제 데이터가 수정된다.

--가상 커럼 수정 불가
UPDATE V_AVG_DEPTJOB SET AVG_SALARY=1000000;
-->⚠️ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다

--VIEW에 데이터 삽입
CREATE VIEW V_EMPTEST
AS SELECT EMP_ID, EMP_NO, EMP_NAME, EMAIL, PHONE, JOB_CODE, SAL_LEVEL FROM EMPLOYEE;
--SELECT DEPT_CODE FROM V_EMPTEST;
-->해당 컬럼이 없기 때문에 조회할 수 없다.
INSERT INTO V_EMPTEST 
		VALUES('997','981011-1234123','홍길동','HONG@HONG.COM','12341234','J1','S1');
-->해당 VIEW에 없는 컬럼은 NULLABLE이기 때문에 오류 없이 데이터 저장이 가능하다.
SELECT * FROM EMPLOYEE;

--JOIN, 집합연산자 사용한 VIEW에 데이터 삽입
INSERT INTO V_EMP VALUES('996','홍길동','980110-1234567','hong@HONG.COM','12345','D5'
					,'J1','S1',100,0.2,206,SYSDATE, NULL,'N','D0','불가','L3');
-->V_EMP는 UNION을 사용한 VIEW. 데이터 삽입 불가

VIEW 옵션

OR REPLACE

객체의 명칭은 중복이 불가능하기 때문에 중복되는 VIEW 이름이 있으면 덮어쓰기 해주는 옵션이다. 겹치면 지웠다가 다시 만들어야 하는 번거로움 없이 덮어쓰기 해서 같은 명칭의 VIEW를 생성할 수 있다. 해당 옵션 사용 시, 필요한 VIEW가 지워지지 않도록 주의해야 한다.

CREATE OR REPLACE VIEW V_EMP
AS SELECT * FROM EMPLOYEE;
-->OR REPLACE없이 생성 시, ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

FORCE/NOFORCE

실제 테이블이 존재하지 않아도 VIEW 생성할 수 있게 해주는 옵션이다. VIEW를 미리 생성해놓고 어떤 용도로 쓰지 않을지 정하지 않았을 때 FORCE 옵션을 설정해서 테이블이 없는 VIEW를 생성할 수 있다. VIEW는 생성되었어도 VIEW로 불러올 테이블이 존재하지 않기 때문에 VIEW를 조회하는 것은 불가능하다.

CREATE FORCE VIEW VEIW_TEST
AS SELECT * FROM TEST_TABLE;

SELECT * FROM VIEW_TEST; -->VIEW는 생성했지만 해당 테이블이 없어서 조회 불가

CREATE TABLE TEST_TABLE(
    TTNO NUMBER,
    TTNAME VARCHAR2(200)
);
SELECT * FROM VEIW_TEST; -->조회 가능

WITH CEACK OPTOIN

WHERE절에 작성된 조건에 따라 조회된 VEIW의 범위 밖의 데이터는 수정하지 못하게 만드는 옵션이다. WHERE절이 달라지면 VIEW의 결과 화면이 달라지기 때문에 수정하지 못하게 막을 때 사용한다. SELECT문의 WHERE절에 작성한다.

CREATE VIEW V_CHECK
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D5' WITH CHECK OPTION;

SELECT * FROM V_CHECK;

UPDATE V_CHECK SET DEPT_CODE='D6' WHERE EMP_NAME='하이유';
-->⚠️ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다.

WITH READ ONLY

VIEW TABLE을 생성 후 수정 자체를 불가능하게 만드는 옵션으로 읽기 전용 옵션이라고 한다.

CREATE VIEW V_CHECK1
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D5' WITH READ ONLY;

SELECT * FROM V_CHECK1;

UPDATE V_CHECK1 SET EMP_NAME='김재훈' WHERE EMP_NAME='하이유';
-->ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.