关于序列

官方文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14231/views.htm#i1106548

 

Creating Sequences

     To create a sequence in your schema, you must have the CREATESEQUENCE system privilege. To create a sequence in anotheruser's schema, you must have the CREATE ANY SEQUENCEprivilege.

Create a sequence usingthe CREATE SEQUENCE statement. For example, thefollowing statement creates a sequence used to generate employeenumbers for the empno column of the emptable:

CREATE SEQUENCE emp_sequence
      INCREMENT BY 1
      START WITH 1
      NOMAXVALUE
      NOCYCLE
      CACHE 10;

      Notice that several parameters can be specified to control thefunction of sequences. You can use these parameters to indicatewhether the sequence is ascending or descending, the starting pointof the sequence, the minimum and maximum values, and the intervalbetween sequence values. The NOCYCLE option indicatesthat the sequence cannot generate more values after reaching itsmaximum or minimum value.

 

     The CACHE clause preallocates a set of sequencenumbers and keeps them in memory so that sequence numbers can beaccessed faster. When the last of the sequence numbers in the cachehas been used, the database reads another set of numbers into thecache.

 

    The database might skip sequence numbers if you choose to cache aset of sequence numbers. For example, when an instance abnormallyshuts down (for example, when an instance failure occurs or aSHUTDOWN ABORT statement is issued), sequence numbersthat have been cached but not used are lost. Also, sequence numbersthat have been used but not saved are lost as well. The databasemight also skip cached sequence numbers after an export and import.See OracleDatabase Utilities for details.

 

 

---下边是创建约束的语法：

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314

 

Semantics

schema

Specify the schema to contain the sequence. If you omitschema,then Oracle Database creates the sequence in your own schema.

sequence

Specify the name of the sequence to be created.

If you specify none of the following clauses, then you create anascending sequence that starts with 1 and increases by 1 with noupper limit. Specifying only INCREMENT BY-1 creates a descending sequence that starts with -1 and decreaseswith no lower limit.

• Tocreate a sequence that increments without bound, for ascendingsequences, omit the MAXVALUE parameter or specifyNOMAXVALUE. For descending sequences, omit theMINVALUE parameter or specify theNOMINVALUE.

• To create a sequence that stops at a predefined limit, for anascending sequence, specify a value for the MAXVALUEparameter. For a descending sequence, specify a value for theMINVALUE parameter. Also specify NOCYCLE.Any attempt to generate a sequence number once the sequence hasreached its limit results in an error.

• To create a sequence that restarts after reaching apredefined limit, specify values for both the MAXVALUEand MINVALUE parameters. Also specifyCYCLE. If you do not specify MINVALUE,then it defaults to NOMINVALUE, which is the value1.

INCREMENTBY Specifythe interval between sequence numbers.

 

This integer value can be any positive or negativeinteger, but it cannot be 0.

--这个整数值可以是任意的正数或者负数，但不能是0.

 

  注意：从此处说明下边的MAXVALUE与 MINVALUE所涉及 序列 的升序 和

降序的产生就是由于参数INCREMENTBY可以为正数或者负数导致。

 

This value can have 28 or fewer digits. The absolute ofthis value must be less than the difference ofMAXVALUE and MINVALUE. If this value isnegative, then the sequence descends. If the value is positive,then the sequence ascends. If you omit thisclause, then the interval defaults to 1.

 

STARTWITH  Specifythe first sequence number to be generated. Use this clause to startan ascending sequence at a value greater than its minimum or tostart a descending sequence at a value less than its maximum.For ascending sequences, the default value isthe minimum value of the sequence. For descending sequences, thedefault value is the maximum value of the sequence. Thisinteger value can have 28 or fewer digits.

 

Note:

This value is not necessarily the value to which an ascendingcycling sequence cycles after reaching its maximum or minimumvalue.

 

MAXVALUE Specifythe maximum value the sequence can generate. This integer value canhave 28 or fewer digits. MAXVALUE must be equal to orgreater than START WITH and must begreater than MINVALUE.

 

NOMAXVALUE  SpecifyNOMAXVALUE to indicate a maximum value of10²⁷ for an ascending sequence or -1 for a descendingsequence. This is the default.

 

MINVALUE Specifythe minimum value of the sequence. This integer value can have 28or fewer digits. MINVALUE must be less than or equalto START WITH and must be less thanMAXVALUE.

 

NOMINVALUE  SpecifyNOMINVALUE to indicate a minimum value of 1 for anascending sequence or -10²⁶ for a descending sequence.This is the default.

 

CYCLE  SpecifyCYCLE to indicate that the sequence continues togenerate values after reaching either its maximum or minimumvalue.

 

After an ascending sequence reaches its maximum value,it generates its minimum value.--在一个升序序列到达它的最大值之后，它将产生最小值。

 

After a descending sequence reaches its minimum, itgenerates its maximum value.

 

NOCYCLE  SpecifyNOCYCLE to indicate that the sequence cannot generatemore values after reaching its maximum or minimum value. This isthe default.

 

CACHE Specifyhow many values of the sequence the database preallocates and keepsin memory for fasteraccess.--为了访问便捷，此参数指定了在内存中数据库能够预存和保留多少个序列的值。

 

 This integer value can have 28 orfewer digits. The minimum value for this parameter is 2. Forsequences that cycle, this value must be less than the number ofvalues in the cycle.

 

You cannot cache more values than will fit in a givencycle of sequence numbers. Therefore, the maximum value allowed forCACHE must be less than the value determined by thefollowing formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence valuesthat have not been used in committed DML statements are lost. Thepotential number of lost values is equal to the value of theCACHE parameter.

--如果系统错误发生，在已提交的DML语句中还没使用的所有缓存的序列值都将丢失。丢失值的可能数量和CACHE参量的值相等。

 

Note:

Oracle recommends using the CACHE setting to enhanceperformance if you are using sequences in a Real ApplicationClusters environment.

NOCACHE  SpecifyNOCACHE to indicate that values of the sequence arenot preallocated. If you omit both CACHE andNOCACHE, the database caches 20 sequence numbers bydefault.

 

 

--下边是怎么引用一个序列

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14231/views.htm#sthref3081

 

 

Referencing a Sequence

 

A sequence is referenced in SQL statements with theNEXTVAL and CURRVAL pseudocolumns; eachnew sequence number is generated by a reference to the sequencepseudocolumn NEXTVAL, while the current sequencenumber can be repeatedly referenced using the pseudo-columnCURRVAL.

NEXTVAL and CURRVAL are not reservedwords or keywords and can be used as pseudocolumn names in SQLstatements such as SELECT, INSERT, orUPDATE.

Generating Sequence Numbers with NEXTVAL

To generate and use a sequence number, referenceseq_name.NEXTVAL. For example, assume acustomer places an order. The sequence number can be referenced ina values list. For example:

INSERT INTO Orders_tab (Orderno, Custno)
    VALUES (Order_seq.NEXTVAL, 1032);

Or, the sequence number can be referenced in theSET clause of an UPDATE statement. Forexample:

UPDATE Orders_tab
    SET Orderno = Order_seq.NEXTVAL
    WHERE Orderno = 10112;

The sequence number can also be referenced outermostSELECT of a query or subquery. For example:

SELECT Order_seq.NEXTVAL FROM dual;

As defined, the first reference toorder_seq.NEXTVAL returns the value 1. Each subsequentstatement that references order_seq.NEXTVAL generatesthe next sequence number (2, 3, 4,. . .). The pseudo-columnNEXTVAL can be used to generate as many new sequencenumbers as necessary. However, only a single sequence number can begenerated for each row. In other words, if NEXTVAL isreferenced more than once in a single statement, then the firstreference generates the next number, and all subsequent referencesin the statement return the same number.

Once a sequence number is generated, the sequence number isavailable only to the session that generated the number.Independent of transactions committing or rolling back, other usersreferencing order_seq.NEXTVAL obtain unique values. Iftwo users are accessing the same sequence concurrently, then thesequence numbers each user receives might have gaps becausesequence numbers are also being generated by the other user.

Using Sequence Numbers with CURRVAL

To use or refer to thecurrent sequence value of your session, referenceseq_name.CURRVAL. CURRVALcan only be used if seq_name.NEXTVAL hasbeen referenced in the current user session (in the current or aprevious transaction). CURRVAL can be referenced asmany times as necessary, including multiple times within the samestatement. The next sequence number is not generated untilNEXTVAL is referenced. Continuing with the previousexample, you would finish placing the customer's order by insertingthe line items for the order:

INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
    VALUES (Order_seq.CURRVAL, 20321, 3);

INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
    VALUES (Order_seq.CURRVAL, 29374, 1);

Assuming the INSERT statement given in the previoussection generated a new sequence number of 347, both rows insertedby the statements in this section insert rows with order numbers of347.

Uses and Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in thefollowing places:

• VALUES clause of INSERT statements

• The SELECT list of a SELECTstatement

• The SET clause of an UPDATEstatement

CURRVAL and NEXTVAL cannot be used inthese places:

• A subquery

• A view query or materialized view query

• A SELECT statement with the DISTINCToperator

• A SELECT statement with a GROUPBY or ORDER BY clause

• A SELECT statement that is combined with anotherSELECT statement with the UNION,INTERSECT, or MINUS set operator

• The WHERE clause of a SELECTstatement

• DEFAULT value of a column in a CREATETABLE or ALTER TABLEstatement

• The condition of a CHECK constraint