官檔學習序列

序列學習（和兜兜一起學習官檔）

 

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.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-753345/，如需轉載，請註明出處，否則將追究法律責任。