官档学习序列

序列学习（和兜兜一起学习官档）

 

1、  权限

本身拥有system权限，或者拥有create any sequence权限

 

2、  创建SQL 范例

CREATE SEQUENCE emp_sequence

      INCREMENT BY 1

      START WITH 2

      NOMAXVALUE

      NOCYCLE

      CACHE 10;

 

序列名字：emp_sequence                                                             【emp_sequence】

递增数：1                                                                                            【INCREMENT BY 1】

开始数：2 (如循环的话，循环第二次开始的数会是1)      【START WITH 2】

没有最大限制：nomaxvalue (实际序列是有最大限制的)   【NOMAXVALUE】

不循环（序列达到maxvalue不循环）                                     【NOCYCLE】

内存缓存：10个数字                                                                      【CACHE 10】

 

 

 

附表：

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6015.htm#SQLRF01314

1、 序列排序顺序（升序序列或降序序列）

Specifying only INCREMENTBY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

2、 Maxvalue、minvalue限制

Maxvalue：正数最大限制28位数，负数最大限制27位

Minvalue：正数最大限制28位数，负数最大限制27位

MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. MAXVALUE must be equal to or greater than STARTWITH and must be greater than MINVALUE.

MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. MINVALUE must be less than or equal to STARTWITH and must be less than MAXVALUE.

3、 nomaxvalue、nominvalue限制

nomaxvalue：升序最大限制10²⁸-1，降序最大限制  -1

nomaxvalue：升序最小限制1，降序最大限制  -(10²⁷ -1)

NOMAXVALUE  Specify NOMAXVALUE to indicate a maximum value of 10²⁸-1 for an ascending sequence or -1 for a descending sequence. This is the default.

NOMINVALUE  Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10²⁷ -1) for a descending sequence. This is the default.

4、nocycle与cycle 不同

         Nocycle达到maxvalue后，不产生新的序列值，

并报错（ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated）

         Cycle循环升序序列，如果你指定最小值，循环后从最小值开始

CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE  Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

5、 cache与nocache

cache有一定的性能优化，oracle推荐在集群环境下使用(但是DML操作未提交保存在内存的序列值会消失，会有出错的危险存在)

nocache 不会预先分配序列值

如果不指明cache or nocache 默认缓存20个数

 

6、order 与noorder

ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

NOORDER  Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.