oracle的序列sequence

一、序列定义

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

二、创建序列

创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下：

　　CREATE SEQUENCE 序列名

　　[INCREMENT BY n]

　　[START WITH n]

　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]

　　[{CYCLE|NOCYCLE}]

　　[{CACHE n| NOCACHE}];

  其中：

1)  INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

2)  START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

3)  MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4)  MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

5)  CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

6)  CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。

7) NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。

8) CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。

三、使用序列

调用NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，即用以下方式调用:

序列名.NEXTVAL

CURRVAL用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用NEXTVAL产生过序列的下一个值，先引用CURRVAL没有意义。调用CURRVAL的方法同上，要指出序列名，即用以下方式调用:

序列名.CURRVAL

四、多种序列的创建和使用

（1）最简单的序列创建：

CREATE SEQUENCE seq_test;

执行这个创建语句会在数据库中产生一个名字为seq_test的序列对象,其它的参数都会默认使用默认值。查看生成的源码如下:

-- Create sequence 
create sequence SEQ_TEST
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

当你调用seq_test这个序列时,它会为你产生从1到999999999999999999999999999的连续递增数值,从1开始，每次增量为1,不循环产生。产生最大值后将无法使用。默认使用缓存生成,每次缓存的数量为20个。

（2）创建有最大值的非循环序列

create sequence seq_test1
increment by 1
start with 10
maxvalue 300
minvalue 5;

这个序列虽然设置最小值为5,但由于开始值为10,并且不循环产生,所以不会产生10以下的数值。需要注意的是,序列的起始值不能小于最小值,否则创建序列会报错。我们把上面代码改成如下:

create sequence seq_test1
increment by 1
start with 10
maxvalue 300
minvalue 11;


SQL> create sequence seq_test1
  2  increment by 1
  3  start with 10
  4  maxvalue 300
  5  minvalue 11;
create sequence seq_test1
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE

会提示创建失败。

（3）创建有最大值的循环序列

create sequence seq_test2
increment by 1
start with 10
maxvalue 300 
minvalue 5
cycle ;

当我们执行序列提取到最大值300时,序列会从最小值5开始重新循环生成。而此序列第一次是从开始值生成。需要注意的是,如果是创建一个循环序列,则必须要设定最大值,否则会报错:

create sequence seq_test2
increment by 1
start with 10
minvalue 5
cycle ;


SQL> create sequence seq_test2
  2  increment by 1
  3  start with 10
  4  minvalue 5
  5  cycle ;
create sequence seq_test2
*
ERROR at line 1:
ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

（4）使用带缓存的序列

创建序列时使用CACHE能提高性能,特别是在高并发的情况下对数据库的性能提升还是不错的。但是使用缓存会有产生断号的现象,如果你的业务要求序列产生的值必须是连续的,那就只能使用nocache了。cache，它的用处是缓存指定个数的序列值。比如你设置的 cache 是20，那么在获取 nextval 时，Oracle 会直接从 cache 中取下一个序列值，如果 cache 中缓存的序列值没有了（比如 cache 中的序列值用完了，或者被手工清空了），那么 Oracle 会再次产生20个序列值，并放置 cache 中供使用，这样有助于提高序列值的获取速度。

--创建一个带缓存的序列
create sequence SEQ_CACHE
minvalue 1
maxvalue 1000
start with 1
increment by 1
cache 20;

此时执行SEQ_CACHE.nextval 会返回产生第一个值1。调用SEQ_CACHE.currval 查看当前值为1。

SQL> select SEQ_CACHE.nextval from dual;

   NEXTVAL
----------
	 1

SQL> select SEQ_CACHE.currval from dual;

   CURRVAL
----------
	 1

当我们第一次调用nextval时,由于设置了缓存数为20,序列会一次生成20个数值放在缓存里。当我们再次调用nextval时其实是从缓存里取到的值。假如我们此时将缓存清空再调用nextval,我们来测试一下。

-- 清空 cache 中缓存的序列值
alter system flush shared_pool;
-- 再次调用nextval获取序列值
select seq_cache.nextval from dual;
发现获取的值是21而不是2 。因为缓存里的值被清空了,所以系统会自动又获取20个新的连续值放在缓存里。

SQL> alter system flush shared_pool;

System altered.

SQL> select seq_cache.nextval from dual;

   NEXTVAL
----------
	21

使用缓存会产生产生的数字不连接的风险,如果系统出异常或oracle重启则系统会清空缓存的数据,当调用nextval时会重新获取相应缓存设置的数量的值。

create sequence SEQ_CACHE1
increment by 10
start with 10
maxvalue 300
minvalue 10
cycle
cache 50;


SQL> create sequence SEQ_CACHE1
  2  increment by 10
  3  start with 10
  4  maxvalue 300
  5  minvalue 10
  6  cycle
  7  cache 50;
create sequence SEQ_CACHE1
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

我们缓存设定的值是 50,而最大值是 300,那么为什么还会提示这样的信息呢？ 其实我们的 cache 虽然是 50，但是我们每次增长值是 10。这样 50 次缓存提取出的数是 500 （50*10）,我们每次循环的最大值是300,所以就提示我们一次获取的缓存值必须小于一次循环产生的最大值。

将上面创建序列的语句修改如下：

create sequence SEQ_CACHE1
increment by 10
start with 10
maxvalue 500
minvalue 10
cycle
cache 50;


SQL> create sequence SEQ_CACHE1
  2  increment by 10
  3  start with 10
  4  maxvalue 500
  5  minvalue 10
  6  cycle
  7  cache 50;
create sequence SEQ_CACHE1
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

我们一次循环的最大值已经设置成500了,为什么还有这样的错误提示？为什么还有这样的错误提示？这是因为还存在一个 minvalue ，minvalue 和 maxvalue 之间是 490 个数，也就是一次循环可以提取 490，但是我们的缓存是500。

create sequence SEQ_CACHE1
increment by 10
start with 10
maxvalue 500
minvalue 9
cycle
cache 50;

发现创建序列成功。在创建序列的时候关于缓存值的设置我们有一个基本的公式要求
最大值-最小值>=(缓存值-1)*每次循环的值

在创建序列时，有如下几点需要注意，否则序列创建可能不成功。

1、序列第一次必须先调用nextval获取一个序列值才能使用currval查看当前值

2、序列的起始值不能小于最小值

3、创建一个循环序列,则必须要设定最大值

4、如果创建带缓存的序列,缓存的值必须满足约束公式:   最大值-最小值>=(缓存值-1)*每次循环的值

五、修改序列

修改序列的注意事项：

1  必须是序列的拥有者或对序列有 ALTER any sequence权限

2  只有将来的序列值会被改变

3  改变序列的初始值只能通过删除序列之后重建序列的方法实现

ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE;
 
可以影响Sequence的初始化参数：

SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。

六、查询序列

1 通过数据字典DBA_OBJECTS可以查看用户拥有的序列。

2 通过数据字典DBA_SEQUENCES可以查看序列的设置。

查看用户的序列：
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM dba_SEQUENCES;

七、删除序列

drop sequence sequence_name;

八、序列使用场景

(1). 如果一个事务中只是INSERT时需要序列，其他地方不会需要这个序列，那么只需要在INSERT ... VALUES (seq.nextval ...)语句中使用即可。

(2). 如果一个事务中INSERT一张表后，还需要插入时的主键ID值，作为外键插入其他表，那么就需要在INSERT第一张表前使用select seq.nextval from dual提前获取可用的ID保存到一个变量中，为后面使用。

使用序列时Oracle内部大体是按照如下步骤进行：

(1). 一个序列会被定义到Oracle内部的一张数据字典表(seq$)的一行。
(2). 第一次使用序列，序列的起始值会加上缓存大小，然后更新回行。
(3). Oracle内部会自动跟踪内存中的两个值，当前值和目标值。
(4). 每次有回话调用seq.nextval，Oracle会递增当前值，然后检查是否超过了目标值，再返回结果。
(5). 如果当前值和目标值相同，Oracle会更新数据字典表中的行，为目标值加上缓存大小，同时内存中产生了一个新的目标值。

例如create sequence seq cache 20;
名称为seq的序列，缓存大小是20，默认初始值是1，步长默认是1。
当使用了一次seq.nextval后，可以看HIGHWATER字段值为21，即目标值1+缓存大小20=21。
当执行20次后，seq.nextval值变为21，此时HIGHWATER字段值是41，即目标值21+缓存大小20=41。

也就是每调用seq.nextval值20次，会更新一次seq$表，如果cache值较小，且序列使用的频率较高，那么会对seq$表有频繁的更新操作，日志量会增加，为了减少这种情况，我们可以将cache缓存值设置大一些，减少对字典表的更新。