在12c之前,Oracle无法独自在表中实现类似MySQL的主键自增(auto_increment),因此在多用户插入场景,需要借助序列来避免各个用户插入的数据出现主键冲突。
一、序列的基本概念
在传统通过应用程序生成主键的场景,我们通常会采用串行(Serialization)的方式来获取唯一的整数,当某个用户获取主键值时,其他用户都必须等待。而序列(sequence)可以并行的为多用户生成唯一的整数,即多用户可以同时获取主键值并插入数据。
在多并发场景下,有时可能会发现序列生成的数字是“不连续的”。当你在使用序列时,别的用户也在使用该序列,你看到的空缺的数字可能被其他用户获取了。另外,只要用户会话获取了某个序列值后,这个值就只能被该会话访问,即使插入数据的事务回滚,这个序列值也不会恢复。因此序列不适合要求主键“连续”的场景,这种场景必须通过应用程序控制。
另外,序列值对不同的表是独立的,例如:同一个序列可以为"表A"和"表B"独立生成序列值。
二、序列的创建和管理
序列也是一种数据库对象,我们可以用相应的语句来创建和管理它。
2.1 创建序列
序列对象是通过 create sequence 语句来创建的,你需要有create sequence系统权限才能创建序列。在创建时,我们需要指定一系列的属性,起始值、步进值、最大值、是否循环等。
示例:创建一个从1开始,每次递增1的序列
create sequence seq1
start with 1
increment by 1
nomaxvalue
nocycle
cache 20;
在创建选项中:
- start with 1 指定序列初始值为1
- increment by 1 指定序列值每次递增1,这个值可以为负数,代表递减序列,increment by 1也是序列创建的默认设置,可以省略。
- nomaxvalue 指定序列值没有上限(实际最大值为10^28-1,),默认设置。你也可以用maxvalue N来指定序列的最大值。
- nocycle 指定序列到达最大值时不循环,默认设置。如果指定cycle,那么序列到达最大值后,会从头开始循环。
- cache 20 为了提高访问速度,预先分配好并缓存在内存中的序列值个数,默认设置。如果是较繁忙的系统,可以适当的提高该值。
上面解释的选项都采用了默认值,实际上面的语句就等价于:
create sequence seq2;
这个seq2和seq1设置是完全相同的。
除了上面解释的选项,序列还有一些其他创建选项,由于使用频率较低,了解即可:
- order 保证序列生成顺序和和访问顺序一致,在用作主键时,这个选项不重要。默认值是noorder。
- keep 指定在数据库重放功能时,将序列也恢复至初始值,默认是nokeep。
- scale 会在序列值前面附加6位数字前缀,减少序列冲突和等待,通常在高并发环境下才会使用此选项,例如RAC。
2.1 序列的修改和删除
序列的修改和创建一样,只需要使用 alter sequence seq_name 后面跟上选项即可,通常情况下,一个序列创建出来很少去修改它。
使用 drop sequence 语句后面跟上序列名,即可删除序列。
三、序列的使用
序列值的生成和访问是通过2个虚拟列 currval 和 nextval 引用的。每次访问 squence.nextval 都会生成一个新的序列值,而每次访问 sequence.currval 都会返回当前值。序列值的引用可以放在select, insert, update 语句中。
注意:currval可以重复引用,但不会生成新的序列值。一个未使用的新序列是没有当前值的,因此在使用currval之前,必须至少引用nextval一次(生成序列值)。
以上面全新创建的seq2为例,直接引用currval会告诉你还未定义:
select seq2.currval from dual;
我们需要引用一次nextval,让序列生成一个值,才可以使用currval;
select seq2.nextval from dual;
select seq2.currval from dual;
重复引用currval会返还相同的值:
select seq2.currval from dual;
select seq2.currval from dual;
重复引用nextval则会生成新的序列值:
select seq2.nextval from dual;
select seq2.nextval from dual;
序列可以用在SQL语句的大部分地方(最常用的就是将 sequence.nextval 放在insert 语句中生成主键),但其也有一些限制,下列场景无法使用:
- 子查询
- select 语句中使用了 distinct, group by, order by 或 union, interest, minus。
- where 子句中
- check 约束中
查询序列信息
可以通过视图 dba/all/user_sequences 查询序列的相关信息
select * from user_sequences;