目录
定义
序列是可以生成唯一序列值的用户对象,通常用于主键和unique约束,可以使用这些伪列引用 SQL 语句中的序列值
A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns
sequence.CURRVAL |
CURRVAL:返回序列当前值
NEXTVAL:返回序列的下一个值
创建序列create sequence
创建序列需要有相应的create sequence权限
SQL> grant create sequence to scott; |
schema
指定包含序列的架构。如果省略架构,则 Oracle 数据库会在当前用户中创建序列。
sequence
指定要创建的序列的名称
如果未指定任何子句,则创建以 1 开头每次增加 1 且无上限的升序。如果仅指定INCREMENT BY -1 将创建以 -1 开头的降序,并在无下限下减小。
要创建无限制递增的序列,对于升序,需要省略 MAXVALUE 参数或指定 NOMAXVALUE。对于降序,省略 MINVALUE 参数或指定 NOMINVALUE。
要创建在预定义限制下停止的序列,对于升序,请为 MAXVALUE 参数指定一个值。对于降序,指定 MINVALUE 参数的值。还要指定 NOCYCLE。一旦序列达到其限制,任何生成序列号的尝试都会导致错误。
若要创建在达到预定义限制后重新启动的序列,需要指定MAXVALUE 和 MINVALUE 参数指定值。还要指定CYCLE。
SHARING
仅适用于在应用root app中创建序列。这种类型的序列称为应用程序公共对象,可以在pdb中共享,包干以下三个参数
METADATA 元数据链接共享序列的元数据,但其数据对于每个容器是唯一的。这种类型的序列称为metadata-linked application common object.
DATA 数据链接共享序列,其数据对于应用程序容器中的所有容器都相同。其数据仅存储在应用程序根目录中。这种类型的序列称为data-linked application common object。
NONE 序列不共享。
INCREMENT BY
指定序列号之间的间隔。此整数值可以是任何正整数或负整数,但不能为 0。此值的升序可以有28位之内,降序可以有27位之内。此值的绝对值必须小于MAXVALUE 和MINVALUE 的差值。如果此值为负值,则序列降序。如果该值为正,则为升序。如果省略此子句,则间隔默认为 1。
START WITH
指定序列的启始值,为升序序列的最小值或者将序序列的最大值,同样正值可以有28位,负值27位。
MAXVALUE
指定序列生成的最大值,也就是升序的最大值和将序的最小值,同样正值可以有28位,负值27位。
NOMAXVALUE
表示不限制最大值,但是最大正值28位
MINVALUE
指定序列的最小值。同样正值可以有28位,负值27位。但是最小值必须小于或等于"START WITH"的值,并且必须小于最大值。
NOMINVALUE
表示不限制最大值,但是最大负值27位
CYCLE
指示序列在达到最大值或最小值后继续生成值。升序达到最大值后,将生成其最小值。降序达到最小值后,将生成其最大值,循环往复
NOCYCLE
指定 NOCYCLE 以指示序列在达到最大值或最小值后无法生成更多值。默认为NOCYCLE
SQL> select sq01.nextval from dual; |
CACHE
指定数据库预分配并保留在内存中的序列值,以便更快地访问。此整数值有28位。此参数的最小值为 2。对于循环的序列,此值必须小于周期中的值数。不能缓存超过给定序列号周期中拟合的值。因此,CACHE 允许的最大值必须小于由以下公式确定的值
CEIL ( (MAXVALUE - MINVALUE) / ABS (INCREMENT) ) |
如果数据库实例发生故障,则未在已提交的 DML 语句中使用的所有缓存序列值将丢失。丢失值的潜在数量等于 CACHE 参数的值。
Note:
如果在 Oracle 真实应用程序群集环境中使用序列,Oracle 建议使用 CACHE 设置来提高性能。
NOCACHE
不与分配序列值,如果省略CACHE和NOCACHE则默认缓存20个序列号
ORDER
指定 ORDER 以保证按请求顺序生成序列号。如果使用序列号作为时间戳,此子句很有用。但是对于用于生成主键的序列来说,保证顺序通常并不重要。
NOORDER
不安请求顺序生成序列号,默认为NOORDER
KEEP
如果希望 NEXTVAL 在"应用程序连续性"replay期间保留其原始值,则需指定“KEEP”。只有当运行应用程序的用户是包含序列的架构的所有者时,才会发生此行为。此子句可用于在可恢复错误后replay时提供绑定变量一致性。
NOKEEP
NEXTVAL 在"应用程序连续性"replay期间不保留其原始值,默认NOKEEP。
SCALE
使用 SCALE 实现序列可伸缩性。指定 SCALE 时,数字偏移量贴在序列的开头,从而删除生成值中的所有重复项。
EXTEND 如果使用SCALE指定EXTEND,则生成的序列值所有长度(x+y),其中x事可伸缩偏移量的长度(默认值为6),y事序列中最大位数(maxvalue/minvalue)。使用SCALE时,最好不要同时使用ORDER
NOEXTEND 默认设置。使用NOEXTEND设置时,生成的序列值最多与序列中的最大位数(maxvalue/minvalue)一样宽。此设置可用于与使用序列填充固定宽度序列的现有应用程序集成
NOSCALE
禁用序列伸缩
SHARD
带碎片和缩放的序列 如果同时指定 SCALE 和 SHARD 子句,则序列将在分片数据库中为多个实例和会话生成可扩展的全局唯一值。 如果使用 SCALE 和 SHARD 子句指定 EXTEND,则生成的序列值是所有长度 (𝑥+𝑦+𝑧),其中 𝑥 是 SHARD 偏移的长度,默认值为 4,𝑦 是默认值为 6(5)的可缩放偏移的长度,𝑧 是序列中最大数字的最大数字。 如果使用 SHARD 和 SCALE 子句指定扩展或 NOEXTEND,则它同时适用于 SHARD 和 SCALE。不需要单独指定 EXTEND 或 NOEXTEND。如果为具有相同或不同值的 SHARD 和 SCALE 子句单独指定 EXTEND 或 NOEXTEND 选项,则会导致分析错误结果,并发送重复或冲突 EXTEND 子句的消息。 使用 SHARD 时,强烈建议您不要同时在序列上使用 ORDER。 可以将 SHARD 与缓存和 NOCACHE 操作模式一起使用。
SESSION
指定 SESSION 以创建会话序列,这是一种特殊类型的序列,专门设计用于具有会话可见性的全局临时表。与现有的常规序列(为了比较而称为"全局"序列)不同,会话序列只返回会话中的唯一序列号范围,而不是会话之间。另一个区别是会话序列不是永久性的。如果会话消失,则会话期间访问的会话序列的状态也消失。 会话序列必须由读写数据库创建,但可以在任何读写或只读数据库(常规数据库临时打开只读数据库或备用数据库)上访问。 当使用 SESSION 子句指定CACHE、NOCACHE、ORDER或NOORDER 子句时,将忽略这些子句
GLOBAL
以创建全局或常规序列。为默认值
创建一个序列
SQL> conn scott/tiger create sequence sq01 increment by 10 start with 3 maxvalue 150 nocache nocycle; |
获取序列的值
SQL> select sq01.currval from dual; |
当序列未使用的时候无法获得当前值
SQL> select sq01.nextval from dual; |
SQL> select sq01.currval from dual; |
SQL> select sq01.nextval from dual; |
序列的使用
作为主键使用
将序列sq01作为列的主键,作为列的填充值
创建测试表并添加主键
SQL> create table tb_seq01(id number(3),name varchar2(10)); |
插入数据并引用序列值为填充
SQL> insert into tb_seq01 values(sq01.nextval,q'[name]'||sq01.nextval); |
*q'[]'*为指定转义符,指定[]为转义符,也可以是其他符号
作为update使用
SQL> update tb_seq01 set id=sq01.nextval where id=23; |
查看序列的有效值
SQL> select sq01.currval from dual; |
序列的修改alter sequence
对于现有序列增量、最小值、最大值缓存等属性的修改,需要用到alter sequence
创建序列sq02,cache为20
SQL> create sequence sq02 increment by 1 start with 1 nocycle cache 20; |
其中LAST_VALUE为下一组有效的第一个值
模拟数据库宕机恢复后查看当前的序列值
SQL> conn / as sysdba |
可以看出缓存中的值不会再恢复后再用了
start with 值只能删除索引后重建,如果直接修改的话会报错
SQL> alter sequence sq02 increment by 1 start with 50 nocycle cache 7; |
其他参数可以直接修改,但是修改之后的序列只会影响以后产生的列值
SQL> select sq02.nextval from dual; |
从43开始是因为之前的值被废弃了,从下一组有效的值开始
如果maxvalue 小雨当前的序列值,会报错
SQL> alter sequence sq02 maxvalue 20; |
删除序列 drop sequence
直接删除即可
SQL> drop sequence sq01; |