Sequence usage in Oracle 10 GB

 

The first of the three major paradigms of database design is that the independent table structure must have a unique primary key to identify the table data. in the past, Microsoft's SQL Server (duo version) platform. manually encode the primary key of the table. setting it as an auto-increment column is extremely simple. 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 ),

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

-- Define Complete Sequence syntax

Create Sequence Sequence_Name

[Start With start_num] -- the 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: defines 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_num must be greater than or equal to start_num, that is, the initial value. At the same time, maximum_num must be greater than the minimum value minimum_num. [intentionally]

 

NoMaxValue: it is the default value set by the system for the sequence. That is, the maximum value of the ascending sequence is 10 to the 27 power. 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_num must be smaller than or equal to start_num, that is, the initial value. minimum_num must be smaller than maximum_num.

 

NoMinvalue: the minimum value of the ascending sequence is 1, and the minimum value of the descending sequence is the 26 power of the 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. this rule is used for loop.

 

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: specifies that integer data is not cached. [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. we recommend that you have a detailed description after creating Cache storage that 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:

 

1 -- create Sequence

2 create sequence product_sequence

3 start with 1 -- the default value is 1.

4 increment by 1 -- each increment is 1 by default

5 maxvalue 10 -- maximum increment limit

6 minvalue 1 -- Minimum Increment Limit

7 cycle

8 nocache;

(B) Use Sequence-Use Sequence

 

The 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 we specify the initial value of the sequence product_sequence as 1 during the definition, this value is not actually initialized. before retrieving the current value of a sequence, you must use Nextval to initialize the sequence. when product_sequence.Nextval is selected, the sequence is initialized to 1. the following is an example.

 

1 -- initialization sequence

2 select product_sequence.nextval from ProductType

3

4 -- ProductType indicates that the table ProductType must be created before initialization as follows:

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 );

The query result is as follows:

 

 

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

 

1 -- get the current value of the sequence

2 select product_sequence.currval from ProductType

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 instance. Note the order of nextval and Currval.

 

1 -- view 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:

 

 

Nextval comes first when two values are queried. as mentioned above, querying Nextval again can obtain the next value of the sequence. therefore, the current value of the sequence Currval is 2. in fact, we can understand this process: Nextval actually assigns values to the sequence through queries, and the value assignment object is Currval. currval is also the only window for external access sequence values. product_sequence.currval gets the current value of the sequence directly. similarly, we can place product_sequence.currval in the primary key assignment instead of defining it each time. is it close to our goal again.

 

Note that we have used the Cycle option in the definition: that is, the cyclic effect is generated when the sequence is auto-incrementing to the maximum or minimum value. Let's take a look at the instance.

 

-- Always execute the value assignment statement until the maximum value of the sequence reaches 10.

Select product_sequence.nextval from ProductType

Execution result:

 

 

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

 

 

The maximum value of the sequence is 10, and the value is restored to the initial value 1 again to achieve a loop. if the re-execution is actually the same as the first execution, the Cycle implementation sequence is defined in a specific range cyclically.

 

(C) Use sequence to fill in the primary key-(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. here I want to mention the Cache option again. 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 replaces the primary key value definition.

2 insert into ProductType (typeid, Typename, Createdate)

3 values (product_sequence.nextval, 'sequence-defined primary key', 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 operation was basically implemented as we expected, but 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. the Oracle 10g sequence is actually 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. 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 the Alert Sequence clause to modify the Sequence. However, I often report errors and frequently modify the Sequence. The following restrictions apply to modifying the Sequence content:

 

(1) The initial values of the sequence 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.

 

From Monkey's blog