Oracle中序列（Sequence）详解(CRUD)

Oracle中序列（Sequence）详解 一 序列定义

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。

其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

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

二 创建序列

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

CREATE SEQUENCE sequence //创建序列名称[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue[{MAXVALUE n | NOMAXVALUE}] //最大值  [{MINVALUE n | NOMINVALUE}] //最小值[{CYCLE | NOCYCLE}] //循环/不循环[{CACHE n | NOCACHE}];//分配并存入到内存中

​

其中：

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

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

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

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

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

CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

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

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

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

三 使用序列

语法：

序列名.[currval/nextval]

currval：表示当前值，新序列若没使用过nextval不能直接使用当前值，会报错；

nextval：表示序列的下一个值

例：查询序列的值

ｓｅｌｅｃｔ seq_name.nextval from dual; --序列seq_name的下一个值

例：SQL语句中使用序列

ｉｎｓｅｒｔ into 表名 value（seq_name.currval）

//序列调用 产生一个新的序列ｓｅｌｅｃｔ seq_test.nextval from dual//查看当前序列的值ｓｅｌｅｃｔ seq_test.currval from dual

​3.2删除修改序列

语法：

ｄｒｏｐ sequence [用户名.]序列名

alert sequence [用户名.]序列名 [可选参数]

例：删除序列

dorp sequence seq_name；

例：修改序列

alert sequence seq_name

increment by 10

nomaxvalue

nocycle

cache 10

(修改序列只能修改这几个值，不能修改初始值)

3.3查看所有序列

查看当前用户下的所有序列：

ｓｅｌｅｃｔ * from user_sequences;

查看所有用户下的所有序列：

ｓｅｌｅｃｔ * from all_sequences;

还有一个

ｓｅｌｅｃｔ * from dba_sequences;

四、创建序列示例

CREATE SEQUENCE seq_test;执行这个创建语句会在数据库中产生一个名字为seq_test的序列对象,其它的参数都会默认使用默认值。查看生成的源码如下:-- Create sequence create sequence SEQ_TESTminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;

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

4.2 创建有最大值的非循环序列

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

​这个序列虽然设置最小值为5,但由于开始值为10,并且不循环产生,所以不会产生10以下的数值。需要注意的是,序列的起始值不能小于最小值,否则创建序列会报错。

4.3 创建有最大值的循环序列

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

当我们执行序列提取到最大值300时,序列会从最小值5开始重新循环生成。而此序列靠前次是从开始值生成。

需要注意的是,如果我们创建一个循环序列,则必须要设定最大值,否则会报错:

五、使用带缓存的序列

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

我们先来了解一下cache这个参数的作用。cache，它的用处是缓存指定个数的序列值。比如你设置的 cache 是20，那么在获取 nextval 时，Oracle 会直接从 cache 中取下一个序列值，如果 cache 中缓存的序列值没有了（比如 cache 中的序列值用完了，或者被手工清空了），那么 Oracle 会再次产生20个序列值，并放置 cache 中供使用，这样有助于提高序列值的获取速度。

我们下面通过一个案例来演示一下缓存的作用:

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

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

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

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

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

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

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

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

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

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 300minvalue 10cyclecache 50;

执行后出错如下:

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

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

我们将代码修改如下:

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 500minvalue 10cyclecache 50;

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

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

我们将代码修改如下:

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 500minvalue 9cyclecache 50;

发现创建序列成功。在创建序列的时候关于缓存值的设置我们有一个基本的公式要求:

最大值最小值缓存值每次循环的值

只要满足这个公式的缓存值设置就没有问题。

总结：

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

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

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

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

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