Oracle, View 란? (뷰 형식, 제약조건, 뷰-인라인, Top N분석)

VIEW

 

다른 테이블이나 뷰에 포함됨 맞춤 표현(Virtual Table)

 

join하는 테이블의 수가 늘어나거나 질의문이 길고 복잡해지면 작성이 어려워지고 유지보수가 어려워질 수 있어 스크립트를 만들어두거나 stored query를 사용해서 데이터베이스 서버에 저장해두면 필요할 때마다 호출해서 사용할 수 있다.

 

특징

 

뷰와 테이블의 차이점 : 뷰는 실제로 데이터를 저장하지 않는다.

베이스테이블 : 뷰를 통해 보여지는 실제테이블

선택적인 정보만 제공 가능하다.

 

CREATE [OR REPLACE][FORCE][NOFORCE] VIEW 뷰이름 [(ALIAS [,ALIAS, ... ])]
AS 서브쿼리
[WITH CHECK OPTION [CONSTRAINT 제약조건이름]]
[WITH READ ONLY [CONSTRAINT 제약조건이름]]

 

VIEW 형식		특징
Create or replace		지정한 이름의 뷰가 없으면 새로 생성, 동일 이름이 있으면 수정
force | noforce	force	베이스테이블이 존재하는 경우에만 뷰 생성 가능
	noforce	베이스테이블이 존재하지 않아도 뷰 생성 가능
	alias	뷰에서 생성할 표현식 이름(테이블의 컬럼 이름 의미) 생략하면 서브쿼리의 이름 적용 alias의 개수는 서브 쿼리의 개수와 동일해야 한다.
서브쿼리		뷰에서 표현하는 데이터를 생성하는 select 구문
제약조건	width check option	뷰를 통해 접근 가능한 데이터에 대해서만 DML 작업 가능
	with read only	뷰를 통해 DML 작업 안됨 제약조건으로 간주되므로 별도의 이름 지정 가능

 

예시

CREATE OR REPLACE VIEW V_VIEW
AS SELECT ID, NAME, SALARY, DEPT_ID 
			FROM EMPLOYEES 
			WHERE DEPT_ID = 90;

 

V_VIEW는 EMPLOYEES 테이블의 ID, NAME, SALARY, DEPT_ID를 가져오는데 DEPT_ID가 90인 값만 가져온다.

 

OR REPLACE : 이름이 있는 뷰테이블 이라면 수정하고, 없다면 생성해라

 

뷰 테이블 내용을 UPDATE 하면 원본 파일도 같이 변경된다.

---

뷰 제약조건 달기

CREATE OR REPLACE VIEW V_VIEW
AS SELECT ID, NAME, JOB_ID
FROM EMPLOYYES
WHERE JOB_ID = 'IT_PTOG'
WITH READ ONLY;

 

뷰를 삭제하면 에러발생된다.

delete from v_view5;

SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
42399.0000 - "cannot perform a DML operation on a read-only view

---

뷰 인라인(inline) 개념

 

별칭을 사용하는 서브쿼리를 말한다.

일반적으로 from절에서 사용한다.

CREATE OR REPLACE VIEW V_VIEW("부서ID", "부서평균")
AS SELECT NVL(DEPARTEMENT_ID, 5000),
			ROUND(AVG(SALARY), -3)
	FROM EMPLOYEES
	GROUP BY DEPARTMENT_ID
	ORDER BY DEPARTMENT_ID ASC;

 

SELECT 부서ID, 부서평균
FROM(SELECT NVL(DEPARTMENT_ID, 5000) "부서ID",
			ROUND(AVG(SALARY),-3) "부서평균"
	FROM EMPLOYEES
	GROUP BY DEPARTMENT_ID
	ORDER BY DEPARTMENT_ID ASC);

---

뷰 - Top N 분석

 

조건에 맞는 최상위(최하위) 레코드를 N개 식별해야 하는 경우에 사용한다.

 

Top N 분석 원리

 

원하는 순서대로 정렬하고

rownum 이라는 가상의 컬럼을 이용하여 순서대로 순번을 부여한다.

부여된 순번을 이용하여 필요한 수만큼 식별한다.

rownum 값으로 특정 행을 선택할 수는 없다.

 

SALARY의 순위를 내림차순 정렬 했을 때, 3개씩 묶어서 2번째 그룹을 출력. (4,5,6 순위 사원 출력 : 페이징 처리 기법)

 

1.

SELECT * FROM
(SELECT ROWNUM, CEIL(ROWNUM/3) AS PAGE, TT.* FROM
(SELECT NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC) TT
) WHERE PAGE = 2;

 

2.

SELECT * FROM
(SELECT ROWNUM RN, TT.* FROM
(SELECT NAME, NVL(SALARY,0) AS SALARY FROM EMPLOYEES ORDER BY 2 DESC) TT
) WHERE RN>=4 AND RN<=6;