OBJECT
: 데이터베이스를 이루는 논리적 구조물들
- OBJECT의 종류에는 TABLE과 USER, VIEW, SEQUENCE, PACKAGE, TRIGGER, FUNCTION, PROCEDURE가 있다.
VIEW
: SELECT를 저장해놓을 수 있는 객체
(1) 장점
: 자주 사용할 SELECT문을 VIEW에 담아놓고 사용하면 매번 SELECT문을 작성할 필요가 없어진다.
→ 조회용 임시테이블(실제로 값이 담겨있는 것이 아니다.)
(2) 생성 방법
ⓐ CREATE VIEW 뷰명
AS 서브쿼리;
ⓑ CREATE OR REPLACE VIEW 뷰명
AS 서브쿼리;
- 뷰 생성 시 기존에 중복된 이름의 뷰가 없으면 새로 생성하고, 있으면 해당 뷰에 덮어쓰기를 한다.
- OR REPLACE 옵션은 생략할 수 있다.
-- VIEW 생성 권한을 부여하기
GRANT CREATE VIEW TO YJ;
-- 권한 부여할 땐 관리자 계정에서 진행해야 한다.
-- '한국'에서 근무하는 사원들의 사번과 이름, 부서명, 급여, 근무국가명, 직급명을 조회하기
CREATE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, N.NATIONAL_NAME, J.JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, NATIONAL N, JOB J, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE
AND E.JOB_CODE = J.JOB_CODE;
-- 다른 컬럼을 추가하고 싶을 때
-- CREATE OR REPLACE VIEW를 사용해 해당 컬럼을 추가하여 기존 뷰를 덮어씌우면 된다.
-- BONUS 컬럼 추가한 뷰 만들기
CREATE OR REPLACE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, N.NATIONAL_NAME, J.JOB_NAME, BONUS
FROM EMPLOYEE E, DEPARTMENT D, NATIONAL N, JOB J, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE
AND E.JOB_CODE = J.JOB_CODE;
VIEW는 논리적 가상테이블로, 실질적으로 데이터를 저장하지 않고 단순히 쿼리문만 저장한다.
만약, 해당 계정(YJ)이 가지고 있는 VIEW들의 내용을 조회할 때는 USERS_VIEWS를 조회하면 된다.
SELECT * FROM USERS_VIEWS;
(3) VIEW 컬럼에 별칭 부여하기
: 서브쿼리 부분에서 SELECT절에 함수나 산술연산식이 기술되어 있는 경우엔 반드시 별칭을 지정해줘야 한다.
- 방법 1. 해당 함수식 또는 산술연산식 옆에 별칭 지정하기
CREATE 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. CREATE절의 뷰명에다 별칭 기술하기(단, 이 방법은 모든 컬럼에 대해 별칭을 지정해야 한다.)
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 사원명, 직급명
FROM VW_EMP_JOB
WHERE 성별 = '남성';
(4) 생성된 VIEW에 DML 구문(INSERT, UPDATE, DELETE) 사용하기
- 주의! VIEW를 통해 조작하면 데이터가 담겨있던 실제 기준 테이블에 변경사항이 적용된다.
-- JOB 테이블로 VIEW 생성하기
CREATE VIEW VW_JOB
AS SELECT * FROM JOB;
- INSERT
INSERT INTO VW_JOB VALUES ('J8', '인턴');
- UPDATE
UPDATE VW_JOB
SET JOB_NAME = '알바'
WHERE JOB_CODE = 'J8';
- DELETE
DELETE FROM VW_JOB
WHERE JOB_CODE = 'J8';
(5) VIEW에 DML이 가능한 경우
: 서브쿼리를 이용하여 기존 테이블을 별도의 처리 없이 복제하고자 할 때 가능하다.
(6) VIEW에 DML이 불가능한 경우
: 한 번이라도 처리가 들어간 구문일 경우 불가하다.
① 뷰에 정의되어 있지 않은 컬럼을 조작하는 경우
② 뷰에 정의되어 있지 않은 컬럼 중 베이스테이블 상에 NOT NULL 제약조건이 지정된 경우
③ 함수식이나 산술연산식으로 작성되어 정의된 경우
④ 그룹함수나 GROUP BY절이 포함된 경우
⑤ DISTINCT 구문이 포함된 경우
⑥ JOIN을 이용하여 여러 테이블을 매칭시킨 경우