ORACLE SEQUENCE

摘自oracle文档

Purpose

    Use the CREATE SEQUENCEstatement to create asequence, which 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
INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.
■ To create a sequence that increments without bound, for ascending sequences,omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify theNOMINVALUE.
■ To create a sequence that stops at a predefined limit, for an ascending sequence,specify a value for the MAXVALUE parameter. For a descending sequence,specify a value for the MINVALUE parameter. Also specifyNOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
■ To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE andMINVALUEparameters. Also specifyCYCLE.if you do not specify MINVALUE, then it defaults toNOMINVALUE, which is the
value 1.
INCREMENT BY Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have
28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If
the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

START WITH Specify the first sequence number to be generated. Use this clause 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 minimum value of the sequence. For descending sequences,
the default value is the maximum value of the sequence. This integer value can
have 28 or fewer digits.
MAXVALUE Specify the maximum value the sequence can generate. This integer
value can have 28 or fewer digits. MAXVALUE must be equal to or greater than
START WITH and must be greater thanMINVALUE.
NOMAXVALUE SpecifyNOMAXVALUEto indicate a maximum value of 1027for an
ascending sequence or -1 for a descending sequence. This is the default.
Note: This value is not necessarily the value to which an
ascending cycling sequence cycles after reaching its maximum or
minimum value.

MINVALUE Specify the minimum value of the sequence. This integer value can
have 28 or fewer digits. MINVALUE must be less than or equal toSTART WITHand
must be less than MAXVALUE.
NOMINVALUE SpecifyNOMINVALUEto indicate a minimum value of 1 for an
ascending sequence or -1026for a descending sequence. This is the default.
CYCLE SpecifyCYCLEto 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 SpecifyNOCYCLEto indicate that the sequence cannot generate more
values after reaching its maximum or minimum value. This is the default.
CACHE Specify how many values of the sequence the database preallocates and
keeps in memory for faster access. This integer value can have 28 or fewer digits.
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. You cannot cache more values
than will fit in a given cycle of sequence numbers. Therefore, the maximum value
allowed for CACHE must be less than the value determined by the following
formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, all cached sequence values that have not been used in
committed DML statements are lost. The potential number of lost values is equal to
the value of the CACHE parameter.
NOCACHE SpecifyNOCACHEto indicate that values of the sequence are not
preallocated. If you omit both CACHE andNOCACHE, the database caches 20
sequence numbers by default.
ORDER SpecifyORDERto 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.
Note: Oracle recommends using theCACHEsetting to enhance
performance if you are using sequences in a Real Application
Clusters environment.

ORDER is necessary only to guarantee ordered generation if you are using Oracle
Database with Real Application Clusters. If you are using exclusive mode, sequence
numbers are always generated in order.
NOORDER SpecifyNOORDERif you do not want to guarantee sequence numbers
are generated in order of request. This is the default.
Example
Creating a Sequence: ExampleThe following statement creates the sequence
customers_seq in the sample schemaoe. This sequence could be used to provide
customer ID numbers when rows are added to the customerstable.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
The first reference to customers_seq.nextvalreturns 1000. The second returns
1001. Each subsequent reference will return a value 1 greater than the previous
reference.