1.创建序列
CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
例:CREATE SEQUENCE dept_deq INCREMENT BY 1 START BY 91 MAXVALUE 100 NOCACHE NOCYCLE;
2.确认序列
SELECT sequence_name,min_value,max_value,increment_by,last_number
FROM user_sequences;
//last_number列显示了下一个有效的序列值
3.使用序列
例:插入一条新的部门记录,部门在hancheng,名称为marketing
INSERT INTO dept(deptno,dname,loc)
VALUES (dept_seq.nextval,'marketing','hancheng');
4.查看序列dept_seq的当前值
SELECT dept_seq.CURRVAL
FROM dual;
5.修改序列
ALTER SEQUENCE dept_seq
INCREMENT BY 1
MAXVALUE 9999
NOCACHE
NOCYCLE;
6.删除序列
DROP SEQUENCE dept_seq;
练习
1.创建一个序列,开始值为1,最大值为1000,增长值为1,不循环
CREATE SEQUENCE seq_test
INCREMENT BY 1
START WITH 1
MAXVALUE 1000
NOCYCLE;
2.插入一条新的部门记录到dept表,部门在BEIJING名称为neusoft,部门号为创建的序列值
INSERT INTO dept
VALUES (seq_test.NEXTVAL,'neusoft','BEIJING');
3.查看当前的序列值,以及下一个值
SELECT seq_test.CURRVAL
FROM dual;
SELECT sequence_name,last_number
FROM user_sequences
WHERE sequence_name='SEQ_TEST';
4.修改序列,设定最大值为2000,增长值为2,可以缓存10
ALTER SEQUENCE seq_test
INCREMENT BY 2
MAXVALUE 200
CACHE 10;
5.删除创建的序列
DROP SEQUENCE seq_test;