oracle序列创建的顺序,ORACLE序列：基本概念和使用说明

【IT168技术】本文主要讲述ORACLE序列的使用。

Create Sequence

你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限，

CREATESEQUENCE emp_sequence

INCREMENTBY1--每次加几个STARTWITH1--从1开始计数NOMAXVALUE--不设置最大值NOCYCLE--一直累加，不循环CACHE10NOORDER;--并行时取得序列的顺序

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL

CURRVAL=返回 sequence的当前值

NEXTVAL=增加sequence的值，然后返回 sequence 值

比如：

emp_sequence.CURRVAL

emp_sequence.NEXTVAL

可以使用sequence的地方：

①不包含子查询、snapshot、VIEW的 SELECT 语句

② INSERT语句的子查询中

③ INSERT语句的VALUES中

④UPDATE 的 SET中

可以看如下例子：

INSERTINTOempVALUES(empseq.nextval,'LEWIS','CLERK',7902, SYSDATE,1200,NULL,20);SELECTempseq.currvalFROMDUAL;

但是要注意的是：

第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白?

如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

Alter Sequence

你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .

Alter sequence 的例子

ALTERSEQUENCE emp_sequence

INCREMENTBY10MAXVALUE10000CYCLE--到10000后从头开始NOCACHE ;

影响Sequence的初始化参数：

SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。

可以很简单的Drop Sequence

DROPSEQUENCE order_seq;

关于Oracle的序列(Sequence)使用

序列是一数据库对象，利用它可生成唯一的整数。一般使用序列自动地生成主键值。

1、 建立序列命令

CREATESEQUENCE[user.]sequence_name[increment by n][start with n][maxvalue n | nomaxvalue][minvalue n | nominvalue];[NOCYCLE]--

INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：指定序列可生成的最大值。

NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。

MINVALUE：指定序列的最小值。

NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。

NOCYCLE：一直累加，不循环

2、 更改序列命令

ALTERSEQUENCE[user.]sequence_name[INCREMENT BY n][MAXVALUE n| NOMAXVALUE][MINVALUE n | NOMINVALUE];

修改序列可以：

①修改未来序列值的增量。

②设置或撤消最小值或最大值。

③改变缓冲序列的数目。

③指定序列号是否是有序。

注意：

第一次NEXTVAL返回的是初始值

可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .

3、 删除序列命令

DROPSEQUENCE[user.]sequence_name;

用于从数据库中删除一序列。

4、牛刀小试

创建一个序列号的语句：

--Create sequencecreatesequence NCME_QUESTION_SEQ

minvalue1maxvalue999999999999startwith1incrementby1nocache;

SQL中取序列号的用法：

SELECTNCME_QUESTION_SEQ.nextvalFROMdualSELECTNCME_QUESTION_SEQ.CURRVALFROMdual

CREATE SEQUENCE command

PURPOSE:

To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

SYNTAX:

CREATE SEQUENCE [schema.]sequence

[INCREMENT BY integer]

[START WITH integer]

[MAXVALUE integer | NOMAXVALUE]

[MINVALUE integer | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE integer | NOCACHE]

[ORDER | NOORDER]

where:

schema is the schema to contain the sequence. If you omit schema, Oracle creates the sequence in your own schema.

sequence is the name of the sequence to be created.

INCREMENT BY

specifies the interval between sequence numbers. This value can be  any positive or negative Oracle integer, but it cannot be 0. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.

MINVALUE

specifies the sequence's minimum value.

NOMINVALUE

specifies a minimum value of 1 for an ascending sequence or -10 for a descending sequence.

The default is NOMINVALUE.

MAXVALUE

specifies the maximum value the sequence can generate.

NOMAXVALUE

specifies a maximum value of 10 for a descending sequence.

The default is NOMAXVALUE.

START WITH

specifies the first sequence number to be generated. You can use this option to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the sequence's minimum value. For  scending sequences, the default value is the sequence's maximum value.

CYCLE

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

NOCYCLE

specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

The default is NOCYCLE.

CACHE

specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle.

NOCACHE

specifies that values of the sequence are not preallocated.

If you omit both the CACHE parameter and the NOCACHE option, Oracle caches 20 sequence numbers by default. However, if you are using  Oracle with the Parallel Server option in parallel mode and you  specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

ORDER

guarantees that sequence numbers are generated in order of request.

You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

NOORDER

does not guarantee sequence numbers are generated in order of request. If you omit both the ORDER and NOORDER options, Oracle chooses NOORDER by default. Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.

PREREQUISITES:

To create a sequence in your own schema, you must have CREATE SEQUENCE privilege.

To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege. If you are using Trusted Oracle in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of the schema to contain the sequence.

example

createsequence seqTest

incrementby1startwith0maxvalue10000minvalue0nocache

cycle

noorder;selectseqTest.nextvalfromdual;selectseqTest.currvalfromdual;altersequence seqTest

incrementby5;dropsequence seqTest;