<Database> VIEW

이제내손을java...·2024년 3월 28일

Database View object oracle oracle object

Database 개념정리

목록 보기

10/11

오늘은 DATABASE VIEW에 대한 개념정리입니다 😊

VIEW

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블
실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만
사용자는 테이블을 사용하는 것과 동일하게 사용 가능

표현법
CREATE VIEW VIEW이름
AS SELECT문

VIEW 예시

☑️ 예시1

CREATE OR REPLACE VIEW V_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)
LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL USING(NATIONAL_CODE);
SELECT * FROM V_EMPLOYEE;

                                               ⤵️

☑️ 예시2

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);

➡️ 서브쿼리의 SELECT절에 함수가 사용된 경우 반드시 별칭 지정

☑️ 예시3
1.

SELECT*FROM JOB;

V_JOB VIEW에 INSERT하기

CREATE OR REPLACE VIEW V_JOB(사번, 이름, 직급, 성별, 근무년수)
AS SELECT JOB_NAME, JOB_NAME
FROM JOB
JOIN JOB USING(JOB_CODE);
INSERT INTO V_JOB VALUES(‘J8’, ‘인턴’);
SELECT  * FROM V_JOB;

➡️ 생성된 뷰를 가지고 DML구문(INSERT, UPDATE, DELETE) 사용 가능

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

DML명령어로 VIEW 조작이 불가능한 경우

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

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

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

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

DISTINCT를 포함한 경우

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

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

CREATE OR REPLACE VIEW V_JOB2
AS SELECT JOB_CODE
FROM JOB;

INSERT INTO V_JOB2 VALUES(‘J8’, ‘인턴’);

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

➡️ 단 DELETE는 가능

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

CREATE OR REPLACE VIEW V_JOB3
AS SELECT JOB_NAME
FROM JOB;

INSERT INTO V_JOB3 VALUES(‘인턴’);

➡️ 뷰에 포함되지 않은 NOT NULL제약조건이 있는 컬럼이 존재하면
➡️ INSERT/UPDATE 시 에러 발생

➡️단 DELETE는 가능

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

CREATE OR REPLACE VIEW EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY,
(SALARY + (SALARY*NVL(BONUS, 0)))*12 연봉
FROM EMPLOYEE;

INSERT INTO EMP_SAL VALUES(800, ‘정진훈’, 3000000, 4000000);

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

➡️ 단 DELETE는 가능

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

CREATE OR REPLACE VIEW V_GROUPDEPT
AS SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE;

INSERT INTO V_GROUPDEPT VALUES(‘D10’, 6000000, 4000000);

DELETE FROM V_GROUPDEPT WHERE DEPT_CODE = ‘D1’;

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

5. DISTINCT를 포함한 경우

CREATE OR REPLACE 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’;

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

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

CREATE OR REPLACE 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(888, ‘조세오’, ‘인사관리부’);

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

➡️ 단 DELETE는 가능

VIEW 구조

뷰 정의 시 사용한 쿼리 문장이 TEXT컬럼에 저장되어 있으며
뷰가 실행할 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서
결과를 보여주는 구조
SELECT * FROM USER_VIEWS;

1. OR REPLACE 옵션
생성한 뷰가 존재하면 뷰를 갱신함
2. FORCE/NOFORCE 옵션
FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성
NOFORCE 옵션이 기본 값으로 지정되어 있음
3. WITH CHECK OPTION 옵션
옵션을 설정한 컬럼의 값을 수정 불가능하게 함
4. WITH READ ONLY 옵션
뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함