3,490
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
jiezhi 2004-01-05
- 打赏
- 举报
就像一個全局變量似的，某個用戶select seq.nextval後，序列的當前值就變化了
snowy_howe 2004-01-05
- 打赏
- 举报
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.
Keywords and Parameters
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.
If you specify none of the following clauses, you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only 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 the NOMINVALUE.
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 specify the NOCYCLE. 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 and MINVALUE parameters. Also specify the CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE (that is, the value 1).
INCREMENT BY
specifies 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 increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.
START WITH
specifies 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 sequence's minimum value. For descending sequences, the default value is the sequence's maximum value. This integer value can have 28 or fewer digits.
Note: This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.
MAXVALUE
specifies 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 than MINVALUE.
NOMAXVALUE
specifies a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE
specifies the sequence's minimum value. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE
specifies a minimum value of 1 for an ascending sequence or -(1026) for a descending sequence. This is the default.
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. This is the default.
CACHE
specifies how many values of the sequence Oracle 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
specifies that values of the sequence are not preallocated.
If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.
ORDER
guarantees that sequence numbers are generated in order of request. You may want to use this clause 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 with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER
does not guarantee sequence numbers are generated in order of request. This is the default.
Example
The following statement creates the sequence ESEQ:
CREATE SEQUENCE eseq
INCREMENT BY 10;
The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.
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.
Keywords and Parameters
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.
If you specify none of the following clauses, you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only 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 the NOMINVALUE.
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 specify the NOCYCLE. 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 and MINVALUE parameters. Also specify the CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE (that is, the value 1).
INCREMENT BY
specifies 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 increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.
START WITH
specifies 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 sequence's minimum value. For descending sequences, the default value is the sequence's maximum value. This integer value can have 28 or fewer digits.
Note: This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.
MAXVALUE
specifies 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 than MINVALUE.
NOMAXVALUE
specifies a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE
specifies the sequence's minimum value. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE
specifies a minimum value of 1 for an ascending sequence or -(1026) for a descending sequence. This is the default.
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. This is the default.
CACHE
specifies how many values of the sequence Oracle 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
specifies that values of the sequence are not preallocated.
If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.
ORDER
guarantees that sequence numbers are generated in order of request. You may want to use this clause 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 with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER
does not guarantee sequence numbers are generated in order of request. This is the default.
Example
The following statement creates the sequence ESEQ:
CREATE SEQUENCE eseq
INCREMENT BY 10;
The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.
beckhambobo 2004-01-05
- 打赏
- 举报
详尽例子:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_615a.htm#SQLRF01314
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_615a.htm#SQLRF01314
cheng9614 2004-01-05
- 打赏
- 举报
看来只有 jiezhi 理解了我的意思，不过还是要谢谢各位
我想知道它在数据库内部是怎么实现的
我想知道它在数据库内部是怎么实现的
lianhg 2004-01-05
- 打赏
- 举报
是创建吗？
CREATE SEQUENCE mySeq
INCREMENT BY 1
START WITH 1;
CREATE SEQUENCE mySeq
INCREMENT BY 1
START WITH 1;
