DB (11) OBJECT

▶ OBJECT

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

 

■ OBJECT의 종류

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

 

▶ 뷰(VIEW)

- SELECT문을 저장해 둘 수 있는 객체

- 자주쓰일 긴 SELECT문을 VIEW에 저장해두면 매번 긴 SELECT문을 다시 기술할 필요가 없다.

- 조회용 임시테이블 같은 존재(실제 데이터가 담겨있는 것은 아니다.)

- 해당 계정이 가지고 있는 VIEW들에 대한 내용을 조회하고자 한다면 USER_VIEWS 데이터 딕셔너리를 이용하면 된다.

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

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

 

■ VIEW 생성 방법

[표현법]
CREATE VIEW 뷰명 AS 서브쿼리;
------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW 뷰명 AS 서브쿼리;
=> 뷰 생성시 기존에 중복된 이름의 뷰가 없다면 새롭게 뷰가 생성되고 기존에 중복된 이름의 뷰가 있다면 그 이름의 뷰를 변경한다.(OR REPLACE는 생략 가능)

 

■ VIEW 컬럼에 별칭 부여

- 서브쿼리 부분에 SELECT절에 함수 OR 산술연산식이 기술되어 있는 경우 "반드시" 별칭으로 지정해줘야 한다.

- 뷰에서 생성할 때 붙인 별칭, 리터럴값들을 SELECT시 활용 가능하다.

별칭부여1 ex)

-- 사원의 사번, 이름, 직급명, 성별, 근무년수를 조회할 수 있는 SELECT문을 VIEW로 정의
CREATE OR REPLACE VIEW VW_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);
-- 오류발생 : must name this expression with a column alias

CREATE OR REPLACE VIEW VW_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);
-- 뷰 생성 성공

별칭부여2 ex)

CREATE OR REPLACE VIEW VW_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 ex)

SELECT 사원명, 직급명
FROM VW_EMP_JOB
WHERE 성별 = '여';

 

■ DML이 가능한 경우

- 서브쿼리를 이용해서 기존의 테이블을 별도의 처리 없이 복제하고자 할 경우

 

■ DML이 불가능한 경우

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

 

■ VIEW에서 사용가능한 옵션들

● OR REPLACE

ex)

CREATE OR REPLACE VIEW V_EMP_SALARY
AS SELECT * FROM EMPLOYEE;

 

● FORCE / NOFORCE 

- 실제 테이블이 없더라도 VIEW를 먼저 생성할 수 있게 해주는 옵션

- CREATE OR REPLACE NOFORCE : 기본값

ex)

CREATE FORCE VIEW V_FORCETEST
AS SELECT A, B, C FROM NOTEXIST; -- 없는 테이블

 

●  WITH CHECK OPTION

- SELECT문의 WHERE절에서 사용한 컬럼을 수정하지 못하게 하는 옵션

ex)

CREATE OR REPLACE VIEW V_CHECKOPTION
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' WITH CHECK OPTION;

UPDATE V_CHECKOPTION
SET DEPT_CODE = 'D6' -- DEPT_CODE를 WITH CHECK OPTION 옵션이 적용되어 수정이 불가능하다.
WHERE EMP_ID = 206; -- 에러발생

 

●  WITH READ ONLY

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

ex)

CREATE OR REPLACE VIEW V_READ
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' WITH READ ONLY;

UPDATE V_READ
SET EMP_NAME = 5000000; -- 에러발생
-- cannot perform a DML operation on a read-only view