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

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 INCREMENT BY -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 START WITH 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 START WITH 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.