11.OBJECT(VIEW)

 

ㅁ < VIEW 뷰 >

- 쿼리문(SELECT문)을 저장해둘 수 있는 데이터베이스 객체.

- 내가 자주 쓰는 쿼리문을 저장해두는 것.

- 임시 테이블처럼 활용할 수 있음.

실제 데이터가 존재하는 물리적 테이블은 아니고, 논리적인 테이블이다.

- VIEW를 통해 DML문 수행시 많은 제약이 있으므로 조회용으로만 쓰는 것을 권장함.

 

 

ㅁ 이전까지는 다 작성해야 했다.

 

ㅇ '한국'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회

 

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE 
WHERE NATIONAL_NAME = '한국';

 

 

ㅇ '러시아'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회

 

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE 
WHERE NATIONAL_NAME = '러시아';

 

 

ㅇ '일본'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회

 

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE 
WHERE NATIONAL_NAME = '일본';

 

 

ㅁ VIEW 생성

 

 

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰명 AS 서브쿼리

     [WITH CHECK OPTION]

     [WITH READ ONLY];

 

 

- 테이블이나 시퀀스는 ALTER로 수정하지만, 뷰는 ALTER로 수정하지 않음.

그냥 CREATE OR REPLACE로 덮어씌운다.

 

- OR REPLACE : 뷰명이 없으면 만들고, 있으면 교체한다. 

 

- FORCE : 서브쿼리에 기술된 테이블이 (아직) 없는 테이블이어도 뷰가 생성되도록 하는 옵션.

NO FORCE : 서브쿼리에 기술된 테이블이 존재해야만 뷰를 생성시키는 옵션. (기본값)

 

- WITH CHECK OPTION : VIEW를 통해서 DML시 서브쿼리에 기술된 조건에 부합한 값으로만 DML이 가능하도록 하는 옵션. 없으면 아무값으로나 DML다 가능함.

- WITH READ ONLY : 뷰를 조회용으로만 설정하는 옵션.

 

 

 

ㅁ 뷰 사용

 

 

ㅇ BR계정) 뷰 생성 시도1

 

CREATE OR REPLACE VIEW VW_EMPLOYEE

  AS

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE;

 

 

- 오류. "insufficient privileges". 시퀀스나 뷰를 생성하려면 생성권한이 필요함.

 

 

ㅇ 관리자계정) BR계정에게 CREATE VIEW 권한 부여

 

GRANT CREATE VIEW TO BR;

 

 

ㅇ BR계정) 뷰 생성 시도2 & 뷰 조회

 

 

 

- 뷰 VW_EMPLOYEE  생성 완료.

- 뷰 조회하면 저 서브쿼리의 결과가 조회됨.

- 실제 데이터를 담고있는 물리적인 테이블은 아님. 데이터가 없음.

저 서브쿼리 결과를 보여주는 논리적인 테이블일 뿐.

- EMPLOYEE, JOB처럼 실제 데이터가 담겨있는 테이블을 베이스 테이블이라고도 함.

 

 

 

 

ㅇ 간편하게 조회하기

 

 

 

 

ㅁ 참고) USER_VIEWS

- 현재 계정이 소유하고 있는 뷰에 대한 정보를 표현하는 테이블

- Data Dictionary

 

 

- 뷰는 쿼리문을 텍스트로 가지고 있는 객체. (서브쿼리의 결과값을 가지고 있는게 아님)

 

 

 

 

ㅁ 뷰 사용2

 

 

ㅇ 사번, 이름, 직급명, 성별(남|여), 근무년수를 조회할 수 있는 SELECT문을 뷰(VW_EMPJOB)로 정의.

 

 

CREATE OR REPLACE VIEW VW_EMPJOB
    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);

 

- 근무년수 :  EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)

- 오류. "must name this expression with a column alias"

- 뷰 정의시 서브쿼리에 산술식, 함수식이 있을 경우 반드시 별칭을 부여해야 한다.

 

 

CREATE OR REPLACE VIEW VW_EMPJOB
    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);

 

- 성공적으로 생성된다.

 

 

 

 

 

ㅇ 별칭을 이렇게도 줄 수 있다. 

 

CREATE OR REPLACE VIEW VW_EMPJOB(사번, 사원명, 직급명, 성별, 근무년수)
    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);

 

- OR REPLACE 옵션 덕에 오류 안나고 갱신됨.

- 단, 모든 별칭을 다 써야 한다. 모든 컬럼에 다 alias 부여.

 

 

 

 

ㅁ 뷰를 통해서 DML(INSERT, UPDATE, DELETE) 사용하기

- 뷰에 DML을 사용하면 뷰가 아닌 실제 베이스 테이블에 반영이 된다.

 

 

ㅇ 뷰 생성

 

- 실제 데이터는 베이스 테이블인 JOB 테이블에 있다. 뷰는 논리적인 테이블이라 데이터가 없다.

 

 

ㅇ 뷰에 DELETE

 

- 뷰에 DELETE를 사용했는데 베이스 테이블인 JOB에서도 삭제가 된다.

 

 

 

ㅁ 뷰에 DML로 조작이 불가능한 경우가 더 많다. (그래서 조회용으로만 쓰는게 좋다)

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

- 뷰에 정의되어 있지 않은 컬럼 중에 베이스테이블 상에 NOT NULL 제약조건이 지정되어 있는 경우

- 산술연산식 또는 함수식으로 정의되어 있는 경우

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

- DISTINCT 구문이 포함된 경우

- JOIN을 이용해서 여러 테이블을 연결시켜놓은 경우