데이터베이스 - 뷰 객체

뷰

뷰는 실제 데이터가 저장되는 곳은 아니고, 바라보는 창이다. 뷰는 내부에 데이터를 저장하고 있지 않지만 기존 테이블에 기초한 논리적 테이블이다. 복잡한 쿼리를 단순하게 만들기 위해 select문을 이름 있게 저장하는 것이라고 생각하면 보다 쉽게 뷰를 다룰 수 있을 것이다.

뷰를 사용하는 목적은 보여주고 싶은 데이터만을 추출하여 보여줌으로써 데이터 접근 제한을 둘 수 있다. 게다가 복잡한 쿼리문이나 조인문의 경우 뷰를 통해 단순하게 바꾸고 사용할 수 있다.

뷰에는 두 종류가 있는데, 단순 뷰와 복합 뷰이다. 이는 select문이 얼마나 포함되었는가를 기준으로 나눈 것뿐이다.

뷰 생성하기

이제 뷰를 생성해보겠다. 뷰의 생성 문법은 다음과 같다. create문을 사용하여 서브 쿼리에 들어간 select문에 이름을 정해준다고 생각하면 된다.

실제로 데이터베이스 서버에서 뷰를 생성해보자.

뷰 생성 권한 주기

그런데 생성에 앞서 한 가지 문제가 있다.

실제로 뷰 생성 create문을 실행하면 다음과 같이 권한이 불충분하다는 메시지가 나타난다. 이는 scott이라는 사용자가 sys로부터 view 생성 권한을 받지 않았기 때문이다.

이제 system으로 접속하여 scott에게 view 생성 권한을 주도록하자.

우리가 처음에 오라클 데이터베이스를 설치할 때 system의 비밀번호를 123456으로 설정하였다. connect(conn)로 접속하여 권한을 주도록 하자.

grant 명령어는 아직 배우지 않았지만 뒤에서 다루도록 할 것이다. 일단 넘어가도록 하자.

이제 scott에게 view 생성 권한이 생겼다. 이제 scott에 다시 연결하여 view를 생성해보자.

이제 뷰가 제대로 생성되고 있는 것을 확인할 수 있다. 그리고 뷰는 물리 데이터는 아니지만 select문이나 desc문을 사용할 수 있다.

이를 select문을 통해 출력하면 테이블의 형태로 출력된다. 즉 실제로 저장된 데이터는 아니지만 사실상 테이블처럼 사용할 수 있다.

한 번 더 만들어보도록 하자. 이 때 우리가 원하는 칼럼 명으로 바꿀 수도 있다.

user_views

view와 관련된 딕셔너리 뷰는 user_views이다. 이를 통해 view에 대한 정보를 알 수 있다.

이 중에서 view_name과 text에 대한 정보를 검색해보자.

view이름은 우리가 정의한 이름이 출력되며, text에는 view를 만들 때 사용한 select문이 출력된다.

뷰를 생성할 때 create를 replace와 함께 사용하는데, 이는 view가 존재하지 않을 경우는 생성하고, 존재할 경우에는 대체하도록 한다. 그리고 칼럼 명을 다음처럼 한꺼번에 선언할 수도 있다.

일단 뷰를 생성하고 호출해보자.

그리고 동일한 이름의 뷰를 다시 생성해보겠다.

테이블에서 볼 수 있듯이 동일한 테이블에 대해 동일한 객체를 참조했다는 오류 메시지 없이 대체되고 있다.

그리고 이렇게 생성한 뷰에 대해서 select문으로 검색할 때 다시 where절을 사용할 수 있다. 이는 select문에서 and를 통해 조건절이 중첩되는 것과 동일하게 동작한다.

이번엔 select문을 통해서 tab을 검색해보자.

위에서 볼 수 있듯이 테이블뿐만 아니라 뷰에 대한 정보까지도 출력한다. 즉 tab에는 테이블에 대한 정보뿐만 아니라 뷰에 대한 정보를 담고 있다고 생각하면 된다. 그리고 이 때 view에 대한 정보만을 가져오는 것도 가능하다.

조건절에 tabtype은 view를 추가하면, view타입에 대한 객체정보만을 가져온다.

좀 더 복잡한 뷰를 생성해보도록 하자. 뷰를 조인을 이용해서도 작성할 수 있다. 부서별 직원 월급 평균을 구하는 뷰를 생성해보도록 하자.

다음처럼 부서별 평균 임금 정보가 제대로 출력되고 있다.

뷰의 삭제

이번에는 우리가 만들었던 뷰를 삭제해보도록 하겠다. 간단하게 drop view view이름을 통해 명령을 수행하면 된다.

인라인 뷰

from 절에 서브쿼리를 인라인 뷰라고 말하며 이에 대해 별칭을 부여할 수 있다. 그리고 서브쿼리의 결과가 메인 쿼리에서 참조된다.

지금까지는 뷰를 만들고, 이를 select문으로 호출하여 내용을 검색하였다.

이번에는 메인 쿼리에서 내용을 호출하고, 호출할 내용을 서브쿼리(인라인 뷰)에서 정해보도록 하자. 다음 예시처럼 각 부서의 최대 임금을 검색해보자.

인라인 뷰는 조인에 대해서도 수행할 수 있다.

top - n 쿼리

top-n 쿼리란 칼럼 값 중에서 가장 큰 값, 가장 작은 값을 n개 검색하는 경우 주로 사용한다.

임금을 기준으로 가장 높은 순으로 5명을 검색해보겠다. 이에 앞서 먼저 임금 정보를 검색하겠다.

이렇게 테이블을 만들 때 사실 테이블의 행번호가 함께 생성된다.

행번호까지 한꺼번에 출력해보자.

행번호는 rownum이라는 칼럼에 저장되어 있다. 따라서 우리가 만든 서브쿼리로부터 rownum을 가져오면 위와 같이 행번호를 함께 출력할 수 있다.

rownum을 응용하여 사용하는 것이 top-n 쿼리이다. 이제 행번호를 사용하여 월급 순위 상위 5명을 검색해보겠다. 조건절에서 행번호를 통해 5명의 데이터만을 받아오도록 하였다. 그리고 해당 테이블은 내림차순으로 정렬되어 있기 때문에 순서대로 원하는 개수만큼의 데이터만을 가져올 수 있게 된다.

문제

급여가 가장 작은 5명을 검색하기.