SQL 시퀀스를 사용해보자
SQL 시퀀스를 사용해보자 — #개발자의도구들 #SQL시퀀스 컴퓨터공학과 학사과정 중 공부한 내용을 정리하였습니다. * 본글은 PC버...
#개발자의도구들 #SQL시퀀스
컴퓨터공학과 학사과정 중 공부한 내용을 정리하였습니다.
\* 본글은 PC버전에 최적화 되어있습니다.
INDEX
- SQL 시퀀스
- 시퀀스 생성
- CURRVAL & NEXTVAL
- 시퀀스로 기본키 생성하기
- TIPS
SQL 시퀀스
보통 MYSQL에서 AUTO\_INCREMENT 옵션을 사용하여 기본키를 정의하면 자동으로 키 값을 순서대로 1씩 증가시켜주는데요. auto\_increment 옵션은 여러 유연하지 못한 특징을 가지고 있습니다. 예를들어 삭제된 번호는 재사용이 불가능 하거나, 테이블에 종속되어 여러 테이블과 공유하지 못합니다.
Orable이나 PostgreSQL에서는 이런 독립적인 번호를 발행하기 위해 시퀀스라는 테이블 독립형 객체를 가지고 있습니다. 이를 통해 유연하게 열번호 관리가 가능하며, 여러 테이블에서 하나의 시퀀스를 공유하는 등의 다양한 이점을 누릴 수 있습니다.
시퀀스 생성
Oracle
시퀀스는 정말 유연하게도 다양한 옵션들을 지원합니다.
CREATE SEQUENCE seq_name
[ INCREMENT BY n] : 시퀀스 번호의 증가치 (기본 1)
[ START WITH n] : 시퀀스 시작 번호, 기본은 1
[ MAXVALUE n] : 생성 가능한 시퀀스의 최대 값
[ MINVALUE n] : 시퀀스 번호를 순환적으로 사용하는 cycle로 지정한 경우, maxvalue에 도당한 후
새롭게 시작하는 시퀀스 값
[ CYCLE | NOCYCLE] : MAXVALUE이후 MINVALUE에 도달한 후 시퀀스의 순환적인 번호 생성여부 지정
[ CACHE | NOCACHE] : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스 개수, 기본은 20
| ✏️ EXAMPLE시작 번호 1, 증가치 1, 최대 값 2인 s\_seq 생성해보기 |
|---|
CREATE SEQUENCE s_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 2;
CURRVAL & NEXTVAL
Oracle
위에서 시퀀스는 독립된 객체라고 한 바 있습니다. 즉, 하나의 테이블이 아닌 여러 테이블에서 사용이 가능한데요. 그러면, 현재 시퀀스 번호가 몇번인지, 다음은 몇번인지를 추적하고 싶은 경우가 있을 텐데요. 이때 CURRVAL, NEXTVALUE를 통해 조회가 가능합니다.
| ✏️ SELECT s\_seq CURVAL FROM DUAL; |
|---|
현재 시퀀스 값 확인이 가능합니다.
| ✏️ SELECT s\_seq NEXTVAL FROM DUAL; |
|---|
다음 시퀀스 값 확인이 가능합니다.
시퀀스로 기본키 생성하기
oracle sql developer
이제 본격적으로 시퀀스를 사용해봅시다. 저는 다음과 같이 시퀀스를 정의하겠습니다.
CREATE SEQUENCE s_seq
INCREMENT BY 1
START WITH 1000
MAXVALUE 1005
MINVALUE 1000 -- CYCLER 설정으로 인해 MAXVALUE이후에 다시 1000으로 돌아옴
CYCLE
CACHE 5; -- CYCLE 수가 6이기 때문에 이 값보다 작은 갑스로 설정
시퀀스 생성 결과
시퀀스가 생성되면, user\_sequences 테이블에서 생성결과를 확인할 수 있습니다.
| ⌨️ 기본키로 사용하기 |
|---|
기본키로 사용하기전에 유의할점이 있는데, 현재 시퀀스를 생성만하고 사용을 한번도 안한 상태입니다. 이 경우 CURRVAL을 사용하면 아래와 같은 오류가 발생합니다.
ORA-08002: 시퀀스 S_SEQ.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다
08002. 00000 - "sequence %s.CURRVAL is not yet defined in this session"
*Cause: sequence CURRVAL has been selected before sequence NEXTVAL
*Action: select NEXTVAL from the sequence before selecting CURRVAL
이는 아직 S\_SEQ의 시퀀스를 한번도 사용하지 않았기 때문에 S\_SEQ.NEXVAL를 호출해야 본격적으로 사용할 수 있습니다. 이를 이용하여 데이터를 삽입하겠습니다.
해당 시퀀스를 학생테이블에 데이터를 삽입할때 사용해보겠습니다. (기본키에 삽입)
INSERT INTO student(studno, name)
values(s_seq.currval, '김김김')
이렇게 넣게되면 아래와 같은 메세지가 출력됩니다.
SQL 오류: ORA-08002: 시퀀스 S_SEQ.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다
08002. 00000 - "sequence %s.CURRVAL is not yet defined in this session"
*Cause: sequence CURRVAL has been selected before sequence NEXTVAL
*Action: select NEXTVAL from the sequence before selecting CURRVAL
위에서 언급한 것처럼 아직 CURVAL이 생성되지 않았기 때문입니다. 처음 생성 후 삽입할 때는 반드시 NEXTVAL를 사용합시다.
INSERT INTO student(studno, name)
values(s_seq.nextvalue, '김김김')
1000번부터 확실히 잘 들어가는 걸 볼 수 있습니다.
이제 현재 값을 조회하면 제대로 나오는 것을 볼 수 잇습니다.
TIPS
oracle sql developer
\* 일단 NEXTVAL을 시행하고 나면 자동으로 시퀀스 아이디가 생성되기 때문에, 만약 아래와 같이 다음 시퀀스 값을 조회를 두번하고 현재값을 보게되면 현재값이 계속 변합니다.
- 시퀀스를 변경할때는 ALTER MODIFY 명령어를 사용합니다.
- 단 이때, START WITH는 변경 불가능합니다.
ALTER SEQUENCE S_SEQ
MINVALUE 100;
\* SEQENCE MINAVLUE를 START WITH보다 낮게 설정이 가능한데, 이 경우 MAXVALUE까지 증가했다가 사이클로인해 다시 MINVALUE로 감소합니다.
예를들어 MAX 1006인데 MIN을 100으로 변경
1006 이후 100에서 1006까지 시퀀스를 사용할 수 있음