数据库(Oracle)序列(Sequence)的基本使用

这篇具有很好参考价值的文章主要介绍了数据库(Oracle)序列(Sequence)的基本使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1. Sequence 定义

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

2. 为什么要用 Sequence

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

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

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

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

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

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

Sequence是一个更好的方法,原因如下:

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

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;

数据库(Oracle)序列(Sequence)的基本使用

添加图片注释,不超过 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创建序列-标识列

数据库(Oracle)序列(Sequence)的基本使用

添加图片注释,不超过 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;

数据库(Oracle)序列(Sequence)的基本使用

添加图片注释,不超过 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'
/

数据库(Oracle)序列(Sequence)的基本使用

图7.1

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

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

数据库(Oracle)序列(Sequence)的基本使用

图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
/

第一次调用s1.nextval之后,我只查询了一次视图(在第一次调用nextval时,序列才会出现在视图中):

数据库(Oracle)序列(Sequence)的基本使用

图7.3

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

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

“缓存”不是传统意义上的缓存,它只是一对数字(无论您设置的序列缓存大小有多大):当前值和提升highwater 后的值。为序列设置大型缓存不会造成资源损失,所以在大多数情况下,您应该尽可能使用大的cache。文章来源地址https://www.toymoban.com/news/detail-746041.html

引用

  1. Oracle sequences: The basics - Simple Talk
  2. https://www.databasestar.com/oracle-sequence/#What_is_a_Sequence_in_Oracle
  3. Oracle CREATE SEQUENCE Explained By Practical Examples

到了这里,关于数据库(Oracle)序列(Sequence)的基本使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • JAVA使用JDBC连接oracle数据库

    首先给出代码和输出:  讲解下代码  Class.forName(\\\"oracle.jdbc.OracleDriver\\\");这段代码是用来加载驱动的。 通过静态类DriverManager的getConnection方法配置连接字符串 DriverManager.getConnection(\\\"jdbc:oracle:thin:@192.168.10.150:1521:orcl11g\\\",\\\"xielong\\\",\\\"xielong\\\"); 其中ip为oracle数据库地址,1521为默认的数据

    2024年02月13日
    浏览(45)
  • (第20天)Oracle 数据库使用 RMAN 检查和恢复数据坏块

    Oracle 数据库的运行不可避免的会遇到各种各样的错误,就比如数据表出现坏块,此时,你这张表的数据就无法访问了,有什么好的办法可以恢复呢?今天就来讲讲如何使用 RMAN 来发现和恢复坏块。 有备份的情况下,我们可以直接通过 RMAN 块介质恢复(BLOCK MEDIA RECOVERY)功能来

    2024年02月04日
    浏览(45)
  • 异地使用PLSQL远程连接访问Oracle数据库【内网穿透】

    转载自cpolar极点云文章:公网远程连接Oracle数据库【内网穿透】 Oracle,是甲骨文公司的一款关系数据库管理系统,它在数据库领域一直处于领先地位。可以说Oracle数据库系统是世界上流行的关系数据库管理系统,系统可移植性好、使用方便、功能强,适用于各类大、中、小微

    2024年02月12日
    浏览(61)
  • Oracle使用exp和imp命令实现数据库导出导入

    Oracle和MySQL在SQL语法和一些数据库特性上有一些差异,以下是一些常见的差异: 数据类型: Oracle和MySQL支持的数据类型有所不同。例如,Oracle支持 NUMBER 、 DATE 、 VARCHAR2 等类型,而MySQL支持 INT 、 DATE 、 VARCHAR 等类型。 字符串比较: 在 Oracle 中,字符串比较默认是区分大小写

    2024年02月22日
    浏览(47)
  • java serverlets使用数据源连接oracle数据库,并执行查询操作代码

    package chap03; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Statement; import java.util.*; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.Namin

    2024年02月08日
    浏览(51)
  • 使用Apache Doris自动同步整个 MySQL/Oracle 数据库进行数据分析

    Flink-Doris-Connector 1.4.0 允许用户一步将包含数千个表的整个数据库(MySQL或Oracle )摄取到Apache Doris(一种实时分析数据库)中。 通过内置的Flink CDC,连接器可以直接将上游源的表模式和数据同步到Apache Doris,这意味着用户不再需要编写DataStream程序或在Doris中预先创建映射表。

    2024年02月09日
    浏览(57)
  • 【笔记】oracle线上生产数据库使用exp的方式更新到本地

    今天的任务是:将线上老的数据库里的数据同步到本地现有的二期数据库中来,即二期项目需要线上数据进行最后测试,于是就有了今天这篇,线上生产数据库更新到本地数据库的笔记。 由于数据量较大,我尝试过将线上的表导出成一个个Excel或者insert语句来完成更新,但是

    2024年02月16日
    浏览(53)
  • 使用免费负载生成器swingbench对oracle数据库进行压力测试(测试Oracle的功能或评估性能)

    Swingbench 是一个免费负载生成器(和基准测试),旨在对 Oracle 数据库 进行压力测试。目前最新版本 Swingbench 2.6。 SwingBench 由负载生成器,协调器和集群概述组成。该软件可以生成负载 并绘制交易/响应时间图表。 Swingbench 可用于演示和测试技术,例如实际应用程序集群,在线

    2024年02月10日
    浏览(56)
  • Python 进阶(三):Python使用ORM框架SQLAlchemy操作Oracle数据库

    要详细连接Oracle数据库并使用SQLAlchemy进行操作,按照以下步骤进行配置和编写代码: Oracle Instant Client:Oracle 提供的客户端库,可用于在 Python 中连接和操作 Oracle 数据库 访问 Oracle 官方网站:前往 Oracle 官方网站(instant-client/winx64-64-downloads) 可能需要创建一个免费的 Oracle

    2024年02月11日
    浏览(47)
  • Oracle/DM序列基本使用

    序列(SEQUENCE)是序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值,可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。序列是一个计数器,它并不会与特定的表关联。通过创建

    2024年01月17日
    浏览(37)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包