[SQL] 오라클 시퀀스(sequence)

oracle sequence

지정된 범위에서 일정한 값을 순서대로 하나씩 뽑아서 사용할 수 있게 하는 데이터베이스 객체
주로 primary key로 설정된 number 타입의 컬럼의 값을 자동으로 불러오기 위해 사용한다

- 1부터 시작하여 999999999까지 1씩 증가하는 숫자를 자동으로 관리하는 시퀀스

create sequence member2_seq
start with 1
maxvalue 999999999
increment by 1
nocache
nocycle;

for(int i = 1; i <= 999999999; i++) {
...
}

 

 

drop sequence member2_seq;
drop table member2;

create sequence member2_seq
	start with 1000
	maxvalue 99999999
	increment by 1
	nocache
	nocycle;


create table member2 (
	idx		number		default member2_seq.nextval primary key,
	userid		varchar2(100)	unique not null,
	userpw		varchar2(255)	not null,
	username	varchar2(100)	not null,
	joinDate	date		default sysdate,
	gender		varchar2(10)	check(gender in('남성', '여성')),
	profileImg	varchar2(255)	
);

-- 시퀀스의 현재 값을 호출한다 (증가시키지 않는다)
select member2_seq.currval from dual;

-- 시퀀스의 다음 값을 호출한다 (증가시킨다)
select member2_seq.nextval from dual;

-- idx가 1005 1006 1007로 증가하는 것을 확인할 수 있다
insert into member2 (userid, userpw, username, gender) values ('test', '1234', '테스트', '남성');
insert into member2 (userid, userpw, username, gender) values ('test2', '1234', '테스트', '여성');
insert into member2 (userid, userpw, username, gender) values ('test3', '1234', '테스트', '남성');

select * from member2 order by idx;

commit;