14장 가상테이블 뷰_쿼리, 가상테이블, Top-n 구하기(오라클)

✔ 뷰(View)란?

뷰는 메인테이블을 내가 원하는 사용자 테이블로 보이게 끔 하는 것입니다.

 

 

✔ 뷰(View)를 사용하는 이유?

예를 들어 우리가 사용하는 사원 테이블이 있다고 가정할 때,

그 사원 테이블은 방대한 내용을 가지고 있습니다.

 

인사과, 경리과, 영업부는 각각 볼 수 있는 내용이 다르기 때문에

사용자에 맞게 내가 제공을 할 내용만 따로 빼서 뷰로 저장하여

제공할 수 있습니다.

 

 

✔ 단순 뷰(View) VS 복합 뷰(View)?

단순 뷰	복합 뷰
하나의 테이블로 생성	여러 개의 테이블로 생성
그룹 함수의 사용이 불가능	그룹 함수의 사용이 가능
DISTINCT 사용이 불가능	DISTINCT 사용이 가능
DML 사용 가능	DML 사용 불가능

 

<단순 뷰>

단순뷰

하나의 테이블에서 단순하게 뷰를 만들며, 별칭이 바로 적용됩니다.

 

 

<복합 뷰>

CREATE VIEW EMP_VIEW_DEPT AS SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO --EQUI 조인 ORDER BY EMPNO DESC;

서브 쿼리로 뷰를 만들어서 복잡한 뷰를 간단히 생성 가능합니다.

 

✔ 뷰(View)를 생성할 때 오류?

오류 보고 -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

 

이런 오류가 뜬다면?

SYSTEM 계정으로 가서

GRANT CREATE VIEW TO SCOTT;

원하는 계정으로 뷰 권한을 줘야 합니다.

그런 후 다시 실행하면

정상적으로 생성됩니다.

 

 

✔ 뷰(View) 삭제하기!

-- 뷰 삭제하기
SELECT * FROM EMP_VIEW;

DROP VIEW EMP_VIEW;

 

삭제 완료!

 

 

✔ 뷰 생성에 사용되는 다양한 옵션

no	옵션	기능	설명
1	OR REPLACE	뷰 수정	 CREATE OR REPLACE VIEW 를 사용하면 존재하지 않은 뷰이면 새로운 뷰를 생성하고 기존에 존재하는 뷰이면 그 내용을 변경한다. CREATE OR REPLACE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30; => EMP_VIEW30 이 없으면 CREATE 하고 있으면 OR REPLACE AS 이하로 변경하는 것 옵션입니다.
2	FORCE	기본 테이블 없이 뷰를 생성	 뷰를 생성하는 경우에 일반적으로 기본 테이블이 존재한다는 가정 하에서 쿼리문을 작성합니다.  극히 드물기는 하지만, 기본 테이블이 존재하지 않는 경우에도 뷰를 생성해야 할 경우가 있습니다. 이럴 경우에 사용하는 것이 FORCE  CREATE OR REPLACE FORCE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30; EMP_COPY 이 테이블이 없을때 강제로 FORCE를 사용하여 뷰를 만듭니다.
3	WITH CHECK  OPTION	뷰를 생성할 때 조건 제시에 사용된 컬럼 값을 변경 못하도록 하는 기능을 제공한다.	CREATE OR REPLACE VIEW VIEW_CHK30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO =30 WITH CHECK OPTION; 이중 UPDATE VIEW_CHK30 SET DEPTNO=20 WHERE SAL>=1200; 내가 셀러리가 1200보다 높은 사람들을 부서번호 20으로 지정하겠다고 바꾼다 라고 했을때 뷰의 내용을 바꾸지 못하게 오류가 납니다.
4	WITH READ ONLY	기본 테이블 변경을 막음	 WITH READ ONLY 옵션은 뷰를 통해서는  기본 테이블의 어떤 컬럼에 대해서도 내용을  절대 변경할 수 없도록 하는 것이다. CREATE OR REPLACE VIEW VIEW_READ30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30 WITH READ ONLY;

 

WITH READ ONLY 옵션 WITH CHECK OPTION 비교
WITH CHECK OPTION	WITH READ ONLY
해당 옵션을 사용하면 INSERT, UPDATE, DELETE를 사용할 때 WHERE절의 조건에 해당하는 데이터만 접근이 가능하다. (ex, deptno = '10')	해당 옵션을 사용하면 SELECT 만 가능하고 INSERT, UPDATE, DELETE는 불가능 하다.

 

 

✔ 뷰 사용 시 DML 주의 사항

단순 뷰인 경우 INSERT, UPDATE, DELETE가 자유로우며 (NOT NULL 칼럼 주의)
함수, UNION, GROUP BY 등을 사용한 복한 뷰인 경우 INSERT, UPDATE, DELETE가 불가능합니다.
(조인만 사용한 복합 뷰인 경우 제한적으로 가능)

 

DML 이란? 아래 링크로 확인해주세요~!

10장 DML 데이터 조작어 설명 및 개념 타파! (오라클_INSERT, UPDATE, DELETE, MERGE, 트랜잭션)

✔ ROWNUM 칼럼 성격 파악하기

ROWNUM 칼럼의 성격 : 해당 테이블에 입력된 순서를 나타냅니다.

ROWNUM은 이름순이나, 부서 넘버 순이 아닙니다~!

 

 

✔ 인라인 뷰로 구하는 TOP-N의 개념

 인라인 뷰란 메인 쿼리의 SELECT 문의 FROM 절 내부에 사용된 서브 쿼리문을 말합니다.
 우리가 지금까지 생성한 뷰는 CREATE 명령어로 뷰를 생성했지만, 인라인 뷰는 SQL 문 내부에 뷰를 정의하고 이를 테이블처럼 사용합니다.

 

-- 급여(SAL)를 많이 받는 6~열 번째 사원을 출력하기 위해서는 인라인 뷰안에 또 다른 인라인 뷰를 사용해야 합니다.
-- 또한 ROWNUM 칼럼에 별칭을 부여해야 검색이 가능합니다.

SELECT ROWNUM, RNUM, ENAME, SAL FROM
(SELECT ROWNUM RNUM, ENAME, SAL FROM
(SELECT * FROM EMP ORDER BY SAL DESC))
WHERE RNUM BETWEEN 6 AND 10;

 

<기본 테이블 = EMP>

기본 EMP 테이블

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

 

급여(SAL)를 많이 받는 6~열 번째 사원을 출력은 결과적으로 TOP N명을 구하는 것입니다.

CREATE를 하는 것 이 아니라, 라인 안에서 

SELECT ENAME, SAL
FROM 
(SELECT * FROM EMP
ORDER BY SAL DESC);

 

이렇게 해도 동일하게 출력됩니다!

 

그럼 이때 ROWNUM을지 정해주면

제일 높은 순으로 번호가 매겨지게 됩니다!!

 

즉, 이미 있는 테이블에서 가상의 테이블을 만들고

정렬을 한 뒤, ROWNUM을 매기면

 

급여가 가장 높은 순 중 6~10번째는 번호로 나오겠죠~? ㅎㅎ

 

여기의 순번 6번째부터 10번째부터 가져오는 방법은 어떻게 쓸까요?

방금 전에 만든 내용을 또 ( )로 감싸고 

 

최종적으로 내가 볼 내용을 위로 뺍니다.

그 후 맨 마지막에 조건을 걸어주면 되는 것 이죠!

 

WHERE RNUM 이 BETWEEN 6 AND10 인 목록만

RNUM과 ENAME과 SAL 이 출력 되게 됩니다~!!!!

 

보통 이 방법은 계시판을 만들 때 1페이지 2페이지 3페이지까지 보여줄 목록을 다르게 만드는 것을

페이지에 해당되는 글만 셀렉트 할때 인라인 뷰를 활용해서 정렬하고 RNUM을 새롭게 만들어서 활용하는 것입니다.

 

해당하는 페이지만 딱 가져올 때 사용하는 기능이 인라인 뷰로 구하는 TOP N입니다~!!!

 

 

---

[확인 문제]

SQL> CONN SCOTT/TIGER로 접속하여 SQL문을 작성하세요.
1. 사원 테이블(EMP)에서 가장 최근에 입사한 사원들 중에 3~5번째의 사번과 사원명을 출력하는 SQL문을 작성하세요?
<정답>

SELECT RNUM 순위, ENAME 사원명, EMPNO 사번
FROM
(SELECT ROWNUM RNUM, ENAME, HIREDATE,EMPNO
FROM 
(SELECT * FROM EMP
ORDER BY HIREDATE DESC))
WHERE RNUM BETWEEN 3 AND 5;

정답

 

 

 

 

그럼 지금까지 jju_developer였습니다~!

 

질문이나 수정사항은 언제나 댓글 부탁드립니다~!

 

좋아요 한 번씩 눌러주세요!

 

감사합니다.