Sequence details in Oracle

Source: Internet
Author: User
The first three paradigm of database design is that the independent table structure must have a unique primary key to identify the data in the table.

The first three paradigm of database design is that the independent table structure must have a unique primary key to identify the data in the table.

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:

In Oracle, if you want to set the primary key of an independent table structure to auto-increment, it involves the use of Sequence in Oracle 10 Gb.

(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

;

Syntax description:

Sequence_Name:Define the sequence name [meaningful name].

Start With (start_num): used to define the initial value of the sequence. Optional. The default value is 1.

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

MaxValue (maximum_num ):Sets the maximum integer of sequence auto-increment.Maximum_numMust be greater than or equalStart_numThat is, the initial value. At the same time, 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_numIt must be smaller than maximum_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: When the sequence auto-increment to the maximum or minimum value is specified, no integer can be generated. NoCycle is the default value.

Cache (cache_num): specifies the number of Integers to be retained in the memory. the default cache format is 20. the number of integers that can be cached must be 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 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 according to the request Order [uncommon settings]. You can use the Order option only when using Real Application Cluster (RAC.

Noorder: that is, (above); otherwise (not in details) NoOrder is the default value of the system.

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:

Nocache;

(B) Use Sequence-Use Sequence

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

Although the initial value of the product_sequence sequence is set to 1 at the time of definition, this value is not actually initialized. Before the current value of the sequence is retrievedNextvalTo initialize the sequence. When product_sequence.Nextval is selected, the sequence is initialized to 1. The following is an instance.


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.

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.

The query result is as follows:

When querying two values, Nextval goes before. 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 is Currval.CurrvalAt the same time, it is the only window for the external access sequence value. 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.

Execution result:

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

The values of the sequence are:Maximum 10Once again, it is restored to the initial value 1 to implement a loop. If the re-execution is actually the same as the first execution, the Cycle implementation sequence is defined 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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.