数据库（Oracle）序列（Sequence）的基本使用

在Oracle中可以用SEQUENCE生成自增字段。Sequence序列是Oracle中用于生成数字序列的对象，可以创建一个唯一的数字作为主键。

张辞忧（YOYO）

3100人浏览 · 2024-05-03 11:07:01

张辞忧（YOYO） · 2024-05-03 11:07:01 发布

1. Sequence 定义

在Oracle中可以用SEQUENCE生成自增字段。Sequence序列是Oracle中用于生成数字序列的对象，可以创建一个唯一的数字作为主键。

2. 为什么要用 Sequence

你可能有疑问为什么要使用序列？

不能使用一个存储主键的表并每次递增吗？或者将列设置为AUTO INCREMENT？

如果使用一个表来储存主键值的话，也许需要这样做：

创建一个新表来储存单一值。
每次插入新值到你想要插入的表时，在新表中使用该值。
然后更新新表（将其加1）。
这些逻辑都可以加入进存储过程。

然而这种方法存在以下几个问题：

如果你的事务中途失败，可能会得到重复的数据（插入的数据有一个ID，但新表没有增加）。这可以通过先递增然后插入来部分避免，但它仍然有中途失败的风险。
更新表将对其进行锁定，这可能会导致多个事务或用户出现问题。如果有一个用户，这可能是可以的，但如果程序有两个或多个用户，那么这可能会导致问题。
创建和维护一个表以及执行此操作的代码是繁重的工作。

Sequence是一个更好的方法，原因如下：

创建更简单。
使用起来更简单。
没有死锁以及导致其他事务出现问题的风险。

3. 创建（CREATE）Sequence

CREATE SEQUENCE [schema_name.]{sequence_name}    -- schema_name为将存储序列的模式名，sequence_name 为自定义名称；
	START WITH      n    -- n 为序列的初始值，默认为1；
	INCREMENT BY    n    -- n 为序列步长（序列增加的幅度），默认为1，如果是负则按此步长递减；
	[MINVALUE n | NOMINVALUE ]    -- 如果序列递减，定义序列生成器能产生的最小值,默认为1
	[MAXVALUE n | NOMAXVALUE ]    -- 定义序列生成器能产生的最大值,默认无限制（1e28 - 1）
	[CACHE n | NOCACHE]    -- value 是存放序列的内存块大小，默认20。对序列进行内存缓存可以改善序列性能。
	[ORDER | NOORDER]    -- 表示序列号是按照请求的顺序生成的。如果使用时间戳的序列号会有用。
	[CYCLE | NOCYCLE]    -- 值达到限制值后是否循环，如果不循环，达到限制值后，继续产生新值会发生错误 ;

使用缓存创建序列的缺点是，如果发生系统故障，所有未使用的缓存序列值都将“丢失”。这会在指定的序列值中产生“间隙”。当系统恢复时，Oracle将从序列中停止的位置缓存新数字，忽略所谓的“丢失”序列值。要恢复丢失的序列值，您可以始终执行ALTER sequence命令，将计数器重置为正确的值。

4. 使用 Sequence

CURRVAL和NEXTVAL可以在以下几个地方使用：

INSERT语句的VALUES子句。
SELECT语句的SELECT列表
UPDATE语句的UPDATE子句

它们不能在以下地方使用：

CHECK约束的条件
视图查询
实体化视图查询
子查询
带有DISTINCT关键字、GROUP BY子句或ORDER BY子句的SELECT语句
使用UNION、INTERSECT或MINUS运算符与另一个SELECT语句组合的SELECT语句
SELECT语句的WHERE子句
CREATE TABLE或ALTER TABLE语句中列的DEFAULT值

最后一点意味着您不能使用DEFAULT关键字的序列将列设置为自动递增。有一种方法可以创建自动递增列，那就是使用触发器和序列。

1）基本Oracle序列示例

以下语句创建一个名为id_seq的升序Sequence，从10开始，递增10，最小值为10，最大值为100。由于CYCLE选项，序列一旦达到100就返回10。

CREATE SEQUENCE id_seq
    INCREMENT BY 10
    START WITH 10
    MINVALUE 10
    MAXVALUE 100
    CYCLE
    CACHE 2;

使用NEXTVAL伪列获取序列的下一个值：

SELECT 
    id_seq.NEXTVAL 
FROM 
    dual;
以下是输出：
    NEXTVAL
----------
        10

一旦通过NEXTVAL伪列获取序列号，就可以使用CURRVAL伪列重复访问它：

SELECT 
    id_seq.CURRVAL 
FROM 
    dual;
以下是输出：
    CURRVAL
----------
        10

注意！当调用NEXTVAL伪列时，它会为正在选择或插入的每一行数据返回一个新值。：

SELECT 
    id_seq.NEXTVAL 
FROM 
    dual;
以下是输出：
    NEXTVAL
----------
        20

但如果我使用几个NEXTVAL命令运行此查询的话：

SELECT id_seq.NEXTVAL AS nv1,
id_seq.NEXTVAL AS nv2,
id_seq.NEXTVAL AS nv3
FROM dual;
以下是输出：
       NV1         NV2         NV3
----------  ----------  ----------
        30          30          30

这表明NEXTVAL只为此记录调用过一次，并且每次的值都是相同的。

2) 在表列中使用 Sequence 的例子

在Oracle12c之前，只能在插入时将序列与表列间接关联。

首先，创建一个名为tasks的新表：

CREATE TABLE tasks(
    id NUMBER PRIMARY KEY,
    title VARCHAR2(255) NOT NULL
);

其次，为tasks表的id列创建一个序列：

CREATE SEQUENCE task_id_seq;

第三，在任务表中插入数据：

INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, 'Create Sequence in Oracle');

INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, 'Examine Sequence Values');

最后，从任务表中查询数据：

SELECT  
    id, title
FROM
    tasks;

添加图片注释，不超过 140 字（可选）

在本例中，tasks表与task_id_seq序列没有直接关联。

3）通过标识列使用序列的例子

在Oracle12c中，您可以通过标识列将序列与表列相关联。

首先，用id列的identity列创建tasks表。

CREATE TABLE tasks(
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR2(255) NOT NULL
);

Oracle创建了一个与 tasks表的 id列关联的序列。

由于Oracle自动为 id列生成序列，因此在您的Oracle实例中，序列的名称可能不同。

Oracle创建序列-标识列

添加图片注释，不超过 140 字（可选）

Oracle使用 sys.idnseq$ 来存储表和序列之间的链接。

此查询返回任务表和 ISEQ$$_74366序列的关联：

SELECT 
    a.name AS table_name,
    b.name AS sequence_name
FROM   
    sys.idnseq$ c
    JOIN obj$ a ON c.obj# = a.obj#
    JOIN obj$ b ON c.seqobj# = b.obj#
WHERE 
    a.name = 'TASKS';

第二，在任务表中插入一些行：

INSERT INTO tasks(title)
VALUES('Learn Oracle identity column in 12c');

INSERT INTO tasks(title)
VALUES('Verify contents of the tasks table');

最后，从任务表中查询数据：

SELECT
    id, title
FROM
    tasks;

添加图片注释，不超过 140 字（可选）

5. 删除（DROP）Sequence

要删除序列，它必须在您的模式中，或者您必须具有drop ANY sequence权限才能删除其他模式中的序列。

DROP SEQUENCE seq_name;

6. 更改（Alter）Sequence

创建序列后，可以更改序列的大多数特性。唯一不能更改的是序列的起始编号和序列的当前值。要更改的话需要删除并重新创建序列。

ALTER SEQUENCE seq02
INCREMENT BY 20
MAXVALUE 100000
CYCLE;

7. Sequence 原理

Oracle用Sequence来按需提供（几乎）无限的数字序列，这些序列保证是唯一的。最简单的方法就是保持一个全局可见的计数器，并在每次查询sequence_name.nextval的值时递增。

但Oracle必须跟踪提供的最后一个数字，以避免两次提供相同数字的风险——这对数据库系统来说很容易：只需将最新的数字保存在表中即可。然而，如果每次有人需要下一个号码时都必须更新一个表格，这流程会非常缓慢。这时应该使用一个自主事务，这样就不需要等待第一个会话提交其当前事务，然后才能生成下一个数字。因此，Oracle使用递归的、自主的事务来处理表更新，并且它通过在内存中计数并记住下一次需要更新表的时机来避免每次调用nextval都更新表。

自制”序列的一种常见策略是，在一个表中包含（序列名，最近使用的序列号）的列，然后编写一个函数，从表中选择相关行进行更新，增加编号，更新表，并将编号提供给最终用户，这正是Oracle内在的机制。

您可以通过查询视图 user_sequences 来查看序列的数据库映像。假设我刚刚创建了一个名为s1的序列，图3.1是一个查询创建序列后立即给出的结果，和一次调用获取 s1.nextval 后得到的结果：

select
        min_value, max_value, 
        increment_by, cache_size, last_number, 
        cycle_flag, order_flag
from 
        user_sequences
where 
        sequence_name = 'S1'
/

图7.1

如果你有DBA权限，你可以进一步找到序列的 object_id，然后查询视图所基于的字典表（seq$）。以下运行了两次查询，一次在第一次调用 s1.nextval 之前，另一次是刚刚调用 s1 之后：

select 
        minvalue, maxvalue, increment$, cache, highwater, cycle#, order$ 
from 
        seq$ 
where
        obj# = 124874
;

图7.2

如果你的账户已被授权，可以通过动态性能视图 v$_sequences 查看内存中的信息：

select
        min_value, max_value,
        increment_by, cache_size, highwater,
        cycle_flag, order_flag, nextvalue
from
        v$_sequences
where
        object# = 124874
/

C# 复制全屏

第一次调用s1.nextval之后，我只查询了一次视图（在第一次调用nextval时，序列才会出现在视图中）：

图7.3

动态性能视图的列 nextvalue 保存了下次用户调用 s1.nextval 时将提供的值。

三个视图中都有一个“highwater”值（在图7.1视图user_sequences中称为“last_number”）。当对s1.nextval 的调用返回 highwater 中显示的值（highwater和nextvalue相同）时，进行调用的会话将执行并提交递归事务，将增量值添加到当前 highwater 并更新 seq$ 表。

“缓存”不是传统意义上的缓存，它只是一对数字（无论您设置的序列缓存大小有多大）：当前值和提升highwater 后的值。为序列设置大型缓存不会造成资源损失，所以在大多数情况下，您应该尽可能使用大的cache。

参考资料：

1、【数据库开发】Oracle数据库ORA-00904错误终极解决方案-无名阁

2、实现oracle分页查询：优化数据检索与提高性能的终极指南1-无名阁

3、Oracle时间格式转换从入门到精通1：时间的魔法之旅-无名阁

4、oracle decode简介应用场景及进阶应用：解密Oracle中的神奇函数——深入探究decode函数的妙用-无名阁