Oracle - view

 

VIEW

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

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

사용자는 테이블을 사용하는 것과 동일하게 사용 가능

VIEW 사용하는 이유 ?

특정 데이터나 컬럼의 정보를 선별적으로 보여줄 수 있음(민감한 정보 보호)

사용 빈도가 높은 복잡한 쿼리가 있다면 이를 VIEW로 만들어서 사용하면 편리

사용할때마다 서브쿼리해서 JOIN으로해서 복잡하게 할필요없다는 이야기

 

생성

권한 부여

VIEW 오브젝트를 생성하기 위해선 권한을 부여해줘야한다.

만약 권한을 부여안하고 실행하면 에러발생함

 

대충 권한이없어서 실행할수없다는이야기

 

생성방법

-- [표현식]
-- CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리
-- [OR REPLACE] : 뷰 생성 시 기존에 같은 이름의 뷰가 있다면 해당 뷰를 변경

-- 우선 권한부여부터함
-- 시스템 계정으로 해야함
GRANT CREATE VIEW TO STU;


-- 사원번호, 사원이름, 부서명, 근무국가명 을 V_EMPLOYEE 로 생성하세요.
CREATE OR REPLACE VIEW V_EMPLOYEE
AS SELECT EMP_ID
        , EMP_NAME
        , DEPT_TITLE
        FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
        LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
        LEFT JOIN NATIONAL USING(NATIONAL_CODE);

권한 부여후 VIEW 생성하면 

 

 

VIEW 함수식

-- 사번, 이름, 직급, 성별, 근무년수 을 V_EMP_JOB VIEW에 조회하시오.
-- 함수식에 별칭 붙인이유는 별칭을 안붙이면 VIEW 생성이안된다
-- 항상 함수식에는 별칭 필수
CREATE VIEW V_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
        JOIN JOB USING(JOB_CODE);
        
-- 방법 2
-- VIEW 생성할때 이름옆에 별칭붙여 생성해도 됨
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);

 

생성된 VIEW DML 사용

DML은 INSERT(추가), UPDATE(수정), DELETE(삭제) 사용가능하다.

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

 

JOB 테이블

 

1. V_JOB VIEW 생성

-- V_JOB 이라는 VIEW 생성 
CREATE VIEW V_JOB
AS SELECT *
    FROM JOB;

 

2. JOB 테이블에 J8 = 인턴 행 추가

-- JOB 테이블에 J8 = 인턴 행 추가
INSERT INTO JOB
VALUES('J8', '인턴');

V_JOV VIEW 확인

JOB테이블에 값을추가하였는데 V_JOB에도 추가되었다.

 

--2. V_JOB 테이블에 J9 = 알바 행 추가해도
--   베이스테이블에 추가된다.
INSERT INTO V_JOB
VALUES('J9', '알바');

 

 

 

VIEW 조작 불가능경우

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

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

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

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

5. DISTINCT를 포함한 경우

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

 

1. 뷰 정의에 포함되지 않은 컬럼 조작

뷰 정의에 포함되지 않은 컬럼을 INSERT/UPDATE/DELETE하는 경우 에러 발생

-- 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
CREATE VIEW V_JOB2
AS SELECT JOB_CODE
     FROM JOB;
     
INSERT INTO V_JOB2 VALUES('J8', '인턴');

V_JOB2 뷰에는 JOB_CODE만 정의되어있는데

그래서 INSERT문이 정상작동안한다.

 

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

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

UPDATE/DELETE는 가능함

-- 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우
CREATE VIEW V_JOB3
AS SELECT JOB_NAME
     FROM JOB;
     
-- 베이스테이블의 JOB_CODE는 NOT NULL 제약조건이 걸려있다.
INSERT INTO V_JOB3('인턴');

JOB 테이블의 JOB_CODE는 NOT NULL 제약조건이걸려있음

 

에러발생

 

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

뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE 시 에러발생함

DELETE는 사용가능함

-- 산술 표현식으로 정의된 경우
CREATE VIEW EMP_SAL
AS SELECT EMP_ID 
        , EMP_NAME
        , SALARY
        , (SALARY + (SALARY * NVL(BONUS, 0))) * 12 AS "보너스 포함 연봉"
   FROM EMPLOYEE;

INSERT INTO EMP_SAL(800, '정진훈', 3000000, 36000000);

INSERT로 (사원번호, 사원이름,월급, 보너스포함연봉) 추가함

하지만 "보너스포함연봉은" 계산된 산술 표현식이기때문에 추가가될수가없음 ..

 

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

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

-- 그룹함수 또는 GROUP BY 절을 포함한 경우
CREATE VIEW V_GROUPDEPT
AS SELECT DEPT_CODE
        , SUM(SALARY) AS "합계"
        , AVG(SALARY) AS "평균"
        FROM EMPLOYEE
        GROUP BY DEPT_CODE;

INSERT INTO V_GROUPDEPT VALUES('D10', '10000000', '1321231321');

함수 산술식도안돼는데 ..이게될거같기도않다 ..

 

5.DISTINCT를 포함한경우

DISTINCT = 중복제거

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

-- DISTINCT를 포함한 경우
CREATE VIEW V_DT_EMP
AS SELECT DISTINCT JOB_CODE
              FROM EMPLOYEE;

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

 

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

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

DELETE는 가능

-- JOIN을 이용해 여러 테이블을 연결한 경우
CREATE VIEW V_JOINEMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
     FROM EMPLOYEE
     JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
     
INSERT INTO V_JOINEMP VALUES(600, '박시후', '인사관리부');

 

 

VIEW 구조

뷰 정의 시 사용한 쿼리 문장이 TEXT컬럼에 저장되어 있으며

뷰가 실행될 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서 결과를 보여주는 구조입니다.

 

-- VIEW 구조
SELECT * FROM USER_VIEWS;

VIEW 생성할때 구문들이 TEXT컬럼에 저장되어있다.

 

VIEW 옵션

OR REPACE 옵션

생성한 뷰가 존재하면 뷰를 갱신함(덮어씌우기)

-- OR REPLACE
--순으로 실행하면 처음에 EMP_ID, EMP_NAME 저장되었다가
-- EMP_ID만 저장하는것으로 덮어씌우기됨
CREATE OR REPLACE VIEW RE_VIEW
AS SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE;

CREATE OR REPLACE VIEW RE_VIEW
AS SELECT EMP_ID
FROM EMPLOYEE;

 

FORCE/NOFORCE 옵션

FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성가능

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

 

-- FORCE
CREATE OR REPLACE FORCE VIEW V_EMP
AS SELECT TCODE, TNAME, TCONTENT
     FROM TT;

 

-- NOFORCE
CREATE OR REPLACE NOFORCE VIEW V_EMP_NO
AS SELECT TCODE, TNAME, TCONTENT
     FROM TT;

NOFORCE 일경우 에러방생

WITH CHECK OPTION옵션

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

-- WITH CHECK OPTION 옵션
-- DEPT_CODE = 'D1' 사람만 들어올수있음
CREATE OR REPLACE VIEW CK_VIEW
AS SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1'
WITH CHECK OPTION;

SELECT * FROM EMPLOYEE;

-- D1이기 떄문에 가능
INSERT INTO CK_VIEW
VALUES (666, '오현정', '660101-2000000', 'oh_hj@kh.or.kr', '01012344321', 'D1', 'J7', 'S1', 8000000, 0.1 , 201, SYSDATE, NULL, 'N');

-- D2여서 추가 불가능
INSERT INTO CK_VIEW
VALUES (667, '강현정', '660101-2000000', 'oh_hj@kh.or.kr', '01012344321', 'D2', 'J7', 'S1', 8000000, 0.1 , 201, SYSDATE, NULL, 'N');

WITH READ ONLY 옵션

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

--WITH READ ONLY
CREATE OR REPLACE VIEW V_DEPT
AS SELECT *
     FROM DEPARTMENT
     WITH READ ONLY;
     
SELECT * FROM V_DEPT;

DELETE  FROM V_DEPT;