Oracle's sequence sequence

Sequence: A sequence is a data object that can be used to generate a unique integer. is a separate object in the database, not dependent on any tables, unique to Oracle. Used to automatically generate primary key values, you can use sequence: The inert statement of the subquery, update set, and so on. Creating a sequence: Create sequence Seqname

-increament by 1//1 per increase

-start with 1//growth starting from 1

-maxvalue 100//Maximum value is 100,nomaxvalue: no maximum value

-nocycle//always accumulate without loops

-nocache//No buffering

-Omit all options, then the default is increased from 1, increasing by 1 each time

The maximum value of a sequence is a 38-bit integer, and the starting value cannot be less than the minimum value

Key notes:

1. The cache parameter is intended for concurrent access, which tells Oracle to pre-allocate a sequence number collection and is stored in memory so that it can be accessed quickly. The size of this memory is determined by the cache, when multiple users access a sequence at the same time, is the Oracle's SGA read sequence current reasonable value, if the concurrent access is too large, but the size of the cache will produce sequence Cache related wait, affect the system performance, simply say is caching.
2. Using the cache may also be skipped, such as the database suddenly abnormal shut down,cache in the sequence will be lost, you can create the sequence again with NoCache to avoid.
3. SGA: The system global Zone, a set of shared memory structures that contains data and control information for an Oracle instance, primarily the memory area used to store database information.
4. Use of sequence:

1.nextval: Increase the value of sequence and then return the sequence value, if the cache is the next available value of the cache, otherwise take out sequence the next available value.

Create Table TableName (

Id Number primary Key,

Name VARCHAR2

）;

Insert into tablename values (seqname.nextval, ' xiaoming ');

Querying the current sequence: select Seqname.nextval from dual;

2.currval: The current connection last use the value of the sequence, that is: the value of the current sequence, after the first nextval initialization can be used, otherwise error, select Seqname.currval from dual;

3. Modify the sequence: Alter sequence seqname[increament By][maxval][minval][cycle][cache], note that the starting value of the sequence can not be changed, if you want to change, you must first drop the sequence.

4. Delete sequence drop sequence seqname;

Note: auto_increament in MySQL is similar to sequence,

Create Table TaB2 (

ID number NOT NULL auto_increment,

Primary Key (ID)

）