一、序列定义
序列(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缓存值设置大一些,减少对字典表的更新。