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;--默认步长为2alter 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_seqstart with 21minvalue 20maxvalue 30increment by 1;select test_seq.nextval from dual;--修改循环标志位Yalter 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;