Shulou(Shulou.com)05/31 Report--

This article will explain in detail about the Oracle sequence sequence, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Oracle sequences: sequences (Sequence) create, use, modify, delete. Sequences (Sequence) are objects used to generate continuous integer data. Sequences are often used as growing columns in primary keys, and those in the sequence can be generated in ascending or descending order.

Syntax: creating sequenc

CREATE SEQUENCE sequence_name

[START WITH num]

[INCREMENT BY increment]

[MAXVALUE num | NOMAXVALUE]

[MINVALUE num | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE num | NOCACHE]

Syntax parsing:

START WITH: starting with an integer, the default value for ascending order is 1 and the default value for descending order is-1.

INCREMENT BY: growth. If it is positive, it is generated in ascending order, and if it is negative, it is generated in descending order. The ascending default value is 1 and the descending default value is-1.

MAXVALUE: the maximum value.

NOMAXVALUE: this is the default option for the maximum value, the maximum value for ascending order is: 1027, and the default value for descending order is-1.

MINVALUE: the minimum value.

NOMINVALUE: this is the default option, the ascending default is 1, and the descending default is-1026.

CYCLE: indicates that if the ascending order reaches the maximum value, it starts from the minimum value; if it is a descending sequence, it starts again from the maximum value after reaching the minimum value.

NOCYCLE: an error is reported when the ascending order of the sequence reaches the maximum value and the descending order reaches the minimum value without starting again. Default NOCYCLE.

CACHE: when using the CACHE option, the sequence is pregenerated into a set of sequence numbers according to the sequence rules. Keep it in memory so that you can respond more quickly when using the next serial number. When the serial numbers in memory are used up, the system generates a new set of serial numbers and saves them in the cache, which can improve the efficiency of generating serial numbers. Oracle produces 20 serial numbers by default.

NOCACHE: serial numbers are not pre-generated in memory.

Case 2: create a sequence that starts at 1, the default maximum value, and increases by 1 at a time, which requires NOCYCLE, with 30 pre-assigned sequence numbers in the cache.

Code demonstration: generate serial number

SQL > CREATE SEQUENCE MYSEQ

MINVALUE 1

START WITH 1

NOMAXVALUE

INCREMENT BY 1

NOCYCLE

CACHE 30

/

Sequence created

After the sequence is created, the current value and the next value of the sequence can be accessed through the CURRVAL and NEXTVAL pseudo columns of the sequence object, respectively.

Use sequence:

SQL > SELECT MYSEQ.NEXTVAL FROM DUAL

NEXTVAL

-

one

SQL > SELECT MYSEQ.NEXTVAL FROM DUAL

NEXTVAL

-

two

SQL > SELECT MYSEQ.CURRVAL FROM DUAL

CURRVAL

-

two

Using ALTER SEQUENCE, you can modify a sequence with the following restrictions:

You can either use the owner of the sequence or have ALTER ANY SEQUENCE permission to change the sequence. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

1. The initial value of the sequence cannot be modified.

two。 The minimum value cannot be greater than the current value.

3. The maximum value cannot be less than the current value.

Use the DROP SEQUENCE command to delete a sequence object.

Sequence modification and deletion

SQL > ALTER SEQUENCE MYSEQ

MAXVALUE 10000

MINVALUE-300

/

SEQUENCE ALTERED

Delete:

SQL > DROP SEQUENCE MYSEQ

SEQUENCE DROPPED

Oracle sequences: sequences (Sequence) create, use, modify, delete. Sequences (Sequence) are objects used to generate continuous integer data. Sequences are often used as growing columns in primary keys, and those in the sequence can be generated in ascending or descending order.

Syntax: creating sequenc

CREATE SEQUENCE sequence_name

[START WITH num]

[INCREMENT BY increment]

[MAXVALUE num | NOMAXVALUE]

[MINVALUE num | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE num | NOCACHE]

Syntax parsing:

START WITH: starting with an integer, the default value for ascending order is 1 and the default value for descending order is-1.

INCREMENT BY: growth. If it is positive, it is generated in ascending order, and if it is negative, it is generated in descending order. The ascending default value is 1 and the descending default value is-1.

MAXVALUE: the maximum value.

NOMAXVALUE: this is the default option for the maximum value, the maximum value for ascending order is: 1027, and the default value for descending order is-1.

MINVALUE: the minimum value.

NOMINVALUE: this is the default option, the ascending default is 1, and the descending default is-1026.

CYCLE: indicates that if the ascending order reaches the maximum value, it starts from the minimum value; if it is a descending sequence, it starts again from the maximum value after reaching the minimum value.

NOCYCLE: an error is reported when the ascending order of the sequence reaches the maximum value and the descending order reaches the minimum value without starting again. Default NOCYCLE.

CACHE: when using the CACHE option, the sequence is pregenerated into a set of sequence numbers according to the sequence rules. Keep it in memory so that you can respond more quickly when using the next serial number. When the serial numbers in memory are used up, the system generates a new set of serial numbers and saves them in the cache, which can improve the efficiency of generating serial numbers. Oracle produces 20 serial numbers by default.

NOCACHE: serial numbers are not pre-generated in memory.

Case 2: create a sequence that starts at 1, the default maximum value, and increases by 1 at a time, which requires NOCYCLE, with 30 pre-assigned sequence numbers in the cache.

Code demonstration: generate serial number

SQL > CREATE SEQUENCE MYSEQ

MINVALUE 1

START WITH 1

NOMAXVALUE

INCREMENT BY 1

NOCYCLE

CACHE 30

/

Sequence created

After the sequence is created, the current value and the next value of the sequence can be accessed through the CURRVAL and NEXTVAL pseudo columns of the sequence object, respectively.

Use sequence:

SQL > SELECT MYSEQ.NEXTVAL FROM DUAL

NEXTVAL

-

one

SQL > SELECT MYSEQ.NEXTVAL FROM DUAL

NEXTVAL

-

two

SQL > SELECT MYSEQ.CURRVAL FROM DUAL

CURRVAL

-

two

Using ALTER SEQUENCE, you can modify a sequence with the following restrictions:

You can either use the owner of the sequence or have ALTER ANY SEQUENCE permission to change the sequence. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

1. The initial value of the sequence cannot be modified.

two。 The minimum value cannot be greater than the current value.

3. The maximum value cannot be less than the current value.

Use the DROP SEQUENCE command to delete a sequence object.

Sequence modification and deletion

SQL > ALTER SEQUENCE MYSEQ

MAXVALUE 10000

MINVALUE-300

/

SEQUENCE ALTERED

Delete:

SQL > DROP SEQUENCE MYSEQ

SEQUENCE DROPPED

On the Oracle sequence sequence to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.