序列号 oracle noorder,Oracle SEQUENCE 具体说明

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;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值