SQL8,9 ORACLE OBJECT(VIEW) , OBJECT(SEQUENCE)

OBJECT(VIEW)

VIEW	SELECT 쿼리의 실행 결과를 화면에 저장한 논리적인 가상 테이블이다. 테이블과는 다르게 실질적으로 데이터를 저장하고 있지 않지만, 사용자는 테이블을 사용하는것과 동일하게 사용할 수 있음

조회/수정/삭제

SELECT문 <VIEW>

쿼리 수행

<수행결과>

 

EATE 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절에 함수가 사용된 경우 반드시 별칭을 지정해 주어야 한다.

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

CREATE OR REPLACE VIEW V_JOB
AS SELECT
JOB_CODE
, JOB_NAME
FROM JOB;

생성된 뷰를 가지고 DML구문(INSERT, UPDATE, DELETE) 사용 가능 생성된 뷰에 요청한 DML구문이 베이스 테이블도 변경

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

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

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우 2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우 3. 산술 표현식이 정의된 경우 4. JOIN을 이용해 여러 테이블을 연결한 경우 5. DISTINCT를 포함한 경우 6. 그룹함수나 GROUP BY 절을 포함한 경우

뷰 정의에 포함되지 않는 컬럼을 INSERT/UPDATE하는 경우 에러 발생, 단, DELETE는 가능	CREATE OR REPLACE VIEW V_JOB2           AS SELECT                            JOB_CODE                    FROM JOB; INSERT          INTO V_JOB3 VALUES(’인턴’);
뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE시 에러 발생, 단, DELETE는 가능	CREATE OR REPLACE VIEW EMP_SAL           AS SELECT           EMP_ID           , EMP_NAME           , SALARY           ,(SALARY + (SALARY * NVL(BONUS,0))) * 12 연봉 FROM EMPLOYEE;
그룹함수 혹은 GROUP BY를 사용한 경우 INSERT/UPDATE시 에러 발생, DELETE시에도 에러 발생	에러 INSERT           INTO V_GROUPDEPT VALUES(                     ‘D10’                     , 6000000                     , 4000000                     ); DELETE           FROM V_GROUPDEPT WHERE DEPT_CODE = ‘D1’;
DISTINCT를 사용한 경우 DML 사용 불가 DELETE시에도 에러 발생	INSERT           INTO V_DT_EMP VALUES (                     ‘J9’                     ); DELETE           FROM V_DT_EMP WHERE JOB_CODE = ‘J1’;
뷰 정의시 JOIN을 사용한 경우 INSERT/UPDATE시 에러 발생 단, DELETE는 가능	INSERT           INTO V_JOINEMP VALUES (                     888                     , ‘조세오’                     , ‘인사관리부‘                     );
뷰 정의 시 사용한 쿼리 문장이 TEXT컬럼에 저장되어 있음 뷰가 재생 될 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서 결과를 보여주는 구조	SELECT                     *           FROM USER_VIEWS;

VIEW 옵션
1. OR REPLACE 옵션	생성한 뷰가 존재하면, 뷰를 갱신
2. FORCE/NOFORCE 옵션	FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성 기본값은 NOFORCE로 지정
3. FORCE/NOFORCE 옵션	FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성 기본값은 NOFORCE로 지정
4. WITH READ ONLY 옵션	뷰에 대해 조회만 가능하고, 삽입, 수정, 삭제 등을 하지 못하게 함
INLINE VIEW	일반적으로 FROM 절에 사용된 서브쿼리의 결과 화면에 별칭을 붙인 것을 말한다. FROM절에 서브쿼리를 직접 사용해도 되고, 따로 뷰를 생성 후 FROM절에서 생성한 뷰를 사용해도 됨

---

SEQUENCE

SEQUENCE	순차적으로 정수 값을 자동으로 생성하는 객체로, 자동 번호 발생기의 역할
표현식	CREATE SEQUENCE 시퀀스명
처음 발생시킬 시작 값 지정, 기본값 1	[START WITH 숫자 ]
다음 값에 대한 증가치, 기본값 1	[INCREMENT BY 숫자 ]
발생시킬 최대값 지정, 10의 27승 -1까지 가능	[MAXVALUE 숫자 | NOMAXVALUE ]
발생시킬 최소값 지정, -10의 26승	[MINVALUE 숫자 | NOMINVALUE ]
시퀀스가 최대값까지 증가를 완료하면 CYCLE은 START WITH 설정값으로 돌아가고, NOCYCLE은 에러발생	[CYCLE | NOCYCLE ]
CACHE는 메모리상에서 시퀀스값을 관리, 기본값 20	[CACHE | NOCACHE]

---

NEXTVAL / CURRVAR 사용 가능 여부

사용가능	사용불가
서브쿼리가 아닌 SELECT문	VIEW의 SELECT절
INSERT문의 SELECT절	DISTINCT 키워드가 있는 SELECT문
INSERT문의 VALUE절	GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문
UPDATE문의 SET절	SELECT, DELETE, UPDATE의 서브쿼리
	CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

시퀀스 수정 시 CREAT에 사용한 옵션을 변경할 수 있다.

단, START WITH값 변경은 불가하기 때문에 변경하려면 삭제 후 다시 생성해야 함

ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;