Sequence details in Oracle

The first three paradigm of database design is that the independent table structure must have a unique primary key to identify the table data.SQL Server (duoOn the platform. Manually encode the primary key in the table and set it as an auto-incrementing column. The encoding is as follows:

  -- The simple primary key is defined as follows and set to auto-increment (only the primary key definition is retrieved)
Typeid Int   Not   Null   Primary   Key   Identity ( 1 , 1 ),

InIn Oracle, if you want to set the primary key of an independent table structure to auto-incrementOracle 10g sequence (Sequence.

(A) Sequence-Sequence definition syntax

SEquence-SEquence is a database item. it generates an integer sequence .. the integers generated by sequences in Oracle can usually be used to fill the number-type primary key columns in the independent table structure. to achieve the same effect as SQL Server auto-increment. however, the sequence definition is very different from that of SQL Server. the Oracle 10g Official Development Manual is somewhat complicated. Let's take a look at the definition sequence (after streamlining, please refer to the official documentation for details .) syntax:

Code --Complete Sequence definition syntax
CreateSequence Sequence_Name
[Start With start_num]--Initial Value of auto-Increment
[Increment by increment_num]--Each increment
[{MaxValue maximum_num | NoMaxValue}]
[{MinValue minimum_num | NoMinValue}]
[{Cycle | NoCycle}]
[{Cache cache_num | NoCache}]
[{Order | NoOrder}];

Syntax description:

Sequence_Name:Define the sequence name [meaningful name].

Start With (start_num): used to define the initial values of a sequence. Optional. Default Value:1.

Increment By (increment_num): Specifies the Increment of each auto-Increment sequence. Optional. The default value is also1.

MaxValue (maximum_num ):Sets the maximum integer of sequence auto-increment.Maximum_numMust be greater than or equalStart_numThat is, the initial value.Maximum_num must be greater than the minimum limit of the sequence.Minimum_num. [intentionally].

NoMaxValue: the default value set by the system for the sequence. That is, the maximum value of the sequence in ascending order is10 to the power of 27. The maximum value of the descending sequence is-1.NoMaxValue is the default value of the system. Similarly, MinValue.

MinValue (minimum_num): sets the minimum and minimum integer for sequence auto-increment .,Minmum_numMust be less than or equalStart_numThat is, the initial value. AndMinimum_numMust be lessMaximum_num. Likewise.

NoMinvalue: the minimum value of the ascending sequence is1, The minimum descending sequence isPower 26 of negative 10.NoMinValue is the default value.

Cycle: Specify to continue generating integers even if the sequence has reached the maximum or minimum auto-increment value. when the ascending sequence reaches the maximum value. the next generated value is the minimum value, that is, the initial value. when the descending sequence reaches the minimum value. the maximum value of the next generated value. with this ruleLoop.

NoCycle: the opposite: no integer can be generated when the sequence is auto-incrementing to the maximum or minimum value.NoCycle is the default value.

Cache (cache_num): specifies the number of Integers to be retained in the memory. The default Cache format is20. The minimum number of integers that can be cached is 2. the maximum number of integers that can be cached is:Cell (maximum_num-minimum_num)/ABS (increment_num). algorithm: Cell (maximum upper limit of the sequence-Minimum lower limit,)/ABS (increment each time ).

NoCache: Specify integer data not applicable to cache. [I personally recommend that you do not use cache to store data. When the database is disconnected, the memory data is automatically cleared,Resulting in discontinuous values of the inserted data sequence.. Details about the effect are recommended after the Cache storage is not applicable ].

Order: Make sure to generate an integer in the Order of requests (not commonly used ).Real Application Cluster (RAC)Set the Order option.

Noorder: That is (above); reverse. (not to repeat)NoOrder is the default value.

To learn more about the syntax definition of the sequence Sequence, define a simple sequence Sequence, and implement auto-increment in the primary key column of the table. The Code is as follows:

1 --Create Sequence
2 CreateSequence product_sequence
3 StartWith 1 --The default value is 1.
4 IncrementBy 1--The default value of each increment is 1.
5 Maxvalue10 --Maximum Increment Limit
6 Minvalue1--Minimum Increment Limit
7 Cycle
8 Nocache;

(B) Use Sequence-Use Sequence

A sequence generates a series of integer numbers. A sequence contains two "pseudo columns", which are"Currval "and"Nextval", Which can be used to obtain the current value and the next value of the sequence respectively.

Although we specify the sequence when definingThe initial value of product_sequence is 1, but this value is not actually initialized. Before retrieving the current value of a sequenceNextvalTo initialize the sequence.During product_sequence.Nextval, the sequence is initialized to 1. The following is an instance.

1 --Initialization sequence
2 SelectProduct_sequence.nextvalFromProductType
3 
4 --If ProductType is set, you must create the following table ProductType before initialization:
5 Create TableProducttype
6 (
7 TypeidInteger ConstraintProducttype_pkPrimary Key,
8 TypenameVarchar(120)Default'CastChen Test Date!' Not Null,
9 Createdate dateDefaultSysdateNot Null
10 ); 

The query result is as follows:

After the first execution, the initialization value in the sequence is defined.1. If no initial value is specified during definition, the default value is 1.After successful initialization, we can obtain the value of the current sequence.

1 --Obtains the current sequence value.
2 SelectProduct_sequence.currvalFromProductType

The query result is as follows:

When Currval is queried, Nextval remains unchanged. nextval changes only when nextval is queried again to obtain the next value.The following is an example. Note the order of nextval and Currval.

1 --Check the values of the two pseudo columns at the same time. Note that Nextval is behind Currval.
2 SelectProduct_sequence.nextval, product_sequence.currvalFromProductType

The query result is as follows:

When two values are queriedNextval is in front of. As mentioned earlier, re-QueryNextvalThe next value of the sequence can be obtained. Therefore, the current value of the sequence Currval is 2. In fact, this process can be understood as follows:NextvalIn fact, the query is used to assign values to the sequence, and the value assignment object isCurrval.CurrvalIt is also a unique window for external access sequence values.Product_sequence.currval directly obtains the current value of the sequence. Similarly, we canProduct_sequence.currvalPut it in the primary key assignment, instead of manually defining it each time. Is it again close to our goal.

Note that we have usedCycleOption: that is, the loop effect is generated when the sequence is auto-incrementing to the maximum or minimum value. Let's look at the instance.

--Execute the value assignment statement until the maximum value of the sequence is 10.
SelectProduct_sequence.nextvalFromProductType

Execution result:

When we execute the value assignment statement again, the result is:

The values of the sequence are:Maximum 10Restore againThe initial value is 1 to implement a loop. If the re-execution is actually the same as the first execution,Cycle is used to define the sequence in a specific range.

(C) Fill the primary key with sequence-(Core content)

All of the above are preparations. The final result we want is to automatically assign values through sequences in the primary key column. of course, the Data Type of the table's primary key definition must be an integer. I will mention it again here.CacheThis option. when the sequence is used to fill in the primary key, it is usually set to NoCache by default. when the database connection is closed, All cached values will be lost. this causes the number of Primary keys to be discontinuous. use a sequence to fill in the primary key ID:

1 --When inserting data, the sequence value is defined instead of the primary key value.
2 Insert IntoProductType (typeid, Typename, Createdate)
3 Values(Product_sequence.nextval,'Use sequence to define primary keys',Default)

In this way, you do not need to view the value of the primary key every time you add data. You are afraid to insert duplicate data. The assignment of the primary key column is done by the sequence. I only need to focus on other places.

The above operations are basically implemented as we expected.I also want to raise a question:

When the primary key of the independent table structure is defined on the Microsoft SQL Server platform. multiple columns can be designated as the primary key of the table. that is, if multiple tables are merged and compared, the primary key is uniquely identified. whileOracle 10GIn fact, the sequence is separated from this relationship. The two are independent of each other. That is to say, the definition of the primary key is irrelevant to the assignment of the primary key.-The series. completely separated. The sequence is only responsible for assigning values to the primary key independently. The definition of the primary key constraint does not matter.

(D) modify and delete a sequence

You can use Alert SequenceClause is used to modify the sequence. However, I often report errors frequently when modifying the sequence. The following restrictions apply to modifying the sequence content:

(1)The sequence initial value cannot be modified.

(2) the minimum value of the sequence cannot be greater than the current value.

(3) the maximum value of the sequence cannot be smaller than the current value.

Modify the sequence increment:

1 --Change the sequence increment to 2.
2 Alert sequence product_sequence incrementBy 2;

The data query result is increased to 2 in each increment. If the sequence is not used, the sequence is deleted:

1 --The deletion sequence is extremely simple.
2 DropSequence product_sequence;

When we add sequences in multiple table tables, we can query the User_sequences table in the query system to obtain detailed sequence information of the relevant table. User_sequences is part of the dictionary table.

So far, the above is all the usage of sequences in Oracle 10 Gb.