🐧뷰(VIEW)
: 테이블을 기반으로 만들어지는 가상의 테이블 - 단순뷰와 복합뷰로 구분
◈ 뷰는 테이블의 행검색 또는 테이블 권한 설정을 간편하게 사용하기 위한 기능을 제공
◈ 단순뷰 : 하나의 테이블을 기반으로 생성되는 뷰
→ 검색뿐만 아니라 뷰를 이용한 테이블의 행 삽입,변경,삭제 가능
◈ 단순뷰 생성시 그룹함수 또는 DISTINCT 키워드를 사용한 경우 검색만 가능
◈ 복합뷰 : 다수의 테이블을 기반으로 생성되는 뷰 : 테이블 결합을 기반으로 생성하는 뷰
→ 검색만 가능
📌뷰 생성 - 서브쿼리 이용
형식)
CREATE [OR REPLACE][{FORCE|NOFORCE}] VIEW 뷰이름[(컬럼명,컬럼명,...)] AS SELECT 검색대상,검색대상,...
FROM 테이블명 [WHERE 조건식][WITH CHEK OPTION] [WITH READ ONLY]
◈ 서브쿼리의 검색결과를 이용하여 뷰 생성
◈ CREATE OR REPLACE : 동일한 이름의 뷰가 존재할 경우 기존 뷰를 삭제하고 새로운 뷰 생성
◈ FORCE : 서브쿼리의 검색결과가 없어도 강제로 뷰를 생성하기 위한 기능 제공
◈ WITH CHEK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼에 저장된
값을 변경하지 못하도록 설정하는 기능 제공
◈ WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공(단순뷰)
📘EMP 테이블에 저장된 모든 사원을 검색하여 EMP_COPY 테이블을 생성하고 검색행을 삽입 처리
CREATE TABLE EMP_COPY AS SELECT * FROM EMP;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_COPY;
📘EMP_COPY 테이블에서 부서번호가 30인 사원의 사원번호,사원이름,부서번호를 검색하여 EMP_VIEW30 뷰 생성
◈ 현재 접속 사용자는 뷰에 대한 시스템 권한이 없으므로 뷰를 생성할 경우 에러 발생 - 관리자에게 뷰에 대한 시스템 권한 요청
CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;
※ 에러 발생
◈ 시스템 관리자(SYSDBA - SYS 계정)로 접속하여 현재 접속 사용자(SCOTT)에세
뷰에 대한 시스템 권한 부여📤GRANT CREATE VIEW TO SCOTT;
◈ 시스템 관리자에게 뷰에 대한 시스템 권한을 부여 받은 후 뷰 관련 명령 사용 가능
CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;
※ 단순뷰
◈ 뷰 목록 확인 - USER_VIEWS : 뷰 정보를 제공하는 딕셔너리
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
◈ 뷰 검색 : 테이블에 저장된 행을 이용하여 검색
SELECT * FROM EMP_VIEW30;
◈ 단순뷰에 행 삽입 : 테이블에 행을 삽입 처리 - 뷰에 없는 컬럼에는 컬럼 기본값 전달되어 삽입
INSERT INTO EMP_VIEW30 VALUES(1111,'홍길동',30);
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW30;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_COPY;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─COMMIT;
📘EMP_COPY 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,입사일,급여를 검색하여 EMP_VIEW01 뷰 생성
CREATE VIEW EMP_VIEW01 AS SELECT EMPNO,ENAME,JOB,HIREDATE,SAL FROM EMP_COPY;
📘EMP_COPY 테이블에서 업무가 SALESMAN인 사원의 사원번호,사원이름,성과급를 검색하여 EMP_VIEW02 뷰 생성
CREATE VIEW EMP_VIEW02 AS SELECT EMPNO,ENAME,COMM FROM EMP_COPY WHERE JOB='SALESMAN';
◈ 뷰를 이용하여 테이블 관련 권한 설정을 하지 않고 필요한 정보를 제공
◈ EMP_COPY 테이블에 접근 권한을 없애고 뷰를 이용해 EMP_COPY 테이블의 저장된 행에서 필요한 정보만 검색하여 제공
◈ 인사부에 근무하는 사원은 EMP_VIEW01 뷰에 접근 가능한 권한 제공하여 필요한 정보 검색SELECT * FROM EMP_VIEW01;
───────────────────────────────────────
◈ 영업부에 근무하는 사원은 EMP_VIEW02 뷰에 접근 가능한 권한 제공하여 필요한 정보 검색SELECT * FROM EMP_VIEW02;
📘EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
◈ 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은
행을 서로 결합하여 검색SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
📘EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색하여 EMP_VIEW 뷰 생성
CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
※ 복합뷰
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW;
※ 뷰를 이용하여 두개이상의 테이블에서 원하는 행의 결합결과 검색
◈ EMP_VIEW30 뷰에 SAL 컬럼 추가
: 뷰 속성 변경 불가능 - 기존 뷰를 삭제하고 새로운 뷰 생성
◈ CREATE OR REPLACE VIEW 명령을 사용하여 기존 뷰를 삭제하고 새로운 뷰 생성 가능SELECT * FROM EMP_VIEW30;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─CREATE OR REPLACE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW30;
📘EMP_COPY 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서번호를 검색하여 EMP_VIEW10 뷰 생성
CREATE OR REPLACE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY WHERE DEPTNO=10;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW10;
📘EMP_VIEW10 뷰에서 사원번호가 7782인 사원의 부서번호를 20으로 변경 - EMP_COPY 테이블에 행의 컬럼값 변경
UPDATE EMP_VIEW10 SET DEPTNO=20 WHERE EMPNO=7782;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW10;
※ 테이블에 저장된 컬럼값이 변경되어 뷰에서는 미검색
◈ 롤백 처리
ROLLBACK;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW10;
📘EMP_COPY 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서번호를 검색하여 EMP_VIEW10 뷰 생성
◈ EMP_VIEW10 뷰에 WITH CHECK OPTION 기능 추가
CREATE OR REPLACE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY WHERE DEPTNO=10 WITH CHECK OPTION;
📘EMP_VIEW10 뷰에서 사원번호가 7782인 사원의 부서번호를 20으로 변경
◈ WITH CHECK OPTION 기능에 의해 뷰를 생성한 서브쿼리의 조건식에서 사용한
컬럼값을 변경할 경우 에러 발생UPDATE EMP_VIEW10 SET DEPTNO=20 WHERE EMPNO=7782;
※ 에러 발생
📘EMP_COPY 테이블에서 부서번호가 20인 사원의 사원번호,사원이름,급여,부서번호를 검색하여 EMP_VIEW20 뷰 생성
CREATE OR REPLACE VIEW EMP_VIEW20 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY WHERE DEPTNO=20;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW20;
📘EMP_VIEW20 뷰에서 사원번호가 7788인 사원정보 삭제 - EMP_COPY 테이블의 행 삭제
DELETE FROM EMP_VIEW20 WHERE EMPNO=7788;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW20;
◈ 롤백 처리
ROLLBACK;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW20;
📘EMP_COPY 테이블에서 부서번호가 20인 사원의 사원번호,사원이름,급여,부서번호를 검색하여 EMP_VIEW20 뷰 생성
◈ EMP_VIEW20 뷰에 WITH READ ONLY 기능 추가
CREATE OR REPLACE VIEW EMP_VIEW20 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_COPY WHERE DEPTNO=20 WITH READ ONLY;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW20;
📘EMP_VIEW20 뷰에서 사원번호가 7788인 사원정보 삭제
◈ WITH READ ONLY 기능에 의해 단순뷰에서 DML 명령을 실행할 경우 에러 발생
DELETE FROM EMP_VIEW20 WHERE EMPNO=7788;
※ 에러 발생
📌뷰 삭제
형식)DROP VIEW 뷰이름
◈ 뷰 목록 확인
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
◈ EMP_VIEW 뷰 삭제
DROP VIEW EMP_VIEW;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
◈ EMP_COPY 테이블 삭제 - 테이블을 삭제해도 테이블을 기반으로 생성된 뷰 미삭제
DROP TABLE EMP_COPY PURGE;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
◈ EMP_VIEW30 뷰 검색 - 뷰가 참조할 수 있는 테이블이 없는 경우 에러 발생
SELECT * FROM EMP_VIEW30;
※ 에러 발생
◈ ROWNUM : 검색행에 순차적으로 행번호를 제공하는 키워드
SELECT EMPNO,ENAME,SAL FROM EMP;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP;
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색
SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색 - 행번호를 제공받아 검색
SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 EMP_VIEW 뷰 생성
CREATE OR REPLACE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT * FROM EMP_VIEW;
📘EMP_VIEW 뷰의 모든 행을 행번호와 같이 검색
SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP_VIEW;
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
◈ 테이블 또는 뷰의 모든 컬럼을 다른 검색대상과 같이 사용할 경우 [테이블명.*] 또는 [뷰.*] 형식으로 표현하여 검색 가능SELECT ROWNUM,EMP_VIEW.* FROM EMP_VIEW;
📌인라인 뷰(INLINE VIEW)
: SELECT 명령에서 FROM의 서브쿼리에 의해 일시적으로 생성되어 사용되는 뷰
◈ 뷰 관련 시스템 권한이 없어도 인라인 뷰를 생성하여 사용 가능
형식)SELECT 검색대상,... FROM (SELECT 검색대상,... FROM 테이블명 [WHERE 조건식])SELECT * FROM (SELECT EMPNO,ENAME,SAL FROM EMP);
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─SELECT EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC);
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 행번호를 제공받아 검색
◈ 서브쿼리로 정렬된 인라인 뷰를 생성하여 행번호 제공받아 검색
SELECT ROWNUM,EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC);
◈ 인라인 뷰에 별칭 설정 가능 - 인라인 뷰의 별칭을 사용하여 인라인 뷰의 모든 컬럼값 검색을 표현
SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP;
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 행번호를 제공받아 검색하고
◈ 행번호가 5보다 작거나 같은 행 검색 - ROWNUM 키워드를 WHERE의 조건식에서 사용하여 검색
SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE ROWNUM<=5;
📘EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 행번호를 제공받아 검색하고 행번호가 10인 행 검색
◈ ROWNUM 키워드는 인라인 뷰의 검색행에 순자적으로 부여되는 행번호로 처리행 전에 행번호를 미리 부여받지 못해 비교 검색 불가능
◈ ROWNUM 키워드를 WHERE의 조건식에서 사용할 때 < 또는 <= 연산자는 사용 가능하지만 > 또는 >=, = 연산자를 이용한 연산식 사용 불가능SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE ROWNUM=10;
※ 검색 실패
◈ ROWNUM 키워드를 포함한 인라인 뷰를 생성하고 ROWNUM 키워드에 컬럼 별칭을
설정하여 조건식에 사용해 검색 가능SELECT * FROM (SELECT ROWNUM RN,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP) WHERE RN=10;
🌟🌟EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 행번호를 제공받아 검색하고
◈ 행번호가 6~10 범위에 포함되는 행 검색 - 페이징 처리시 사용하는 SELECT 명령
◈ 페이징 처리 : 하나의 웹페이지에 원하는 범위의 행만 검색하여 출력하는 기능
SELECT FROM (SELECT ROWNUM RN,TEMP. FROM
(SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP)
WHERE RN BETWEEN 6 AND 10;
