가상 테이블인 뷰

-- 가상 테이블인 '뷰'

-- 기본 테이블 생성하기

DROP TABLE DEPT_COPY;

CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPT;

DROP TABLE EMP_COPY;

CREATE TABLE EMP_COPY
AS
SELECT * FROM EMP;

SELECT * FROM DEPT_COPY;

SELECT * FROM EMP_COPY;

-- 뷰 정의하기

CREATE OR REPLACE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30; -- "insufficient privileges"

SELECT * FROM EMP_VIEW30;

-- 단순 뷰의 컬럼에 별칭 부여하기

DESC EMP_VIEW30; -- 기본 테이블 EMP_COPY의 서브커리문

CREATE OR REPLACE VIEW EMP_VIEW(사원번호, 사원명, 부서번호)
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP_COPY;

SELECT * FROM EMP_VIEW;

SELECT * FROM EMP_VIEW WHERE 부서번호=30;

-- 복합 뷰 만들기

CREATE OR REPLACE 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
ORDER BY EMPNO DESC;

SELECT * FROM EMP_VIEW_DEPT;

-- 뷰 삭제

SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;

SELECT * FROM EMP_VIEW;

DROP VIEW EMP_VIEW;

-- OR REPLACE 옵션

CREATE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30; -- "name is already used by an existing object"

CREATE OR REPLACE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;

SELECT * FROM EMP_VIEW30;

-- FROCE 옵션으로 기본 테이블 없이 뷰 생성하기

DESC EMPLOYEES;

CREATE OR REPLACE VIEW EMPLOYEES_VIEW
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMPLOYEES
WHERE DEPTNO=30; -- "table or view does not exist"

CREATE OR REPLACE FORCE VIEW EMPLOYEES_VIEW
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMPLOYEES
WHERE DEPTNO=30; -- 경고: 컴파일 오류와 함께 뷰가 생성되었습니다.

SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;

CREATE OR REPLACE NOFORCE VIEW EMPLOYEES_VIEW
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMPLOYEES
WHERE DEPTNO=30; -- NOFORCE는 생략한 것과 같음

-- 조건 컬럼값을 변경하지 못하게 하는 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; -- view WITH CHECK OPTION where-clause violation

SELECT * FROM VIEW_CHK30;

-- 기본 테이블 변경을 막는 WITH READ ONLY 옵션 WITH CHECK OPTION 비교

DROP TABLE EMP_COPY;

CREATE TABLE EMP_COPY
AS
SELECT * FROM EMP;

SELECT * FROM EMP_COPY;

CREATE OR REPLACE VIEW VIEW_CHK30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH CHECK OPTION;

SELECT * FROM VIEW_CHK30;

UPDATE VIEW_CHK30
SET DEPTNO=20
WHERE SAL <= 1200; -- view WITH CHECK OPTION where-clause violation

UPDATE VIEW_CHK30
SET COMM=1000; -- 6개 행 이(가) 업데이트되었습니다.

CREATE OR REPLACE VIEW VIEW_READ30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH READ ONLY;

UPDATE VIEW_READ30
SET COMM=1000; -- "cannot perform a DML operation on a read-only view"

 

-- ROWNUM 컬럼 성격 파악하기

SELECT * FROM EMP;

SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP;

SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE; -- ROWNUM은 테이블에 데이터가 들어온 순서를 보관하는 컬럼이다.

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

SELECT * FROM EMP ORDER BY SAL DESC;

SELECT ROWNUM, ENAME, SAL FROM EMP ORDER BY SAL DESC;

SELECT ROWNUM RNUM, ENAME, SAL FROM
(SELECT * FROM EMP ORDER BY SAL DESC); -- 새로운 테이블을 만들었기 때문에 들어온 순서가 새로 작성됨

-- 급여(SAL)를 많이 받는 6~10번째 사원을 출력하세요.
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;

-- 급여가 가장 낮은 TOP3 사원명과 급여를 출력하세요.
SELECT ROWNUM, RNUM, ENAME, SAL FROM
(SELECT ROWNUM RNUM, ENAME, SAL FROM
(SELECT * FROM EMP ORDER BY SAL ASC))
WHERE RNUM BETWEEN 10 AND 12;

-- 예제

-- 사원 테이블(EMP)에서 가장 최근에 입사한 사원들중에 3~5번째의 사번과 사원명을 출력하는 SQL문을 작성하세요.

SELECT ROWNUM, RNUM, EMPNO, ENAME FROM
(SELECT ROWNUM RNUM, EMPNO, ENAME FROM
(SELECT * FROM EMP ORDER BY HIREDATE DESC))
WHERE RNUM BETWEEN 3 AND 5;