How to add sequence in oracle

In Oracle, you can use the CREATE SEQUENCE statement to add a sequence. The syntax is "CREATE SEQUENCE [user.]sequence_name [interval between sequence numbers] [first sequence number] [maximum value that can be generated] [minimum value of sequence]".

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

How to add a sequence in oracle

CREATE SEQUENCE [user.]sequence_name 序列号之间的间隔 第一个序列号 可生成的最大值 序列的最小值

A sequence is a database object that can be used to generate unique integers. Sequences are generally used to automatically generate primary code values. The values ​​of a sequence are automatically generated by a special Oracle program, so the sequence avoids the performance bottleneck caused by implementing the sequence at the application layer.

Oracle sequence allows multiple sequence numbers to be generated at the same time, and each sequence number is unique. When a sequence number is generated, the sequence is incremented independently of the transaction's commit or rollback. Allows designing default sequences without specifying any clauses. This sequence is an ascending sequence, starting from 1, incrementing by 1, and has no upper limit.

1) Create sequence command

    CREATE SEQUENCE [user.]sequence_name
    [increment by n]
    [start with n]
    [maxvalue n | nomaxvalue]
    [minvalue n | nominvalue];

INCREMENT BY: Specify the interval between sequence numbers. The value can be a positive or negative integer, but cannot be 0 . The sequence is in ascending order. When this clause is omitted, the default value is 1.

START WITH: Specify the first serial number generated. In ascending order, the sequence can start with a value greater than the minimum value, and the default value is the minimum value of the sequence. For descending order, the sequence can start with a value smaller than the maximum value, and the default value is the maximum value of the sequence.

MAXVALUE: Specifies the maximum value that can be generated by the sequence.

NOMAXVALUE: Specify the maximum value as 1027 for ascending order and -1 for descending order.

MINVALUE: Specifies the minimum value of the sequence.

NOMINVALUE: Specify the minimum value as 1 for ascending order. Specify a minimum value of -1026 for descending order.

2) Change sequence command

    ALTERSEQUENCE [user.]sequence_name
    [INCREMENT BY n]
    [MAXVALUE n| NOMAXVALUE ]
    [MINVALUE n | NOMINVALUE]；

To modify the sequence:

1) Modify the increment of future sequence values.

2) Set or cancel the minimum or maximum value.

3) Change the number of buffer sequences.

4) Specify whether the sequence number is in order.

5) Delete sequence command

Extended knowledge:

1. Add incremental sequence numbers to the same group

Syntax format:

row_number() over(partition by 分组列 order by 排序列 desc)

2. Add the same serial number to the same group

select id, dzid, dense_rank() over(order by dzid) 序号结果 from z_test;

Recommended tutorial: "Oracle Video Tutorial"

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