[SQL] 뷰 VIEW

VIEW

- SELECT(퀴리문)문을 저장해두는 객체
- 자주 사용하는 긴 SELECT문을 저장해두면,
   해당 SELECT문을 매번 다시 기술할 필요가 없음
- 뷰는 임시테이블에 저장됨 (실제 데이터 X)

CREATE [ OR REPLACE ]  VIEW 뷰명 
AS 서브쿼리;  -->OR REPLACE 생략가능

==> 뷰가 서브쿼리에 담긴 내용을 대신할 수 있다 



* OR REPLACE 작성하여 뷰를 생성하면: 
기존 존재하는 뷰 중에서 중복된 이름의 뷰가 없다면 새로 만들고, 
이미 존재하는 뷰라면 해당 뷰를 갱신하는 옵션

* 뷰 내용 수정 : 
OR REPLACE문을 적어놨다면, 뷰 내용 갱신이 가능함!
(기존 뷰 내용을 수정한 후 재실행 시 덮어쓰기처리)

--'한국'에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명, 직급명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_CODE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN JOB
USING (JOB_CODE)
LEFT JOIN DEPARTMENT
ON DEPT_CODE = DEPT_ID
LEFT JOIN LOCATION 
ON LOCAL_CODE = LOCATION_ID
WHERE NATIONAL_CODE = 'KO';

--뷰 이름: VW_EMPLOYEE

DROP VIEW VW_EMPLOYEE;

--사원들의 사번, 이름, 부서명, 급여, 근무국가명, 직급명으로 뷰를 생성한 후,
  1) 근무국가가 '한국'(NATIONAL_CODE ='KO')인 사원 조회
  2) 근무국가가 '일본'(NATIONAL_CODE ='JP')인 사원 조회
CREATE OR REPLACE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_CODE, JOB_NAME, BONUS
        FROM EMPLOYEE
         LEFT JOIN JOB
        USING (JOB_CODE)
        LEFT JOIN DEPARTMENT
        ON DEPT_CODE = DEPT_ID
        LEFT JOIN LOCATION 
        ON LOCAL_CODE = LOCATION_ID

1)
SELECT * FROM VW_EMPLOYEE
WEHRE NATIONAL_CODE = 'KO';

2)
SELECT * FROM VW_EMPLOYEE
WEHRE NATIONAL_CODE = 'JP';

SELECT * FROM VW_EMPLOYEE ---- 뷰는 테이블화 되어(가상의 테이블) 테이블처럼 쓸 수 있음
WHERE NATIONAL_NAME = '한국';
--- 뷰 생성시 서브쿼리를 이용하면 복잡한 쿼리문을 작성하는 대신 
    뷰를 이용하여 간편하게 조회가 가능함
----뷰는 논리적인 가상테이블(하드디스크에는 저장되지 않는다) 

SELECT * FROM VW_EMPLOYEE;

SELECT * FROM USER_VIEWS; 
---- VIEW는 쿼리문이 텍스트로 저장되는 것, 
     해당 계정이 가지고 있는 VIEW의 내용을 조회하려면 데이터 딕셔너리중 USER_VIEWS를 조회

-- 사원의 사번, 이름, 직급명, 성별, 근무년수
SELECT EMP_ID, EMP_NAME, JOB_NAME, 
             DECODE( SUBSTR(EMP_NO, 8,1), 1, '남', 2, '여') AS 성별 ,
             EXTRACT (YEAR FROM SYSDATE)  - EXTRACT (YEAR FROM HIRE_DATE) AS 근무년수
LEFT JOIN JOB USING (JOB_CODE);


---서브쿼리로 사용할 컬럼에 특수문자, 산술연산 등이 사용되었을 시 
   별칭을 지정하지 않으면 에러 발생: MUST NAME THIS EXPRESSION WITH A COLUMN ALIAS
-- 사원의 사번, 이름, 직급명, 성별, 근무년수

 --VW_EMP_JOB
 
 
 CREATE OR REPLACE VIEW VW_EMP_JOB 
 AS SELECT EMP_ID, EMP_NAME, JOB_NAME, 
                   DECODE( SUBSTR(EMP_NO, 8,1), 1, '남', 2, '여') AS 성별 ,
                   EXTRACT(YEAR FROM SYSDATE ) - EXTRACT (YEAR FROM HIRE_DATE) AS 근무년수
                   FROM EMPLOYEE
                   LEFT JOIN JOB USING (JOB_CODE);
                   
-- 근무년수가 15년 이상인 사원 조회

SELECT *
FROM VW_EMP_JOB
WHERE 근무년수 >=15;

VIEW를 이용한 DML 연산 (제약사항이 많음!)

뷰 테이블에서 DML 사용시,
실제 데이터가 담겨있는 베이스 테이블에 해당 DML 결과가 적용됨

CREATE OR REPLACE VIEW VW_JOB
AS SELECT * FROM JOB;

SELECT * FROM VW_JOB;
SELECT * FROM JOB;
--- 뷰에 INSERT

INSERT INTO VW_JOB VALUES ( 'J8', '인턴');
SELECT * FROM JOB; ---> 조회시 VIEW에 추가한 내용이 베이스 테이블에 적용되어 있음

--JOB_CODE 가 J8인 JOB_NAME을 알바로 업데이트

UPDATE VW_JOB
      SET JOB_NAME = '알바'
    WHERE JOB_CODE = 'J8';  

--SELECT절은 조회결과가 RESULT SET(행들의 집합) 으로 결과가 돌아오고
--DML은 처리된 행의 갯수를 알려준다. 조회결과가 몇 행이 처리되었는지 옴  < 1 행 이(가) 업데이트되었습니다.>


DELETE FROM JOB
WHERE JOB_CODE = 'J8';    --- 없는 행을 처리할 경우 < 0개 행 이(가) 삭제되었습니다.>
                                         --- 돌릴게 없다고 프로그램이 안돌아가는 것이 아니다!!
                                         
                                
COMMIT;

VIEW를 이용한 DML 연산이 제한되는 경우

1) 뷰에 정의되지 않은 컬럼을 조작하는 경우
2) NOT NULL제약조건이 지정된 경우
3) 산술연산식 또는 함수를 통해 정의되어 있는 경우
4) 그룹함수, GROUP BY절이 포함된 경우
5) DISTINCT구문이 포함된 경우
6) JOIN을 이용해 여러 테이블을 매칭시켜놓은 경우

 

 

--1)  뷰에 정의되지 않은 컬럼을 조작하는 경우
CREATE OR REPLACE VIEW VW_JOB
        AS SELECT JOB_CODE FROM JOB;

INSERT INTO VW_JOB VALUES ( JOB_CODE, JOB_NAME); --- SQL 오류: ORA-00913: too many values

UPDATE VW_JOB
SET JOB_NAME = '인턴'
WHERE JOB_CODE = 'J7';  --SQL 오류: ORA-00904: "JOB_NAME": invalid identifier

DELETE FROM VW_JOB
WHERE JOB_NAME = '사원'; SQL 오류: ORA-00904: "JOB_NAME": invalid identifier

----VW_JOB에 존재하지 않는 컬럼에 값을 추가, 변경, 삭제시 오류발생  "JOB_NAME": invalid identifier

--2)  뷰에 정의되지 않은 컬럼중에 베이스테이블 상에 NOTNULL 제약조건이 지정된 경우
CREATE OR REPLACE VIEW VW_JOB
AS SELECT JOB_NAME FROM JOB;


--2) NOT NULL제약조건이 지정된 경우
SELECT * FROM VW_JOB;
INSERT INTO VW_JOB VALUES ('인턴'); --- ORA-01400: cannot insert NULL into ("KH"."JOB"."JOB_CODE")
                                                        --INSERT는 행단위로 값을 추가하는데, VW_JOB의 베이스테이블에 해당 행 중에서 NOT NULL이 걸린 컬럼이 있음
                                                        -- 사용불가..
UPDATE VW_JOB
SET JOB_NAME  = '알바'
WHERE JOB_NAME = '사원';

SELECT * FROM VW_JOB;

ROLLBACK;

--3) 산술연산식 또는 함수를 통해 정의되어 있는 경우
-- 사원의 사번, 사원명, 급여, 연봉에 대해 조회하는 뷰

CREATE OR REPLACE VIEW VW_EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY , SALARY*12 AS "연봉" 
FROM EMPLOYEE; 



INSERT INTO VW_EMP_SAL VALUES (400, '김초보', 2000000, 240000); --- SQL 오류: ORA-01733: virtual column not allowed here
UPDATE VW_EMP_SAL
SET 연봉 = 4000000
WHERE EMP_ID = 200; -- SQL 오류: ORA-01733: virtual column not allowed here
-- 가상의 컬럼에 대한 값 조작은 불가하다

--- 존재하는 컬럼은?
UPDATE VW_EMP_SAL
SET SALARY = 4000000
WHERE EMP_ID = 200;

DELETE FROM VW_EMP_SAL
WHERE 연봉 = 72000000;
---- 조작하는 것이아니라 단순히 WHERE의 조건절로 쓰는 것은 가능하다

---- 4) 그룹함수, GROUP BY절이 포함된 경우
-- 부서별 급여합, 평균급여를 조회하는 뷰
CREATE OR REPLACE VIEW VW_GROUPDEPT
AS SELECT DEPT_CODE, SUM(SALARY) "급여합", ROUND (AVG(SALARY)) "평균급여"
        FROM EMPLOYEE
      GROUP BY DEPT_CODE;


SELECT * FROM VW_GROUPDEPT;

INSERT INTO VW_GROUPDEPT VALUES('D0', 80000, 20000); ---- SQL 오류: ORA-01733: virtual column not allowed here

UPDATE VW_GROUPDEPT
SET 급여합 = 8000000
WHERE DEPT_CODE = 'D1'; --오류 보고 -SQL 오류: ORA-01732: data manipulation operation not legal on this view


---5) DISTINCT구문이 포함된 경우: INSERT, UPDATE, DELETE다 안됨
CREATE OR REPLACE VIEW VW_DT_JOB 
AS SELECT DISTINCT(JOB_CODE) FROM EMPLOYEE;


INSERT INTO VW_DT_JOB VALUES ('J8'); --- SQL 오류: ORA-01732: data manipulation operation not legal on this view

---6) JOIN을 이용해 여러 테이블을 매칭시켜놓은 경우
CREATE OR REPLACE VIEW VW_JOINEMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;

SELECT * FROM VW_JOINEMP;
INSERT INTO VW_JOINEMP VALUES (999, '어쩌고', '무슨부'); -- SQL 오류: ORA-01776: cannot modify more than one base table through a join view

ROLLBACK;

VIEW 옵션지정

 CREATE OR REPLACE FORCE/NOFORCE  뷰명  [ 생략시 NOFORCE 기본값]
 WITH CHECK OPTION
 WITH READ ONLY;

 
 1) OR REPLACE : 해당 뷰가 존재하지 않을 시 신규생성, 있을 시 덮어쓰기

 2) FORCE/NOFORCE: 
    - FORCE: 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰 생성
     ----> 뷰 틀을 먼저 설계해두고 실제 데이터(컬럼)을 채울 떄 사용
    - NOFORCE(기본값) : 서브쿼리에 기술된 테이블이 반드시 존재해야만 뷰 생성

3) WITH CHECK OPTION: 서브쿼리 조건절에 기술된 내용을 만족하는 값으로만 DML가능
    조건에 부합하지 않는 값으로 수정하는 경우 오류 발생

4) WITH READ ONLY: 뷰에 대해 조회만 가능(DML 수행불가)

2) FORCE/NOFORCE: 

FORCE: 실제 존재하지 않는 컬럼으로 뷰 생성시
CREATE OR REPLACE FORCE VIEW  VW_TEST  --경고: 컴파일 오류와 함께 뷰가 생성되었습니다.
AS SELECT FORCE, NOFORCE
FROM NIKE;

SELECT * FROM VW_TEST;
==> ORA-04063: view "CC.VW_TEST" has errors
	04063. 00000 -  "%s has errors"
	*Cause:    Attempt to execute a stored procedure or use a view that has
    	       errors
               
----> 뷰 틀만 먼저 설계해두고 실제 데이터(컬럼)을 채울 때 사용
----> INSERT로 해당 테이블과 컬럼들을 CREATE 한 후 다시 조회시
CREATE TABLE NIKE (
    FORCE NUMBER,
    NOFORCE NUMBER
);

SELECT * FROM VW_TEST;
-- 채워넣고 나니 오류 안난다!


3) WITH CHECK OPTION: 서브쿼리 조건절에 기술된 내용을 만족하는 값으로만 DML가능
   -- 조건에 부합하지 않는 값으로 수정하는 경우 오류 발생
CREATE OR REPLACE VIEW VW_EMP
AS SELECT * FROM EMPLOYEE
WHERE SALARY >= 3000000
WITH CHECK OPTION;

SELECT * FROM VW_EMP;   
   
UPDATE VW_EMP
SET SALARY = 2999999;  

-----  ORA-01402: view WITH CHECK OPTION where-clause violation
--- 서브쿼리에 기술한 조건에 부합하지 않기 때문에 변경 불가

   
   
4) WITH READ ONLY: 뷰에 대해서 조회만 가능(DML 수행불가)
CREATE OR REPLACE VIEW VW_EMPBONUS
AS SELECT EMP_ID ,EMP_NAME, BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL 
WITH READ ONLY;


SELECT * FROM VW_EMPBONUS;

DELETE FROM VW_EMPBONUS
WHERE EMP_ID = '213';
--SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view