Oracle序列(sequence)使用总结

前言：

我们都知道数据库表中的主键值有的时候我们会用数字类型的并且自增。mysql、sql server中的都可以使用工具创建表的时候很容易实现。但是oracle中没有设置自增的方法的工具，一般情况我们会使用序列和触发器来实现主键自增的功能。

序列：

Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。由于oracle中没有设置自增列的方法，所以我们在oracle数据库中主要用序列来实现主键自增的功能。

创建序列

[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值  
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环  CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
[{CACHE n | NOCACHE}];//分配并存入到内存中，CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

使用序列

序列创建后,可以使用序列的NEXTVAL来获取序列的下一个值,使用CURRVAL来查看当前值。第一次使用必须先使用NEXTVAL来产生一个值后才可以使用CURRVAL进行查看。

select seq_test.nextval from dual
//查看当前序列的值
select seq_test.currval from dual
​

这里有两个坑需要注意：
1:如果第一次使用seq_test.currval 会报错：

2：nextval 有先查询当前序列的下一个值设置后返回该值，使用seq_test.nextval from 后面跟的是dual 虚拟表这样会查询出一条数据，这里不能跟test 具体表，否则序列增加值会设置成test表的大小值造成序列断裂

除了做主键自增外我们利用自增特性结合oracle数据库lpad()函数生成多位数字格式

查看当前用户拥有的序列

 SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER
 FROM  USER_SEQUENCES; 

删除序列

DROP SEQUENCE test;

修改序列

1、直接drop sequence test;然后重新创建并且设置序列的初始值为希望的数据

SQL:
drop sequence test;
create sequence test;
increment by 1
start with 1//你想要的值
maxvalue 999999999;

2、修改序列的每次增加的值，然后再修改回每次自增1

比如将每次只自增1修改为99这样，做一次nextval也能得到预想的值，然后再将每次自增再次修改为1;
SQL:
alter sequence test increment by n; //n为做自增能够达到预想的值
做一次select seq_test.nextval from dual;
再将序列的值修改回去alter sequence seq_test increment by 1;

使用带缓存的序列

使用带缓存的序列

创建序列时使用CACHE能提高性能,特别是在高并发的情况下对数据库的性能提升还是不错的（防止线程拿到重复序列）。但是使用缓存会有产生断号的现象,如果你的业务要求序列产生的值必须是连续的,那就只能使用nocache了。

我们先来了解一下cache这个参数的作用。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。

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

-- 清空 cache 中缓存的序列值
alter system flush shared_pool;
-- 再次调用nextval获取序列值
select seq_cache.nextval from dual;

发现获取的值是21而不是2 。因为缓存里的值被清空了,所以系统会自动又获取20个新的连续值放在缓存里。

我们现在再执行四次nextval,会得到当前的值为25。此时我们再次清空缓存,然后再次调用nextval来获取序列。

-- 清空 cache 中缓存的序列值
alter system flush shared_pool;
-- 再次调用nextval获取序列值
select seq_cache.nextval from dual;

我们会得到当前的值为41。为什么呢？因为每次oracle获取20值是从上次获取的最大值开始的,而不是从当前值开始计算的！使用缓存会产生产生的数字不连接的风险,如果系统出异常或oracle重启则系统会清空缓存的数据,当调用nextval时会重新获取相应缓存设置的数量的值。

我们再来看一个带缓存的案例:

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

执行后出错如下:

为什么会出现这样的错误呢？

我们缓存设定的值是 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;

发现仍然报错如下图所示:

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

我们将代码修改如下:

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

发现创建序列成功。在创建序列的时候关于缓存值的设置我们有一个基本的公式要求:
最大值最小值缓存值每次循环的值
只要满足这个公式的缓存值设置就没有问题。

如果正常关闭Oracle，未用完的，缓存到内存的sequence值
文档中只说system failure 时会浪费sequence值：
If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

番外

RAC环境下ORACLE序列缓存导致序列混乱

Oracle下关于Sequence的使用有三种情况：

1. cache + noorder

2. nocache

3. cache + order

总结：

通过上述我们发现使用序列有几个基本的约束条件,总结有以下几条：

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

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

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

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