当前位置:Gxlcms > mysql > ORACLEsequence各参数及创建修改删除使用详解示例

ORACLEsequence各参数及创建修改删除使用详解示例

时间:2021-07-01 10:21:17 帮助过:56人阅读

ORACLE没有自增数据类型,如需生成业务无关的主键列或惟一约束列,可以用sequence序列实现。 CREATE SEQUENCE语句及参数介绍: 详见官方文档:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314 创建序列:需要有CREATE

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

CREATE SEQUENCE语句及参数介绍:

详见官方文档:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314
创建序列:需要有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以指示该序列不能在达到其最大值或最小值后产生更多的值。这是默认的。达到最大值会报错:ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
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:这是默认的。

使用sequence时对系统性能大致有以下影响: -->Seq$基表

详见: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)

RAC时,如果序列号不要求有序建议用NOORDER+CACHE

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

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
序列常见使用场景:
可以在SELECT 语句,CREATE TABLE ... AS SELECT语句, CREATE MATERIALIZED VIEW ... AS SELECT中使用。
在UPDATE的SET中,在INSERT 的子句或VALUES中。序列可以由多个用户同时访问而不产生等待或锁定。
第一次查询要用 NEXTVAL,返回序列的初始值。
查询当前序列号用:CURRVAL,返回的是最后一次引用NEXTVAL返回的值。
查询下一个序列号用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;

人气教程排行