Oracle序列管理-优快云博客

序列：数据库对象之一。从指定整数开始，按照特定的步长增加，从而获取新的整数。

【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;

oracle序列