[SQL-Oracle]뷰(VIEW) - 단순뷰, 복합뷰, 인라인뷰

*실제로 테이블에 있는 모든 행들을 보면 안되는 경우가 많다.

이 때, 불필요하거나 보안상 중요한 내용들이 보여지지 않고 필요한 부분만 볼 수 있도록 하는 기능이 바로  VIEW다 

 

⚫뷰(VIEW) : 테이블을 기반으로 만들어지는 가상의 테이블 - 단순뷰와 복합뷰로 구분
--뷰는 테이블에 저장된 행에서 필요한 행 또는 열을 쉽게 제공하기 위해 생성하여 사용

   = > 불필요한 값 또는 보안상 중요한 값은 배제
--단순뷰 : 하나의 테이블을 기반으로 생성된 뷰 - 뷰를 이용한 테이블의 행 검색뿐만 아니라 행 삽입,삭제,변경 가능
   => 단순뷰 생성시 그룹함수 또는 DISTINCT 키워드를 사용한 경우 테이블의 행 검색만 가능
--복합뷰 : 다수의 테이블을 기반으로 생성된 뷰 - 두 개이상의 테이블의 행을 결합하여 생성된 뷰로 테이블의 행 검색만 가능

🔘뷰 생성 - 서브쿼리의 검색결과를 이용하여 뷰 생성
형식) CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW 뷰이름[(컬럼명,컬럼명,...)]
      AS SELECT 검색대상,... FROM 테이블명 [WHERE 조건식] [WITH CHECK OPTION] [WITH READ ONLY]
--CREATE OR REPLACE : 동일한 이름의 뷰가 있는 경우 기존 뷰를 삭제하고 뷰 생성
--FORCE : 서브쿼리로 검색된 행이 없어도 강제로 뷰를 생성하기 위한 기능 제공
--WITH CHECK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼값을 변경하지 못하도록 방지하는 기능 제공
--WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공 - 단순뷰

 

--테이블 생성
--DEPT 테이블의 모든 행(부서정보)를 검색하여 DEPT_COPY 테이블 생성하여 검색행 삽입 처리
CREATE TABLE DEPT_COPY AS SELECT * FROM DEPT;
DESC DEPT_COPY;
SELECT * FROM DEPT_COPY;

--DEPT_COPY 테이블의 모든 행을 검색하여 DEPT_VIEW 뷰 생성
--CREATE VIEW 시스템 권한이 현재 사용자에게 없으므로 CREATE VIEW 명령을 실행되도록 전달하여 에러 발생
CREATE VIEW DEPT_VIEW AS SELECT * FROM DEPT_COPY;--권한이 충분하지 않아 에러 발생

 

=> SCOTT 계정에는 CRATE VIEW  권한이 없어, 권한을 부여받기 전에는 CREATE VIEW를 써도 에러 발생함

 

--시스템 관리자(SYSDBA - SYS 계정)로 접속하여 현재 접속 사용자(SCOTT)에게 CREATE VIEW 시스템 권한 부여

▶️아래 그림과 같이 관리자 계정으로 접속한 세션을 열어서 거기에서 GRANT CREATE VIEW TO SCOTT; 라고 작성해줌.

그러면 권한이 부여됨.

 

권한 부여 후, 다시 생성 시도

 

1) 단순뷰 생성

--CREATE VIEW 시스템 권한이 부여된 후 CREATE VIEW 명령을 전달하여 실행 가능
--하나의 테이블을 기반으로 생성된 단순뷰
CREATE VIEW DEPT_VIEW AS SELECT * FROM DEPT_COPY;

--뷰 확인 - USER_VIEWS : 뷰 정보를 제공하는 딕셔너리
--아래 둘 중 하나를 사용해 확인할 수 있음
DESC USER_VIEWS;
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

--뷰의 행 검색 - 테이블의 행 검색하는 것과 동일
SELECT * FROM DEPT_VIEW;

--단순뷰는 행에 대한 삽입,변경,삭제 가능 - 뷰를 이용하여 테이블에 행 삽입,변경,삭제와 동일
--DEPT_VIEW 뷰에 행 삽입한 것은 DEPT_COPY 테이블에 행을 삽입한 것과 동일 - 생략된 컬럼에는 기본값 저장
INSERT INTO DEPT_VIEW(DEPTNO,DNAME) VALUES(50,'총무부');
SELECT * FROM DEPT_VIEW;
SELECT * FROM DEPT_COPY;

 

2) 복합뷰 생성

--EMP 테이블과 DEPT 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서이름을 검색하여 EMP_VIEW 뷰 생성
--서브쿼리로 2개 이상의 테이블을 결합하여 뷰 생성 - 복합뷰
CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE EMP.DEPTNO=10;
SELECT * FROM EMP_VIEW;

--EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름을 검색하여 EMP_VIEW 뷰 생성
--CREATE OR REPLACE VIEW 명령을 사용하면 기존 뷰가 있는 경우 뷰를 삭제하고 생성 - 뷰 변경 처리
CREATE OR REPLACE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

 

 

🔸권한없이 CREATE VIEW 할 수 있음 - 인라인뷰

--뷰를 생성하지 않고 SELECT 명령의 FROM에서 서브쿼리로 뷰를 생성하여 사용 가능 - 인라인뷰(INLINE VIEW)
--CREATE VIEW 시스템 권한이 없어도 사용 가능
SELECT * FROM (SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO);

 

 

🔘 뷰 삭제
형식) DROP VIEW 뷰이름

--EMP_VIEW 뷰 삭제
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
DROP VIEW EMP_VIEW;