[DB] 뷰 VIEW table

뷰 VIEW

가상테이블이다. 즉, 실제 존재하진 않지만 실제 테이블과 똑같이 사용할 수 있다.

주로 보안문제로 사용한다. 보여주고자 하는 정보만 빼서 보여주는 것이다. 

SELECT / INSERT / DELETE / UPDATE 가능하나, 권한이나 조건에 따라 다름

복잡한 쿼리문을 (조인/서브 쿼리 등) 미리 뷰로 만들어 사용하면 간단하게 검색 가능 - 이때는 INSERT/ UPDATE/ DELETE 안됨 ) 

데이터의 액세스를 제한 - 보안위해 사용.

생성

CREATE VIEW 뷰이름

AS 뷰 내용

CREATE VIEW 뷰이름(컬럼명, 컬럼명 ... )

AS 뷰 내용

수정

CREATE OR REPLACE VIEW 뷰이름

AS 뷰 내용

삭제 

DROP VIEW 뷰이름

EX) 

1. 보안적 측면의 뷰 

-- 뷰를 생성한다(emp테이블 모든 컬럼과 모든 레코드를 가지고 온다.)

  CREATE VIEW v_emp
  AS
  SELECT * FROM EMP ;

  SELECT * FROM emp; ---테이블
  SELECT * FROM v_emp; --뷰

-- 뷰와 테이블 각각 update실시
  UPDATE v_emp SET ename='소녀시대' WHERE empno=7782; --뷰수정 , 가리키고 있는 테이블의 데이터도  같이 수정됨
  UPDATE emp SET ename='카라' WHERE empno ='7654'; --테이블수정

  * 결론 : 뷰를 만들때 모든 컬럼과 모든 레코드를 가져와서 작성하면 뷰나 테이블에 INSERT , UPDATE, DELETE 기능이 적용된다.
          뷰에 조작(DML)하여도 실제 테이블을 조작하는 것이다.

--뷰를 만들때 특정 컬럼만 선택하여 만든다.
  CREATE OR REPLACE VIEW v_emp
  AS
  SELECT empno, ename, job FROM emp;

--변경된 뷰에 insert 해본다.
  INSERT INTO v_emp VALUES('8888', '장동건', 'CLERK');

-- 뷰를 만들때 not null컬럼을 제외하고 뷰를 만들어본후 뷰에  insert를 해본다.
   CREATE TABLE userlist(
     id varchar2(20) PRIMARY KEY,
     name varchar2(20) NOT NULL,
     age NUMBER(3) NOT NULL,
     addr varchar2(50)
     );

  INSERT INTO userlist VALUES ('jang', '장희정', 20, '서울시 청담동');
  INSERT INTO userlist VALUES ('javaking', '자바킹', 25, '서울시 구로');
  INSERT INTO userlist VALUES ('javamaster', '자바마스터', 30, '관악구');

  SELECT * FROM userlist;
-- userlist  테이블을 이용해서 뷰를 만들어본다. (not null 컬럼을 제외하고)

  CREATE VIEW v_userlist
  AS
  SELECT id, name FROM userlist; --- not null 컬럼인 age는 생략함;

-- 검색
  SELECT * FROM userlist;
  SELECT * FROM v_userlist;

-- 뷰에 insert 해본다.
   INSERT INTO v_userlist VALUES('aa', '에이야'); 
   -- 오류발생(실제 userlist에 insert되는데 not null인 age값에 null들어감)
   -- ORA-01400: NULL을 ("SCOTT"."USERLIST"."AGE") 안에 삽입할 수 없습니다

   *결론 : 뷰를 만들때 NOT NULL 컬럼을 뷰로 만들지 않은 경우 뷰에 INSERT 권한 없다.

-- 뷰를 만들때 조건을 주어 특정 레코드만 뷰를 만들어 본다.
   CREATE OR REPLACE VIEW v_emp(사원번호, 사원이름, 담당업무)
   AS
   SELECT empno, ename, job FROM emp WHERE job = 'SALESMAN'  ;

   CREATE OR REPLACE VIEW v_emp(사원번호, 사원이름, 담당업무)
   AS
   SELECT empno, ename, job FROM emp WHERE job = 'MANAGER'  OR job = 'SALESMAN';

   SELECT * FROM emp;
   SELECT * FROM v_emp;

-- 뷰에 insert를 해본다. (담당업무에 samleman 아닌 다른 담당 업무를 insert 한다);

  INSERT INTO v_emp VALUES('8282','빅뱅','경리'); -- 삽입은 성공, 뷰에는 보이지 않는다.

  UPDATE v_emp SET 담당업무 = '총무부' WHERE 사원번호 = '7654'  -- 성공(뷰에서 보이지 않는다);

  *결론 : job이 'SALESMAN' 인 레코드만 모인 뷰에서 'SALESMAN'이외의 값을 INSERT 했을때
아무이상 없이 삽입된다. 하지만 뷰를 검색한 결과에는 나타나지 않는다. 이는 뷰만 알고 있는 사용자 입장에서는 데이터 손실이므로 이러한 오류점이 발생하지 않도록 뷰를 생성할때 WITH CHECK OPTION 을 주어 뷰를 적성하기를 권장한다.

--다시 뷰를 수정해본다. ; (with check option 를 사용한다)
  CREATE OR REPLACE VIEW v_emp(사원번호, 사원이름, 담당업무)
  AS
  SELECT empno, ename, job FROM emp WHERE job = 'MANAGER'  OR job = 'SALESMAN'
  WITH CHECK OPTION ; --뷰를 작성할때 조건이 있는 경우 with check option을 지정한다.

  INSERT INTO v_emp VALUES('9999','김연우','가수'); -- 오류발생 ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
  INSERT INTO v_emp VALUES('9999','김연우','SALESMAN'); --성공

2. 복잡한 쿼리문 뷰로 만든 경우

ex) 급여의 평균 급여보다 많이 받는 사원의 정보를 검색한다. (서브 쿼리)
    
    --서브쿼리
    SELECT * FROM emp WHERE sal > (SELECT  avg(sal) FROM emp );

    --뷰생성
    CREATE VIEW v_empavg
    AS
    SELECT * FROM emp
    WHERE sal > (SELECT  avg(sal) FROM emp );

    --뷰검색
    SELECT * FROM v_empavg;


ex) ename이 scott인 사원의 empno, ename, job, deptno, dname를 검색한다. (JOIN)

    SELECT * FROM emp;

    SELECT empno, ename, job, emp.deptno, dname
    FROM emp JOIN dept
    ON emp.deptno = dept.deptno
    WHERE ename = 'SCOTT';


    CREATE VIEW v_join_emp
    AS
    SELECT empno, ename, job, emp.deptno, dname
    FROM emp JOIN dept
    ON emp.deptno = dept.deptno
    WHERE ename = 'SCOTT';


    --뷰 검색
    SELECT * FROM v_join_emp;

    *결론 : 뷰를 작성할 때 복잡한 쿼리문(subQuery, JOIN)이을 이용하여 만든 경우에는 뷰에 dml권한 이 없다.

 (INSERT / UPDATE / DELETE 사용 안됨) SELECT 기능만 가능.

   --뷰 삭제
   DROP VIEW v_join_emp;