Oracle SEQUENCE 详细说明[通俗易懂]

Oracle SEQUENCE 详细说明[通俗易懂]ORACLE SEQUENCE  ORACLE没有自增数据类型,如需生成业务无关的主键列或惟一约束列,可以用sequence序列实现。CREATESEQUENCE语句及参数介绍:创建序列:需要有CREATESEQUENCE或者CREATEANYSEQUENCE权限, CREATESEQUENCE[schema.]sequence  [{IN

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用
 ORACLE  SEQUENCE

    ORACLE没有自增数据类型,如需生成业务无关的主键列或惟一约束列,可以用sequence序列实现。

CREATE SEQUENCE语句及参数介绍:

创建序列:需要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限,

 CREATE SEQUENCE [ schema. ]sequence

    [ { INCREMENT BY | START WITH } integer

    | { MAXVALUE integer | NOMAXVALUE }

    | { MINVALUE integer | NOMINVALUE }

    | { CYCLE | NOCYCLE }

    | { CACHE integer | NOCACHE }

    | { ORDER | NOORDER }

    ];

   

CREATE SEQUENCE各参数详解:

schema指定在哪个用户的schema下创建sequence,如不指定,默认在当前用户下创建。

sequence指定要创建的sequence序列名

    注意:如果只指定以上参数,将启动一个从1开始,以1为单位递增,没有最大值限制的递增序列。

    如果要创建一个没有约束的序列,递增序列时:忽略MAXVALUE参数或指定NOMAXVALUE;递减序列:省略MINVALUE参数或指定NOMINVALUE。

    如果要创建一个有限制的序列,递增序列时:指定MAXVALUE参数;递减序列:指定MINVALUE参数。此时序列达到限制后会报错:

    如果要创建一个有限制的序列在达到限制后重新启动,指定MAXVALUE和MINVALUE后,还需要指定CYCLE。如果不指定MINVALUE,默认为NOMINVALUE,

    这个值是1.

INCREMENT BY指定序列号间的间隔,这个整数值可以是任何正整数或负整数,但不能是0。这个值最多有28位数字。

    绝对值必须小于MAXVALUE与MINVALUE的差异(如非在此区间报错:ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE)。

    如果这个值是负的,则该序列下降。如果该值为正,则序列上升。如果省略此子句,则间隔缺省为1。

START WITH指定要产生的第一个序列号。

    此子句启动一个递增序列,要大于最小值;或启动一个递减序列,小于它最大值。

    对于递增序列,默认值是序列中的最小值。对于递减的序列,默认值是序列中的最大值。这个整数值可以最多28位数字。

    这个值和达到限制的最大/最小值后重新启动时的值没有关系(如递增序列创建时指定有最大值最小值且指定CYCLE,则序列达到最大值后,

    会从最小值开始;如未指定兼包最小值,默认1开始。

MaxValue指定序列可生成的最大值。这个整数值可以最多28位数字。MAXVALUE必须>=START WITH、必须大于MINVALUE。

NOMAXVALUE:指定NOMAXVALUE表示递增序列的最大值是10的27次方,或递减序列最大值为-1。这是默认的。

MINVALUE:指定序列的最小值。这个整数值可以最多28位数字。MINVALUE必须<=START WITH的值和必须小于MAXVALUE。

    如此处不符,报:ORA-04006: START WITH cannot be less than MINVALUE。不指定此参数时,默认是1.

NOMINVALUE:指定NOMINVALUE来表示递增的序列最小值为1,递减序列为负10的26次方。这是默认的。

CYCLE:指定循环,表明序列在达到它的最大或最小值后生成的值。当递增序列达到最大值后,再从最小值开始循环。

    当递减序列达到最小值,从最大值开始循环。

NOCYCLE:指定NOCYCLE以指示该序列不能在达到其最大值或最小值后产生更多的值。这是默认的。

CACHE :指定数据库为序列预分配多少个值放在内存中以便更快访问。这个整数值可以最多28位数字。该参数最小值为2;

    这个值必须小于一个CYCLE循环的数(比如从1-100是一个循环,CACHE要小于100,不然可能 一次CACHE的值要有重复的会出错。

    报错是:ORA-04013: number to CACHE must be less than one cycle)。

    计算公式是:(CEIL (MAXVALUE – MINVALUE)) / ABS (INCREMENT)

    如果系统故障,内存中未使用的CACHE值会丢失,将会导致序列不连续。ORACLE建议在RAC中使用CACHE来提高性能。

NOCACHE :指定该序列值不被预分配。如果省略CACHE和NOCACHE,数据库默认会缓存20个序列号。

ORDER :只有在RAC时需要指定,指定ORDER 是为了保证序列号是因为有请求才生成的。在使用序列号做为一个时间戳时很有用。

NOORDER:这是默认的。

使用序列

    序列生成的是一系列整数数字.一个序列中包含两个”伪列” ,分别为”Currval”和”Nextval”,可以分别用来获取该序列的当前值和下一个值.

    虽然我们在定义时指定序列初始值为1但并没有真正初始化该值. 当在检索序列的当前值前,必须通过检索序列的下一个值即Nextval来对序列进行

    初始化操作.在选择了Nextval时,该序列就被初始化为1.

使用sequence时对系统性能大致有以下影响:

详见:http://blog.itpub.net/17203031/viewspace-717042

    1.Seq$基表是记录系统sequence的数据字典表.每次调用nextval,会递归调用更新并COMMIT Seq$基表。

    2.更新Seq$基表并提交会产生redo log–几百字节,COMMIT频繁会造成LGWR的压力;过多redo log生成,造成LGWR压力、恢复时费时等。

    3.多个会话使用sequence可能出现争用,等待事件row lock contention

    对于nocache/cache参数:

    nocache:每次使用nextval,都会更新Seq$基表并COMMIT。

    cache:只有在内存中cache的序列号使用完后才会重新获取sequence,才会更新Seq$基表并提交。

    比如cache设置为2000,则在使用sequence时对性能影响比nocache小上千倍。

    所以一般情况下,建议设置一个较大的cache值,用于进行性能的优化。(默认不指定nocache时是20)

#####################################################

ORACLE sequence创建示例:

create sequence bys.test_seq

    increment by 3

    start with 5

    maxvalue 18

    minvalue 4

    cycle

    cache 4;

在bys用户下创建名为test_seq的sequence

    从5开始,每次增加3,最大值是18,最小值是4

    允许重用,cache 4 表示会缓存四个序列号,比如5 8 11 14

    当然在实验中也可以使用最简单的:create sequence bys.test_seq2; 其它参数不写,使用系统默认哈哈

#####################################################

ORACLE sequence修改和删除示例:

ALTER SEQUENCE [ schema. ]sequence

    { INCREMENT BY integer

    | { MAXVALUE integer | NOMAXVALUE }

    | { MINVALUE integer | NOMINVALUE }

    | { CYCLE | NOCYCLE }

    | { CACHE integer | NOCACHE }

    | { ORDER | NOORDER }

    }

修改时的三个注意事项:

    如果要使序列start with不同的数字,只能删除序列重建。

    如果在使用NEXTVAL初始化序列前改变INCREMENT BY的值,一些序列号会被跳过。解决跳过问题的方法–删除重建

    修改的各个参数的新值依然要满足create sequence各参数介绍中的描述。

NEXTVAL初始化序列前改变INCREMENT BY的值示例:

    create sequence bys.seq3

    increment by 3

    start with 5

    maxvalue 18

    nominvalue

    cycle

    cache 4;

 BYS@ bys3>alter sequence bys.seq3 increment by 5; –初始化前修改

    Sequence altered.

BYS@ bys3>select seq3.nextval from dual; –初始化时确实跳过了一些数字。。

    NEXTVAL

    ———-

    7

BYS@ bys3>select seq3.nextval from dual;

    NEXTVAL

    ———-

    12

示例修改语句:

    ALTER SEQUENCE customers_seq MAXVALUE 1500;

    ALTER SEQUENCE customers_seq CYCLE CACHE 5;

删除序列语句:

    DROP SEQUENCE [ schema. ]sequence_name ;

    如:BYS@ bys3>drop sequence bys.seq2;

############################

ORACLE sequence使用示例

详见官方文档–http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#i1006157

序列常见使用场景:

    1可以在SELECT 语句,CREATE TABLE … AS SELECT语句, CREATE MATERIALIZED VIEW … AS SELECT中使用。

    2在UPDATE的SET中,在INSERT 的子句或VALUES中。序列可以由多个用户同时访问而不产生等待或锁定。

    3第一次查询要用 NEXTVAL,返回序列的初始值。

    4查询当前序列号用:CURRVAL,返回的是最后一次引用NEXTVAL返回的值。

    5查询下一个序列号用NEXTVAL–用此命令时,sequence会先增加1或increment by指定的值,然后返回sequence值

本实验中的查询:

BYS@ bys3>select test_seq.currval from dual; —未使用NEXTVAL初始化,故报此错。

    select test_seq.currval from dual

    *

    ERROR at line 1:

    ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

    BYS@ bys3>select test_seq.nextval from dual; 第一次使用NEXTVAL,显示的是创建时start with指定的值

    NEXTVAL

    ———-

    5

BYS@ bys3>select test_seq.currval from dual; 使用currval查到当前序列号—最后一次引用NEXTVAL返回的值

    CURRVAL

    ———-

    5

BYS@ bys3>select test_seq.nextval from dual; –一直执行nextval,观察序列达到maxvalue指定的值后如何循环使用

    NEXTVAL

    ———-

    17

BYS@ bys3>select test_seq.nextval from dual; –序列达到maxvalue指定的值后返回的是minvalue指定的值而不是start with了。如未指定minvalue或指定NOMINVALUE,则是返回1.

    NEXTVAL

    ———-

    4

BYS@ bys3>insert into test values(test_seq.nextval,’seqtest’); –使用INSERT语句调用序列

    1 row created.

    BYS@ bys3>select * from test;

    OBJECT_NAME STATUS

    ———— ——-

    10 seqtest

BYS@ bys3>insert into test values(test_seq.currval,’seqtest’);

    1 row created.

    BYS@ bys3>select * from test;

    OBJECT_NAME STATUS

    ———— ——-

    10 seqtest

    10 seqtest

BYS@ bys3>insert into test(object_name) select test_seq.nextval from dual; –使用INSERT子语调用序列

    1 row created.

    BYS@ bys3>select * from test;

    OBJECT_NAME STATUS

    ———— ——-

    10 13

    10 16

    4

BYS@ bys3>update test set status=test_seq.nextval; –使用UPDATE语句调用序列

    2 rows updated.

    BYS@ bys3>select * from test;

    OBJECT_NAME STATUS

    ———— ——-

    10 13

    10 16

BYS@ bys3>delete test where status=test_seq.currval; –DELETE中不能使用sequence做条件

    delete test where status=test_seq.currval

    *

    ERROR at line 1:

    ORA-02287: sequence number not allowed here

利用解发器自动为表插入递增序列:—类似自增字段的作用

建解发器代码为:

    create or replace trigger tri_test_id

    before insert on test –test 是表名

    for each row

    declare

    nextid number;

    begin

    IF :new.testid IS NULL or :new.testid=0 THEN –DepartId是列名

    select seq1.nextval –seq1是提前创建好的序列的名字

    into nextid from sys.dual;

    :new.testid:=nextid;

    end if;

    end tri_test_id;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/180573.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • tomcat docbase(fpga版本管理)

    TOMCAT的配置文件Server.XML里有一句:指定应用目录,其他参数先不说,聊聊appBase。1、appBase=”webapps“,这是默认值,代表:d:\tomcat\webapps这样的路径,谓之根目录;根目录下的ROOT目录,代表默认的主目录。访问:http://localhost:8080默认找d:\tomcat\webapps\ROOT下的文件(前提是没有d…

  • 数据链路层学习之LLDP「建议收藏」

    一、LLDP协议概述 随着网络技术的发展,接入网络的设备的种类越来越多,配置越来越复杂,来自不同设备厂商的设备也往往会增加自己特有的功能,这就导致在一个网络中往往会有很多具有不同特性的、来自不同厂商的设备,为了方便对这样的网络进行管理,就需要使得不同厂商的设备能够在网络中相互发现并交互各自的系统及配置信息。 LLDP(LinkLayerDiscoveryProtocol,链路层发现协

  • git下载安装教程

    git下载安装教程git下载安装教程前言:因为最近突然对使用github搭建一个自己的网站并绑定域名特别着迷,但是前提条件是必须得安装git,于是便把安装过程记录下来,便利自己,帮助他人。1.访问git官网下载最新版本git官方网页:https://git-scm.com/download/win在git官网中,有不同操作系统下的git,选择符合自己电脑版本的进行下载就可以了这里我选择的windows,然后根据自己电脑是32位还是64位,在下面两个选项中选择选择好了静待其下好就好了或许会有下载缓慢或无法下

  • Origin绘图快速上手指南

    Origin绘图快速上手指南1、创建工程打开origin后,点击菜单栏“文件”,选择“项目另存为”,给项目命名,并存到某个工作路径。2、导入数据然后将excel中的数据(只要数据)选中后复制到Book1中,从第5行开始粘贴。可以在侧面打开“项目管理器”,给表格“Book1”重命名为“曲线数据”。还可以在表格的“长单位”处给每列数据加上标签。3、那么这时可以直接使用Origin的自动绘图功能了。选择A、B、C所有列,然后点击菜单栏的“绘图”,选择一个折线图,双击即可绘图。这样呢就是将两条曲线放到同一张图中了。如果想要自定

  • cocos2d-x3.x屏蔽遮罩层屏蔽触摸button

    cocos2d-x3.x屏蔽遮罩层屏蔽触摸button

  • DirectX修复工具使用技巧之一——解除被占用的文件,完整修复C++

    DirectX修复工具使用技巧之一——解除被占用的文件,完整修复C++最后更新:2020-9-23随着V4.0正式版的发布,近来有部分用户来咨询如何删除被占用的C++文件。在此我将以解决最常见的PC版QQ占用的3个C++2010文件(alt100.dll、msvcr100.dll、msvcp100.dll)为例,向大家演示一下操作方法,其他C++或文件的方法大同小异。此次操作以Windows10为例,其他系统相应参考即可。首先,当C++修复失败时,如果想查看具体的错误信息,请首先确定您使用的V4.0增强版或更高版本,老版本不支持此…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号