SEQUENCE usage in Oracle databases

InOracle DatabaseWhat isSequenceWhat about it? What is its purpose? SEQUENCE is actually a serial number generator. It can automatically generate a serial number for rows in the table and generate a set of values with equal intervals (numbers ). Its main purpose is to generate the table's primary key value, which can be referenced in the insert statement, or you can check the current value through the query, or increase the sequence to the next value.

The create sequence system permission is required to CREATE a SEQUENCE. The sequence creation syntax is as follows:

Create sequence name [increment by n] [start with n] [{MAXVALUE/MINVALUE n | NOMAXVALUE}] [] [{CACHE n | NOCACHE}];

Increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the sequence value is decreased according to this step.

Start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

MAXVALUE defines the maximum value that a sequence generator can generate. The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

MINVALUE defines the minimum value that can be generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

CACHE (buffer) defines the size of the memory block that stores the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

The syntax for deleting a sequence is:

Drop sequence name;

Where:

The person who deletes the SEQUENCE should be the creator of the SEQUENCE or the user who has the permission to drop any sequence system. Once deleted, the sequence cannot be referenced.

Some parts of the sequence can also be modified in use, but the satrt with option cannot be modified. Modifications to the sequence only affect the subsequent sequence numbers. The generated sequence numbers remain unchanged.

The syntax for modifying the sequence is as follows:

Create and delete Sequences

Example 1: Create sequence:

Create sequence abc increment by 1 start with 10 MAXVALUE 9999999 nocycle nocache;

Execution result:

The sequence has been created.

Step 2: delete sequence:

Drop sequence abc;

Execution result:

The sequence has been discarded.

Note: The sequence created above is named ABC, which is an incremental sequence. The increment value is 1 and the initial value is 10. This sequence is not cyclic and memory is not used. No minimum value is defined. The default minimum value is 1 and the maximum value is 9 999 999.

Sequence usage

If a sequence has been created, how can we reference the sequence? The method is to use CURRVAL and NEXTVAL to reference the values of the sequence.

There are many reasons for the gap during the numbering process. If a stored procedure selects a number from a sequence as a local variable but never uses it, the number is lost. It will no longer return to the original sequence, and the result will cause a gap in the numerical sequence. Do not worry about this in the relational database model. But sometimes people care about this and they want to know which numbers are lost.

Call NEXTVAL to generate the next serial number in the sequence. You must specify the sequence name when calling the sequence, that is, use the following method:

Sequence name. NEXTVAL

CURRVAL is used to generate the current value of the sequence. No value of the sequence is generated no matter how many calls are made. If the sequence has not produced the next value of the sequence by calling NEXTVAL, it makes no sense to reference CURRVAL first. The method for calling CURRVAL is the same as above. You must specify the sequence name, that is, use the following method to call it:

Sequence name. CURRVAL.

The value of the generated 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

Current Value of the generation sequence:

Select abc. currval from dual;

Execution result:

CURRVAL -------------------- 11

Note:Call NEXTVAL for the first time to generate the initial value of the sequence. The initial value is 10 according to the definition. The second call generates 11 because the sequence step is 1. Call CURRVAL to display the current value of 11. No new value is generated. Oracle's parsing functions provide a much faster way to check the gap. They allow you to view the values of the next row or the previous row (LAG) while using the complete and set-oriented SQL processing.

The Oracle Database sequence is introduced here. I hope this introduction will bring you some benefits. Thank you!