Sequence usage explained in Oracle database

A sequence (SEQUENCE) is a sequence number generator that automatically generates a sequence number for a row in a table, producing a set of equal-spaced values (type number). Its main purpose is to generate the table's primary key value, which can be referenced in the INSERT statement, or the current value can be checked through the query, or the sequence will be increased to the next value.

AD:WOT2015 Internet operations and developers Conference selling tickets

sequence (SEQUENCE) is the serial number generator, which automatically generates serial numbers for the rows in the table, producing a set of equal-spaced values (type number). Its main purpose is to generate the table's primary key value, which can be referenced in the INSERT statement, or the current value can be checked through the query, or the sequence will be increased to the next value.

Creating a sequence requires the Create sequence system permission. The creation syntax for a sequence is as follows:

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

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

START with defines the initial value of the sequence (that is, the first value produced), which defaults to 1.

MAXVALUE defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum definition, at which point the system can produce a maximum value of 10 for the increment sequence, and the maximum value for the descending sequence is-1.

MinValue defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means there is no minimum definition, and for a descending sequence, the minimum value that the system can produce is 10 of the 26 square, and for the increment sequence, the minimum value is 1.

Cycle and nocycle Indicate whether a loop occurs when the value of the sequence generator reaches the limit. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

The cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a 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 the user who has the drop any sequence system permission. Once a 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 resulting ordinal number, and the number that has been generated is unchanged. The syntax for modifying a sequence is as follows:

Creating and deleting sequences

Example 1: Creating a sequence:

CREATE SEQUENCE ABC INCREMENT by 1 START with ten MAXVALUE 9999999 nocycle NOCACHE;

Execution results

The sequence has been created.

Step 2: Delete the sequence:

DROP SEQUENCE ABC;

Execution Result:

The sequence has been discarded.

Description: The sequence created above is named ABC, which is 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 value is 1 and the maximum value is 9 999 999.

Use of sequences

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

In the process of numbering, there are various reasons for the gap to occur. If a stored procedure picks a number from a sequence, it is set 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 numeric sequence. There is no need to worry about this in the relational database model. But sometimes people care about it, and these people want to know what numbers are missing.

Calling Nextval will generate the next sequence number in the series, which is called with the name of the sequence, which is called in the following way:

The sequence name. Nextval

Currval is used to produce the current value of a sequence, no matter how many times it is called, the next value of the sequence is not produced. If the sequence has not yet produced the next value of the sequence by calling Nextval, it does not make sense to refer to Currval first. Call Currval the same way, to indicate the sequence name, which is called in the following way:

The sequence name. Currval.

The value that produces the sequence.

Step 1: Generate the first value of the sequence:

SELECT ABC. Nextval from DUAL;

Execution Result:

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

Step 2: Generate the next value of the sequence:

SELECT ABC. Nextval from DUAL;

Execution Result:

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

Produces the current value of the sequence:

SELECT ABC. Currval from DUAL;

Execution Result:

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

Note: The first call to Nextval produces the initial value of the sequence, which, by definition, knows the initial value is 10. The second call produces 11 because the step of the sequence is 1. Call Currval, which displays the current value 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 values of the next line (lead) or the previous line (LAG) while using the full, collection-oriented SQL processing.

View sequence

The data dictionary user_objects can be used to view the user-owned sequences.

The Data dictionary user_sequences lets you view the settings for a sequence.

Example: Viewing a sequence of users:

Execution Result:

Sequence_name min_value max_value increment_by last_number

Description: The current user has two sequences: ABC and BookID.

Sequence usage explained in Oracle database