26일차(2). 뷰(View)와 시퀀스(Sequence)

 

View는 하나 이상의 테이블이나 다른 뷰의 데이터를 볼수 있게 하는 데이터베이스 객체 이다.

 

우리가 조회하는 Select문의 조회 결과는 일회성이다. 물론 다중 Insert문에서 처럼 Insert를 하는 값으로 일시적으로 사용할수는 있지만 그게 다이다.

 

하지만 View를 이용하면 Select문을 이용하여 조회한 값의 객체를 재사용할수 있게 가상 테이블을 생성하여 반복적인 Select문을 이용한 조회를 줄여줄수 있다.

 

뷰를 생성하는 문법이다.

 

--View 생성 문법
create or replace view 뷰이름 as 서브쿼리;

--생성한 View 삭제 문법
DROP VIEW 뷰명;

 

여기서 create or replace는 뷰가 없다면 생성을 하고 있다면 수정을 한다. or replace는 생략이 가능하다.

 

 

뷰를 하나 생성해보겠다.

 

급여를 2450이상 받는 사번, 사원명, 급여, 부서명을 조회하여 이걸 View로 만들어주겠다.

 

create or replace view emp_dept
as
select e.empno, e.ename, e.sal, d.dname
from emp e join dept d
on e.deptno = d.deptno
where e.sal >= 2450;

 

라고 작성하고 엔터를 쳤는데 오류가 났다.

 

ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

 

권한이 부족하다는 말이다. SCOTT은 뷰 생성 권한이 없나보다. 뷰 생성 권한을 부여해주러 오랜만에 SYSTEM에 접속해서 권한 부여를 해줬다.

 

--View 생성 권한 Scott에게 부여하기
grant create view to scott;

 

Grant가 성공했다고 한다. 다시 뷰를 생성했다.

 

 

뷰가 생성되었다. 뷰도 일반적인 테이블처럼 Select문으로 조회할수 있다.

 

select *
from emp_dept;

 

서브쿼리에서 조회된 데이터들이 emp_dept라는 뷰 테이블 안으로 들어간것을 확인할수가 있다.

 

이처럼 View를 생성하여 필요할때 Select 하는것이 아닌 View 가상 테이블에서 꺼내어 사용할수 있다.

 

View는 원본 데이터와 동기화 된다. 따라서 원본 데이터의 값을 수정해도 View에서 반영되고, 

 

반대로 View데이터를 수정해도 원본데이터에 반영된다.

 

7698 사원번호 BLAKE의 급여를 3000으로 수정해보겠다.

 

update emp_dept
set sal = 3000
where empno = 7698;

 

그리고 다시 뷰를 조회해보니

 

정상적으로 수정되었다. 그렇다면 원본 데이터 테이블인 EMP 테이블을 살펴보자

 

 

EMP 테이블의 전체를 조회했다. BLAKE가 3000으로 원본데이터에서도 변경되어있는것을 확인할수가 있다.

 

다시 BLAKE의 급여를 이번엔 100으로 줄여보겠다

 

update emp_dept
set sal = 100
where empno = 7698;

 

그리고 뷰를 조회해보겠다.

 

 

음... BLAKE가 사라졌다. 원본 테이블에는 있을까?

 

원본 테이블인 EMP 테이블에는 100으로 존재한다.

 

이건 바로 View를 생성해줄때 Where절로 조건을 준 급여 2450 이상에 해당되지 않아서 리스트에서 사라진것이다.

 

이처럼 View는 생성하면 그 조건을 그대로 유지하고 있다. 마지막으로 뷰를 삭제해주겠다.

 

drop view emp_dept;

 

 

삭제가 정상적으로 완료되었다.

 

시퀀스(SEQUENCE)는 자동 순번을 정해주는 객체이다.

 

EMP 테이블의 EMPNO나 DEPT테이블의 DEPTNO같은 Primary Key 중복이 불가하며 Unique한 값들을 정해줄때 시퀀스를 사용하여 자동으로 순번을 정해주면 개발자가 수동으로 일일히 입력해주지 않아도 순차적으로 증감하여 자동으로 값이 지정된다.

 

예로 우리가 인터넷상 홈페이지나 쇼핑몰에 회원가입하면 우리는 회원번호가 내부적으로 정해질것이다.

 

이 회원번호는 개발자가 하나하나 지정하는것이 아닌, 바로 Sequence 처리가 되어 우리는 가입만 하고 회원번호는

 

자동으로 1씩 증가하여 부여되는것이라고 할수 있겠다

 

시퀀스 생성 문법은 다음과 같다.

 

--SEQUENCE 생성 문법
CREATE SEQUENCE 시퀀스명
[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE  최저값] 
[MAXVALUE  최고값]
[CYCLE | NOCYCLE]  --최대값에 도달했을때 1부터 다시 시작할지 안할지
[CACHE | NOCACHE]; --캐시의 사용여부, 캐시를 사용할시 미리 값을 할당받아놓아서 속도가 빠르며 동시사용자가 많을경우 유리

 

여기서 [] 괄호안에 있는 조건들은 생략이 가능하다.

 

아무런 조건을 주지 않고 emp_empno_seq라는 시퀀스를 하나 만들어보겠다.

 

Create sequence emp_empno_seq;

 

 

시퀀스는 조회도 가능한데

 

SELECT 문으로 조회 가능하다

 

--USER가 보유한 SEQUENCE 조회 방법
select *
from user_sequences;

 

생성한 시퀀스의 값들을 살펴보면 따로 지정해주지 않았지만 최소값이 1, 최대값이 9999999999 거의 무한이라고 봐도 무방하다. 그리고 1씩 증가하며, 현재 값은 1이다.

 

참고로 시퀀스명뒤에 _SEQ는 관례적으로 많이 붙이는거니 사용해주도록 하며

 

시퀀스 사용시 시퀀스명.NEXTVAL을 이용하여 사용할때마다 값을 증가시킬수 있고. 

 

현재 시퀀스의 값을 가져오려면 시퀀스명.CURRVAL을 사용하면 현재 시퀀스의 값을 알수 있다.

 

이제 이 생성한 시퀀스를 바탕으로 EMP 테이블의 사원을 몇명 추가할것이다.

 

insert into emp(empno, ename)
values(emp_empno_seq.nextval, '나얼');

insert into emp(empno, ename)
values(emp_empno_seq.nextval, '박효신');

insert into emp(empno, ename)
values(emp_empno_seq.nextval, '이수');

insert into emp(empno, ename)
values(emp_empno_seq.nextval, '김범수');

 

이렇게 4명의 사원을 추가해주겠다. 추가해준후 EMP 테이블을 살펴보면

 

1번부터 차례대로 1씩 증가하여 자동으로 순번지정되어 입력된것을 확인할수 있다.

 

여기서 유의할점은 시퀀스.nextval은 insert가 아닌 호출 될때마다 설정한 숫자씩 증가한다는것이다.

 

예를들어서 Select문에도 시퀀스는 호출할수가 있는데. 이때 호출되면 증감식에 1을 넣었다면 1이 증가해서 5가 되고

 

다시 insert 할때 호출하면 5번이 아닌 6번으로 입력된다. 이를 유의하자. 입력이 아닌 NEXTVAL로 호출만해도 1이 증가한다.

 

현재 시퀀스의 값이 얼마인지 알고싶다면 dual 테이블을 이용하여 select 해주면 된다.

--현재 SEQUENCE의 값 확인하기
select emp_empno_seq.currval
from dual;

 

다음은 시퀀스 수정을 알아보겠다.

 

시퀀스 수정은 ALTER를 이용한다.

 

--SEQUENCE 수정 문법
ALTER SEQUENCE 시퀀스명
--[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE  최저값] 
[MAXVALUE  최고값]
[CYCLE | NOCYCLE]  
[CACHE | NOCACHE];

 

 

증감규칙을 1-> 2로 변경해주고 MAX VALUE를 100으로 수정해보겠다.

 

 

--SEQUENCE 수정 문법
ALTER SEQUENCE emp_empno_seq
--[START WITH 시작번호] --시작번호(수정불가)
INCREMENT BY 2 --증감규칙
MAXVALUE  100;

 

정상적으로 잘 수정됐는지 조회해보자.

 

 

잘 수정된것을 확인할수 있다.

 

마지막으로 생성된 시퀀스를 지워보자.

 

--SEQUENCE 삭제 문법
Drop sequence emp_empno_seq;

 

실행하면 시퀀스 삭제가 완료된다.