Deep understanding of Oracle sequence

Deep understanding of Oracle sequence

Oracle Sequence: Sequence creates, uses, modifies, and deletes data. Sequence is an object used to generate continuous integer data. A sequence is often used as a growth column in the primary key. It can be generated in ascending or descending order.

Syntax: Create a sequence
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 from an integer, the default value in ascending order is 1, and the default value in descending order is-1.
Increment by: Number of increases. If it is a positive number, it is generated in ascending order, and if it is a negative number, it is generated in descending order. The default value is 1, and the default value is-1.
MAXVALUE: the maximum value.
NOMAXVALUE: This is the default option for the maximum value. The maximum value in ascending order is 1027, and the default value in descending order is-1.
MINVALUE: minimum value.
NOMINVALUE: This is the default option. The default value is 1 in ascending order and-1026 in descending order.
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 from the maximum value after reaching the minimum value.
NOCYCLE: indicates that the sequence does not start again. If the ascending sequence reaches the maximum value or the descending sequence reaches the minimum value, an error is returned. The default value is NOCYCLE.
CACHE: When the CACHE option is used, a sequence number is pre-generated according to the sequence rules. It is stored in the memory and can respond faster when the next serial number is used. When the serial number in the memory is used up, the system generates a new sequence number and stores it in the cache. This improves the efficiency of generating the serial number. By default, Oracle produces 20 serial numbers.
NOCACHE: serial numbers are not generated in memory in advance.

 

Case 2: Create a sequence starting from 1, with the default maximum value increasing by 1 each time. NOCYCLE is required, and the cache contains 30 pre-allocated serial numbers.

Code Demonstration: generate a serial number

SQL> CREATE SEQUENCE MYSEQ
MINVALUE 1
Start with 1
NOMAXVALUE
Increment by 1
NOCYCLE
CACHE 30
/

Sequence created

After creating a sequence, you can access the current value and the next value of the sequence through the two "pseudo columns" CURRVAL and NEXTVAL of the sequence object.

Sequence:

SQL> SELECT MYSEQ. NEXTVAL FROM DUAL;

NEXTVAL

----------

1

SQL> SELECT MYSEQ. NEXTVAL FROM DUAL;

NEXTVAL

----------

2

SQL> SELECT MYSEQ. CURRVAL FROM DUAL;

CURRVAL

----------

2

You can use alter sequence to modify a SEQUENCE, which has the following restrictions:
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.

1. The initial values of the sequence cannot be modified.
2. The minimum value cannot be greater than the current value.
3. The maximum value cannot be smaller than the current value.

You can use the drop sequence command to delete a SEQUENCE object.

Sequence Modification and Deletion

SQL> ALTER SEQUENCE MYSEQ
Max value 10000
MINVALUE-300
/

SEQUENCE ALTERED

Delete:
SQL> DROP SEQUENCE MYSEQ;

SEQUENCE DROPPED

Oracle Sequence: Sequence creates, uses, modifies, and deletes data. Sequence is an object used to generate continuous integer data. A sequence is often used as a growth column in the primary key. It can be generated in ascending or descending order.

Syntax: Create a sequence
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 from an integer, the default value in ascending order is 1, and the default value in descending order is-1.
Increment by: Number of increases. If it is a positive number, it is generated in ascending order, and if it is a negative number, it is generated in descending order. The default value is 1, and the default value is-1.
MAXVALUE: the maximum value.
NOMAXVALUE: This is the default option for the maximum value. The maximum value in ascending order is 1027, and the default value in descending order is-1.
MINVALUE: minimum value.
NOMINVALUE: This is the default option. The default value is 1 in ascending order and-1026 in descending order.
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 from the maximum value after reaching the minimum value.
NOCYCLE: indicates that the sequence does not start again. If the ascending sequence reaches the maximum value or the descending sequence reaches the minimum value, an error is returned. The default value is NOCYCLE.
CACHE: When the CACHE option is used, a sequence number is pre-generated according to the sequence rules. It is stored in the memory and can respond faster when the next serial number is used. When the serial number in the memory is used up, the system generates a new sequence number and stores it in the cache. This improves the efficiency of generating the serial number. By default, Oracle produces 20 serial numbers.
NOCACHE: serial numbers are not generated in memory in advance.

 

Case 2: Create a sequence starting from 1, with the default maximum value increasing by 1 each time. NOCYCLE is required, and the cache contains 30 pre-allocated serial numbers.

Code Demonstration: generate a serial number

SQL> CREATE SEQUENCE MYSEQ
MINVALUE 1
Start with 1
NOMAXVALUE
Increment by 1
NOCYCLE
CACHE 30
/

Sequence created

After creating a sequence, you can access the current value and the next value of the sequence through the two "pseudo columns" CURRVAL and NEXTVAL of the sequence object.

Sequence:

SQL> SELECT MYSEQ. NEXTVAL FROM DUAL;

NEXTVAL

----------

1

SQL> SELECT MYSEQ. NEXTVAL FROM DUAL;

NEXTVAL

----------

2

SQL> SELECT MYSEQ. CURRVAL FROM DUAL;

CURRVAL

----------

2

You can use alter sequence to modify a SEQUENCE, which has the following restrictions:
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.

1. The initial values of the sequence cannot be modified.
2. The minimum value cannot be greater than the current value.
3. The maximum value cannot be smaller than the current value.

You can use the drop sequence command to delete a SEQUENCE object.

Sequence Modification and Deletion

SQL> ALTER SEQUENCE MYSEQ
Max value 10000
MINVALUE-300
/

SEQUENCE ALTERED

Delete:
SQL> DROP SEQUENCE MYSEQ;

SEQUENCE DROPPED