How Does Oracle replace SQL Server auto-increment fields?

 

Name

Create sequence-CREATE a new serial number generator

Syntax

Create sequence seqname [INCREMENT increment]

[MINVALUE minvalue] [MAXVALUE maxvalue]

[START start] [CACHE cache] [CYCLE]

Input

Seqname

Name of the serial number to be created.

Increment

The INCREMENT increment clause is optional. A positive number will generate an ascending sequence, and a negative number will generate a descending sequence. The default value is 1 (1 ).

Minvalue

The optional sub-statement MINVALUE minvalue determines the minimum value that can be generated for a sequence. The default value is that the ascending sequence is 1 and the descending value is-2147483647.

Maxvalue

Use the Optional Clause MAXVALUE maxvalue to determine the maximum value of the sequence. The default values are 2147483647 and-1 respectively.

Start

The optional START start clause allows the sequence to START from any position. The default initial value is that the ascending sequence is minvalue, And the descending sequence is maxvalue.

Cache

The CACHE option preallocates the serial number and stores it in the memory for quick access. The minimum value (also the default value) is 1 (only one value can be generated at a time, that is, no cache ).

CYCLE

 

The optional CYCLE keyword can be used to reset and continue when the sequence reaches the maximum (maxvalue) or minimum (minvalue. if the limit is reached, the next data generated will be the minimum value or the maximum value ).

Example:

Create sequence emp_sequence

Increment by 1 -- add several

Start with 1 -- count from 1

NOMAXVALUE -- do not set the maximum value

NOCYCLE -- always accumulate without repeating

CACHE 10;

Usage:

Insert into table_name values (emp_sequence.nextval ,......);

Sequence Value Added by emp_sequence.nextval

Current emp_sequence.currval Value

 

Note that:

-The first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment,

Then return the added value. CURRVAL always returns the value of the current sequence, but in the first NEXTVAL

You can use CURRVAL only after initialization. Otherwise, an error occurs. NEXTVAL increases the value of sequence once,

Therefore, if you use multiple NEXTVAL statements, the values are different. Understand?

-If the CACHE value is specified, oracle can place some sequence in the memory in advance, so that the access is fast.

Some.

After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped, for example

The database suddenly fails to shut down abort, and the sequence in the cache will be lost.

To prevent this situation by using nocache when creating sequence.