What is oracle sequence

In Oracle, a sequence is a database object used to generate a series of unique numbers; a sequence is a sequence number generator that can automatically generate sequence numbers for rows in a table. Its main purpose is to generate primary keys. value, and the user defining the sequence must have CREATE SEQUENCE permission.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is Oracle sequence

Sequence: Sequence is a database object provided by Oracle for generating a series of unique numbers. Since there is no method to set up an auto-increment column in Oracle, we mainly use sequences to implement the primary key auto-increment function in the Oracle database.

In Oracle database, sequence is actually a sequence number generator, which can automatically generate sequence numbers for rows in the table. Its main purpose is to generate the primary key value of the table. Equivalent to the auto-increment field in SQL Server and MySQL.

In SQL Server, you can use the identity keyword when defining the primary key. In MySQL, you can use the increment keyword when defining the primary key. If you want to use auto-increment fields in Oracle database, you must first define a sequence object, and then use [sequence object name.nextval] to insert auto-increment data in the primary key when inserting data.

To define a sequence, the user defining the sequence must have CREATE SEQUENCE permission. In the Oracle database, the syntax for defining a sequence is as follows:

CREATE SEQUENCE 序列名 
[START WITH n] 
[INCREMENT BY n]
[MAXVALUE n|NOMAXVALUE]
[MINVALUE n|NOMINVALUE]
[CACHE n|NOCACHE]
[CYCLE|NOCYCLE]

Parameter description:

(1) INCREMENT BY: Define the step size of the sequence. If n is a positive value, it means that the sequence is an increment Sequence; if n is a negative value, it means that the sequence is a decreasing sequence; if omitted, the default value is 1.

(2) START WITH: Defines the starting value of the sequence. If omitted, the default value is 1.

(3) MAXVALUE: Defines the maximum value that the sequence generator can produce. The option NOMAXVALUE is the default option, which means that there is no maximum value definition. At this time, for the increasing sequence, the maximum value that the system can generate is 10 to the 27th power; for the decreasing sequence, the maximum value is -1.

(4) MINVALUE: Define the minimum value that the sequence generator can produce. The option NOMAXVALUE is the default option, which means that there is no minimum value definition. At this time, for the descending sequence, the minimum value that the system can generate is -10 to the 26th power; for the increasing sequence, the minimum value is 1.

(5) CYCLE|NOCYCLE: Indicates whether to loop when the value of the sequence generator reaches the limit value. CYCLE stands for cycle and NOCYCLE stands for no cycle.

(6) CACHE: Define the size of the memory block to store the sequence, the default is 20. NOCACHE means no memory buffering of the sequence.

Examples are as follows:

Expand knowledge:

Modify the sequence

Format:

ALTER SEQUENCE name [INCREMENT BY n]
    [MINVALUE n | NO MINVALUE] 
    [MAXVALUE n | NO MAXVALUE ]
    [MINVALUE n | NO MINVALUE ]
    [CACHE n ] 
    [CYCLE | NO CYCLE]

Examples are as follows:

The above is the detailed content of What is oracle sequence. For more information, please follow other related articles on the PHP Chinese website!