序列:数据库对象之一。从指定整数开始,按照特定的步长增加,从而获取新的整数。
【1】创建序列
create sequence comployee_seq;
--查看序列对象信息
select * from user_objects where object_type='SEQUENCE' and object_name = 'COMPLOYEE_SEQ';
--结果
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
-------------- --------------- ----------- ------------------- ------- ------------ --------- ---------- ------------------------------
COMPLOYEE_SEQ 72199 SEQUENCE 2017/9/7 22 2017/9/7 22:1 2017-09-07:22:10:37 VALID N N N 1
--查看序列信息
select * from user_sequences where sequence_name='COMPLOYEE_SEQ';
--结果
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
-------------------------- ---------- ------------ ---------- ---------- ---------- -----------
COMPLOYEE_SEQ 1 1E27 1 N N 20 1
--创建序列后首次使用序列,会报错
select comployee_seq.currval from dual;
--创建序列后需要先使用nextval,然后再使用currval就可以使用了
select comployee_seq.nextval from dual;
--利用序列向t_employee表中插入数据,报错,因为序列的当前值和表中的id最大值不一致
insert into t_employee values(comployee_seq.nextval,'张三',6,'XXX');
--查看序列的当前值
select comployee_seq.currval from dual;
--解决方法:
--1.多次执行序列知道nextval达到表中id的最大值,不适合数据量太大的
--2.在创建序列的时候指定序列起始值
drop sequence comployee_seq;
【2】修改序列的属性
start with指定序列的初始值
minvalue:最小值
maxvalue:最大值
increment by:步长
cache:缓存,minvalue和maxvalue根据increment by一次循环所能获得的数。一次性读入缓存,nextval时再取出。
cycle:开启序列在minvalue和maxvalue之间循环取值
--重新创建序列并制定初始值
create sequence comployee_seq start with 13;
select comployee_seq.nextval from dual;
select comployee_seq.currval from dual;
--重新利用序列插入数据
insert into t_employee values(comployee_seq.nextval,'张三',6,'XXX');
注意:序列的minval不能大于当前值,报错下式
--修改序列的属性
alter sequence comployee_seq minvalue 12;
alter sequence comployee_seq maxvalue 200;
--最大值设为无限,即没有最大值
alter sequence comployee_seq nomaxvalue;
--修改序列的步长
create sequence test_seq;
select test_seq.nextval from dual;--默认步长为2
alter sequence test_seq increment by 5;
--查看test_seq的序列属性
select * from user_sequences where lower(sequence_name)='test_seq';
drop sequence test_seq;
create sequence test_seq
start with 21
minvalue 20
maxvalue 30
increment by 1;
select test_seq.nextval from dual;
--修改循环标志位Y
alter sequence test_seq cycle;
alter sequence test_seq cache 10;
--利用序列获得流水号
create table target(
object_id number not null primary key,
object_name varchar2(20),
object_type varchar2(20),
status varchar2(10)
);
create sequence tartget_seq;
insert into target(object_id,object_name,object_type,status)
select tartget_seq.nextval,object_name,object_type,status from user_objects;
select * from target;
select * from user_sequences where lower(sequence_name)='comployee_seq';
select * from t_employee;