Sequence usage 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.It is extremely simple. The encoding is as follows:

Typeid Int   Not   Null   Primary   Key   Identity (1 ,1 ),

InOracleIf you want to set the primary key of an independent table structure to auto-incrementOracle 10GAbout the sequence (Sequence.

(A) Sequence-Sequence definition syntax

SEquence-a SEquence is a database item. It generatesInteger 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
Create Sequence 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 ):This parameter is used to define the initial values of a sequence. Optional. The default value is1.

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_numMust be greater than the minimum limit of the sequenceMinimum_num. [Warranty intentionally].

NoMaxValue:Is the default value set by the system for the sequence, that is, the maximum value of the ascending sequence is10 to the power of 27. The maximum value of the descending sequence is-1.NoMaxValueIt 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:Even if the default value of the lower limit of the sequence auto-increment is. the minimum value of the ascending sequence is1, The minimum descending sequence isPower 26 of negative 10.NoMinValueThe default value.

Cycle:Specify to continue generating integers even if the maximum or minimum value of the sequence has been reached. 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:On the contrary, an integer cannot be generated when the sequence is specified to auto-increment to the maximum or minimum value.NoCycleIs the default value.

Cache (cache_num ):Number of Integers to be retained in the memory. The default cache format is20. The number of integers that can be cached is at least 2. the maximum number of integers that can be cached is:Cell (maximum_num-minimum_num)/ABS (increment_num)The algorithm is Cell (maximum upper limit of the sequence-Minimum lower limit,)/ABS (incremental 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 connection 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)Only availableOrderOption settings.

Noorder: That is, (the above) and vice versa (not to be repeated)NoOrderIs 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  Create Sequence product_sequence
3  Start With  1   -- The default value is 1.
4  Increment By  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 definingProduct_sequenceThe initial value is 1, but the value is not actually initialized. Before the current value of the search sequence, the next value of the search sequence must beNextvalTo initialize the sequence.Product_sequence.NextvalThe sequence is initialized as 1. The following is an instance.

1  -- Initialization sequence
2  Select Product_sequence.nextval From ProductType
3 
4  -- If ProductType is set, you must create the following table ProductType before initialization:
5  Create   Table Producttype
6  (
7  Typeid Integer   Constraint Producttype_pk Primary   Key ,
8  Typename Varchar (120 ) Default ' CastChen Test Date! '   Not   Null ,
9  Createdate date Default Sysdate Not   Null
10  ); 

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  Select   Product_sequence.currval   From ProductType

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  Select   Product_sequence.nextval , Product_sequence.currval   From ProductType

The query result is as follows:

When two values are queriedNextvalPreviously, I mentioned that querying againNextvalThe 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.currvalThe current value of the sequence is obtained directly. 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.
Select   Product_sequence.nextval   From ProductType

Execution result:

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

The values of the sequence are:Maximum 10Restore againInitial Value 1To implement a loop. If the re-execution is actually the same as the first execution,CycleThe implementation sequence is defined cyclically within 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   Into ProductType (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 increment By  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  Drop Sequence 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.