[Oracle] 11. Object(VIEW)

1.Object

: 데이터베이스를 이루는 논리적인 구조물들

 

- TABLE, USER, VIEW, SEQUENCE, INDEX, PACKAGE, TRIGGER, FUNCTION, RPOCEDURE...

 

▶ VIEW 뷰
    - SELECT문을 수행한 수행결과(RESULTSET)를 저장해둘 수 있는 객체
    (자주 쓰일 SELECT문을 VIEW에 저장해두면 매번 긴 SELECT문을 다시 기술할 필요가 없다.)

    => 조회를 위한 임시테이블 같은 존재이며 실제 데이터가 담겨있는 것은 아니다.
    => 조회를 위한 서브쿼리문만을 저장하며 VIEW 호출시 서브쿼리를 실행시킨다.

 

 

---

2. VIEW 생성방법

[표현법]

CREATE VIEW 뷰명     AS 서브쿼리;

 

=> 뷰 생성

 

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

 

=> 뷰 생성시 기존에 중복된 이름의 뷰가 없다면 새롭게 뷰가 생성되고 기존에 중복된 이름의 뷰가 있다면 해당 이름의 뷰를 변경한다.

 

 

 

▶ 실습문제(VIEW 생성)

- '한국'에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명, 직급명을 조회하시오.

 

 

 

- VIEW 생성

 

 

☞ 먼저 현재 계정에 뷰 생성권한을 주지 않으면 오류가 발생한다.

☞ 관리자 계정에서 GRANT CREATE VIEW TO KH; 로 권한 부여를 먼저 한 후, 실행해야한다.

 

 

 

 

☞ 권한 부여 후, 실행결과 정상적으로 쿼리문 실행되는 것 확인

 

=> 서브쿼리 형태로 저장이 되어 테이블처럼 VIEW를 사용할 수 있다.

 

 

 

- '러시아'에 근무하는 사원들의 사번, 이름, 직급명, 보너스 조회

 

 

☞ 뷰는 논리적인 가상테이블이다! => 실제로 데이터를 저장하고 있지 않음
☞ 뷰는 단순히 쿼리문을 TEXT 형태로 보관만 하는 객체

 

 

---

3. VIEW 컬럼에 별칭 부여

: 서브쿼리부분에 SELECT절에 함수호출식, 산술연산식등이 기술된 경우 반드시 별칭을 지정.

 

 

▶ 사원의 사번, 이름, 직급명, 성별, 근무년수를 조회할 수 있는 SELECT문을 만들고 이를 VIEW로 정의

 

 

☞ 별칭을 붙이기 전에 오류 발생했지만, 별칭 부여후 정상적으로 작동

 

 

▶ 다른 방법으로 별칭 부여하기(단, 모든 칼럼에 대해 별칭을 기술해야함)

 

 

---

4. VIEW 삭제

DROP VIEW 테이블명;

 

 

---

5. 생성된 뷰를 이용해서 DML(INSERT, UPDATE, DELETE)사용 가능

 

※ 주의사항 : 뷰를 통해서 조작하게된다면, 실제 데이터가 담겨있던 테이블에 변경사항이 적용된다.

 

 

① VIEW 테이블 생성 (VW_JOB테이블이라는 VIEW 생성)

 

 

 

 

② VIEW에 INSERT

 

 

 

③ SELECT * FROM JOB; 결과

 

☞ VIEW가 아니라 JOB 테이블에 데이터가 추가됨

 

☞ INSERT 뿐만 아니라 DELETE, UPDATE도 이와 같은 결과가 발생한다!

 

 

 

 

※ VIEW를 활용해서 DML이 가능한 경우 : 서브쿼리를 이용해서 기존의 테이블을 그대로 복제한 경우만 가능
    
☞ 하지만 뷰를 가지고 DML이 불가능한 경우가 훨씬 더 많다. => 추가적인 처리가 더 들어간 경우 불가능.
    1) 뷰에 정의되어 있지 않은 컬럼을 조작하는 경우
    2) 뷰에 정의되어 있지 않은 컬럼 중에 베이스테이블상에 NOT NULL 제약조건이 지정된 경우
    3) 산술연산식 또는 함수를 통해 정의되어있는 경우
    4) 그룹함수나 GROUP BY 절이 포함된 경우
    5) DISTINCT 구문이 포함된 경우
    6) JOIN을 이용한 경우

 

 

---

6. VIEW에서 DML이 불가한 경우

 

1) 뷰에 정의되어 있지 않은 컬럼을 조작하는 경우

 

 

☞ 이 3가지 쿼리문 모두 에러발생

 

 

2) 뷰에 정의되어 있지 않은 컬럼 중에 NOT NULL 제약조건이 지정된 경우

 

 

☞ JOB테이블에 NOT NULL 제약조건이 걸림. JOB_CODE에 NULL값을 넣으려고 함. 

=> 에러발생

 

 

 

3) 산술연산식 또는 함수를 통해 정의되어있는 경우

 

☞ 에러 발생. 가상칼럼에 값을 추가할 수 없음

 

 

 

4) 그룹함수나 GROUP BY 절이 포함된 경우

 

 

 

☞ 에러발생

 

 

5) DISTINCT 구문이 포함된 경우 DML불가.

6) JOIN을 이용해서 여러 테이블을 매칭시켜 놓은 경우

 

=> 이경우에는 되는 경우가 있고 안되는 경우가 있다.

아래 예시를 통해 알아보도록 한다!

 

 

 

 

① JOIN 이용하여 VIEW 테이블에 DML이 가능한 경우

 

- 사번을 제시해서 이름 변경시

 

 

 

 

② JOIN 이용하여 VIEW 테이블에 DML이 불가능한 경우

 

- 사번을 제시해서 부서이름을 변경시

 

 

☞ 서로다른 테이블에 있는 칼럼을 사용시 에러발생

 

 

 

---

7. VIEW에 사용가능한 옵션들

 

1) OR REPLACE

 

 

2) FORCE / NOFORCE

: 실제 테이블이 없더라도 VIEW를 강제로 생성할 수 있게 해주는 옵션

 

 

[표현법]

CREATE OR REPLACE [NOFORCE/FORCE] (생략가능)

 

 

 

☞ NOTHING이라는 테이블이 없는데도 정상적으로 VIEW가 생성됨

 

☞  하지만, SELECT * FROM V_FORCETEST; 을 실행하게 되면 에러 발생.

 

 

 

3) WHTH CHECK OPTION

: SELECT 문의 WHERE 절에서 사용한 칼럼은 수정하지 못하게 막는 옵션

 

 

 

 

 

UPDATE V_CHECKOPTION SET DEPT_CODE = 'D6' WHERE EMP_ID = 215;

 

☞ 에러 발생

 

 

UPDATE V_CHECKOPTION SET SALARY = '5000000' WHERE EMP_ID = 215;

 

 

☞  실행 O (WHERE에 존재하는 칼럼이 아니기 때문에 실행된다)

 

 

 

4) WITH READ ONLY

: VIEW 자체를 수정 못하게 차단하는 옵션

 

 

 

 

 

 

UPDATE V_READ SET SALARY = 10000000;

 

☞ 에러 발생. WITH READ ONLY 옵션을 걸었기 때문에 에러발생한다.