Detailed usage of sequence (SEQUENCE) in Oracle Database

Http://database.51cto.com/art/201108/280742.htm

In the Oracle database , what is a sequence ? What it is used for. A sequence (SEQUENCE) is actually a serial number generator that can automatically generate serial numbers for rows in a table, generating a set of equal-interval values (type numbers). Its primary purpose is to generate a primary key value for the table, to be referenced in the INSERT statement, to check the current value through a query, or to increase the sequence to the next value.

Creating a sequence requires the Create sequence system permissions. The syntax for creating a sequence is as follows:

CREATE SEQUENCE sequence name [INCREMENT by n] [START with n] [{maxvalue/minvalue n| Nomaxvalue}] [] [{CACHE n| NoCache}];

INCREMENT by is used to define the step size of the sequence, if omitted, the default is 1, and if a negative value is present, the values of the sequence are decremented by this step.

The START with defines the initial value of the sequence (that is, the first value produced), and the default is 1.

MAXVALUE defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option, representing no maximum definition, in which case the maximum value that the system can produce for an ascending sequence is 10 27; For a descending sequence, the maximum value is-1.

MinValue defines the minimum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a minimum value definition, in which case the minimum value that the system can produce for a descending sequence is 10 26; For an ascending sequence, the minimum value is 1.

Cycle and nocycle indicate whether the value of the sequence builder loops after it reaches the limit value. Cycle represents loops, and nocycle represents not loops. Loops to the minimum when the increment sequence reaches its maximum value, and loops to the maximum value for the descending sequence when it reaches the minimum value. If you do not loop, and the limit is reached, the new value continues to produce an error.

Cache (buffering) defines the size of the memory block that holds the sequence, and defaults to 20. NoCache indicates no memory buffering of the sequence. Memory buffering of the sequence can improve the performance of the sequence.

The syntax for deleting a sequence is:

DROP SEQUENCE sequence name;

which

The person who deletes the sequence should be the creator of the sequence or a user with the drop any sequence system permissions. Once the sequence is deleted, it cannot be referenced.

Some parts of the sequence can also be modified in use, but the SATRT with option cannot be modified. Changes to the sequence affect only the subsequent sequence number, and the number that has been generated is unchanged.

The syntax for modifying a sequence is as follows:

Create and delete a sequence

Example 1: Create a sequence:

CREATE SEQUENCE ABC INCREMENT by 1-START with MAXVALUE 9999999 nocycle NoCache;

Execution results:

The sequence has been created.

Step 2: Delete the sequence:

DROP SEQUENCE ABC;

Execution results:

The sequence has been discarded.

Description: The sequence named above is ABC, an increment sequence with an increment of 1 and an initial value of 10. The sequence does not loop and does not use memory. The minimum value is not defined, the default minimum is 1, and the maximum value is 9 999 999.

use of sequences

If you have created a sequence, how can you reference a sequence? The method uses Currval and Nextval to refer to the value of the sequence.

In the process of numbering, the causes of gaps are varied. If a stored procedure picks a number from a sequence, it is defined as a local variable, but it is never used, and the number is lost. It will no longer return to the original sequence, resulting in a gap in the numerical sequence. There is no need to worry about this in relational database models. But sometimes people care about this, and these people want to know which numbers are missing.

Calling Nextval will generate the next sequence number in the series, calling to indicate the name of the sequence, which is invoked in the following way:

The name of the sequence. Nextval

Currval is used to produce the current value of a sequence, no matter how many times it is invoked, does not produce the next value of the sequence. If the sequence has not yet produced the next value of the sequence by calling Nextval, it is meaningless to refer to Currval first. The method that invokes the Currval, to indicate the sequence name, is invoked in the following way:

The name of the sequence. Currval.

The value that produces the sequence.

Step 1: Produce the first value of the sequence:

SELECT ABC. Nextval from DUAL;

Execution results:

Nextval------------------10

Step 2: Produce the next value of the sequence:

SELECT ABC. Nextval from DUAL;

Execution results:

Nextval-------------------11

The current value of the resulting sequence:

SELECT ABC. Currval from DUAL;

Execution results:

Currval--------------------11

Note: the first call to Nextval produces the initial value of the sequence, knowing that the initial value is 10 by definition. The second call produces 11 because the sequence has a step size of 1. Call Currval to display the current value of 11 without generating a new value. Oracle's analytic functions provide a much quicker way to check gaps. They enable you to still see the next line (lead) or the previous line (LAG) values while using full, set-oriented SQL processing.

About Oracle Database sequence knowledge is introduced here, I hope this introduction can bring you some harvest, thank you.