03. 테이블처럼 사용하는 뷰

뷰란?

흔히 가상테이블(virtual table)로 부르는 뷰(view)는 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻한다. SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않는다. 따라서 뷰를 SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있다. 뷰는 서브쿼리와 비슷해 보인다.

[오류메세지에 자주 등장하던 '테이블 또는 뷰가 존재하지 않습니다.' 의 뷰가 이것이다!]

 

뷰의 사용 목적

뷰와 서브쿼리를 비교한 후 '서브쿼리가 있는데 뷰를 써야하나?' 는 생각이 들수도 있지만, 뷰는 편리성과 보안성의 목적으로 사용한다.

1) 편리성 = 실무에서 사용하는 SELECT문은 공부하는 예제와 같이 짧게 몇줄로 이루어진 것도 있지만 길게는 A4용지 몇 장을 꽉 채울 정도의 분량으로 이루어진 경우도 많다. 많은 분량의 SELECT문 여러 개의 결과를 다시 조인하고 서브쿼리로 WHERE 조건식에도 사용한다면 전제 SELECT문은 훨씬 더 커질 것이다. 또한 이후 수정이 필요하거나 다른 개발자가 코드를 처음부터 파악해야 하는 경우에 적잖은 시간과 노력이 든다. 이럴 때 SQL문에서 자주 활용하는 SELECT문을 VIEW로 저장해놓고 다른 SQL문에서 활용하면 전체 SQL문의 복잡도가 완화되고 본래 목적의 메인 쿼리에 집중할 수 있다.

2) 보안성 = 테이블 내에 아무에게나 노출하기 예민한 데이터나, 담당자 외에 노출이 허용되지않는 중요한 개인정보 데이터 등이 들어있을 때 다른 사용자에게 특정 테이블의 전체 조회권한을 부여하는 것은 데이터 보안에 위협이 될 수 있으므로 주의해야 한다. 테이블의 일부 데이터 또는 조인이나 여러함수 등으로 가공을 거친 데이터만 SELECT 하는 VIEW 열람 권한을 제공하는 것이 불필요한 데이터 노출을 막을수 있기 때문에 안전하다.

 

뷰 생성

뷰는 CREATE문으로 생성할 수 있다. 뷰는 SYSTEM 계정으로 접속 후 SCOTT 계정에 뷰 생성권한을 부여해준다.

GRANT CREATE VIEW TO SCOTT; >> 권한이 부여되었습니다 메세지 출력

뷰를 하나 만들어봅시다.

CREATE VIEW VW_EMP20 AS ( SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO = 20);

VW_EMP20 뷰가 잘 생성되었는지 알아보려면 SELECT * FROM USER_VIEWS; 에서 확인하거나 접속 - SCOTT - 뷰( 테이블 아래) 로 확인할 수 있다. 

물리적 데이터는 없고, 테이블을 만든게 아니다! 

 

뷰 삭제

뷰를 삭제할 때 역시 DROP를 사용한다.

DROP VIEW VW_EMP20; 실제 데이터가 아닌 SELECT문을 저장하였으므로 뷰를 삭제해도 테이블이나 데이터가 삭제되는 것은 아니다.

 

뷰와 데이터 조작어

뷰가 SELECT문을 저장하는 객체이기 때문에 데이터 삽입, 수정, 삭제 같은 데이터 조작어 사용이 불가능할 것이라고 생각할 수 있지만, 의외로 뷰에도 데이터 조작어를 직접 사용할 수 있는 경우가 있다. 하지만 뷰를 통한 테이블 데이터 조작이 가능하려면 여러 가지 조건을 만족해야 하고, 테이블을 설계할 때 누락된 내용이 있으면 뷰를 통한 데이터 조작으로 인해 적합하지 않은 데이터가 생길 수도 있으므로 자주 사용하는 편은 아니다. 뷰의 주 목적은 물리적 데이터 저장없이 SELECT문의 저장으로 테이블의 데이터를 열람하는 것이다. 데이터를 따로 저장하는 것이 허용되는 구체화 뷰(materialized view)도 존재한다.

 

인라인 뷰를 사용한 TOP-N SQL문

CREATE문을 통해 객체로 만들어지는 뷰 외에 SQL문에서 일회성으로 만들어서 사용하는 뷰를 인라인 뷰(inline view)라고 한다. SELECT문에서 사용되는 서브쿼리, WITH절에서 미리 이름을 정의하고 하용하는 SELECT문 등이 해당한다. 이 인라인 뷰와 ROWNUM을 사용하면 ORDER BY절을 통해 정렬된 결과 중 최상위 몇 개 데이터만들 출력하는 것이 가능하다. SELECT ROWNUM, E.* FROM EMP E;

ROWNUM열은 EMP테이블에 존재하지는 않지만 ROWNUM열의 데이터가 숫자로 출력되고 있음을 확인할 수 있다.

ROWNUM은 의사 열(pseudo column)이라고 하는 특수 열이다. 의사 열은 데이터가 저장되는 실제 테이블에 존재하지는 않지만 특정 목적을 위해 테이블에 저장되어 있는 열처럼 사용 가능한 열을 뜻한다. [ 게시판 한 페이지에 몇개의 게시글이 나오게 할지 등에 사용할 수 있다 ] . ROWNUM 열 데이터 번호는 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호이다. ORDER BY절을 사용하여 내림차순 급여로 EMP테이블을 다시 조회해보자.

SELECT ROWNUM, E.* FROM EMP E ORDER BY SAL DESC;

데이터를 급여 기준으로 정렬했지만 ROWNUM은 앞서 사용한 SELECT문의 행 번호와 같은 번호로 매겨져 있다. ROWNUM은 데이터를 하나씩 추가할 때 매겨지는 번호이므로 ORDER BY절을 통해 정렬해도 유지되는 특성이 있다. 이 특성을 인라인 뷰에서 적용하면 정렬된 SELECT문의 결과 순번을 매겨서 출력할 수 있다. 서브쿼리를 인라인 뷰로 사용한 SELECT문과 WITH절의 인라인 뷰를 사용한 SELECT문의 결과는 같다.

[서브쿼리] SELECT ROWNUM, E.* FROM ( SELECT * FROM EMP E ORDER BY SAL DESC ) E;

[WITH절] WITH E AS ( SELECT * FROM EMP E ORDER BY SAL DESC ) SELECT ROWNUM, E.* FROM E;

 

마지막으로 급여가 높은 상위 3명(TOP-3)의 데이터만 출력하려면 ROWNUM을 WHERE절 조건으로 지정하면 된다. 활용 빈도가 높으니 꼭 기억해두자.

[ 서브쿼리 ] SELECT ROWNUM, E.* FROM ( SELECT * FROM EMP E ORDER BY SAL DESC ) E WHERE ROWNUM <= 3;

[ WITH절 ] WITH E AS ( SELECT * FROM EMP E ORDER BY SAL DESC ) SELECT ROWNUM, E.* FROM E WHERE ROWNUM <= 3;