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;