oracle 序列升序降序,Oracle 官档学习序列

序列学习(和兜兜一起学习官档)

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

】

附表：

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

28-1

，降序最大限制

-1

nomaxvalue

：升序最小限制

1

，降序最大限制

-(10

27 -1)

NOMAXVALUE

Specify

NOMAXVALUE

to indicate a maximum value of 10

28-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

27 -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.