Oracle Learning article 10: Sequences

Sequence: is a database object that is used to generate a unique, contiguous integer. A sequence is typically used to automatically generate a value for a primary key or unique key.

1. Create the sequence syntax as follows:
Create SEQUENCE sequence_name
[START with integer]
[INCREMENT by integer]
[MAXVALUE integer| Nomaxvalue]
[MINVALUE integer| Nominvalue]
[cycle| Nocycle]
[CACHE interger| NOCACHE];

Where: START with: Specifies the first sequence number to be generated. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence.
INCREMENT by: Is the interval between the specified sequence numbers. Its default value is 1. If integer is a positive value, the resulting sequence is sorted in ascending order, otherwise descending.
MAXVALUE: Specifies the maximum value that a sequence can generate.
Nomaxvalue: This is the default option, which sets the maximum value of the ascending sequence to 27 powers of 10, and sets the maximum value of the descending sequence to-1.
MINVALUE: Specifies the minimum value for the sequence. The minvalue must be less than or equal to the value of start with and must be less than MaxValue.
Nominvalue: This is the default option to set the minimum value of the ascending sequence to 1, and the lowest value of the descending sequence to 26 powers of 10.
CYCLE: Specifies that the sequence will continue to generate values from scratch when the maximum or minimum value is reached.
Nocycle: This is the default option. Specifies that the sequence will no longer be able to generate a value after it reaches the maximum or minimum value.
Cache: Use the cache option to pre-assign a set of serial numbers and keep them in memory, which allows for faster access to the serial number.
NOCACHE: This key does not pre-allocate serial numbers for faster speeds. If you omit the cache and NoCache options when you create a sequence, Oracle caches 20 serial numbers by default.

Example 3:create SEQUENCE toys_seq
START with ten
INCREMENT by 2
MAXVALUE +
MINVALUE ten
nocycle< br> CACHE;

2. Access sequence: The value of the sequence can be accessed through currval and nextval pseudo-columns.
Example 4: Demonstrates selecting a value from the sequence toys_seq insert the Toyid column in the Toys table. Successful execution will insert the value "P10" and "P12" in the Toyid column of the table.
INSERT into Toys (toyid,toyname,toyprice) VALUES (' P ' | | Toys_seq. Nextval, ' twenty ', 25);
INSERT into Toys (toyid,toyname,toyprice) VALUES (' P ' | | Toys_seq. Nextval, ' MAGIC PENCIL ', 75);

Example 5: Show how to view the current value of a sequence
Select Toys_seq. Currval from dual;

3. Change sequence: The alter sequence command is used to set or remove MinValue or MaxValue, modify delta values, and modify the number of serial numbers in the cache.
Modify the sequence syntax as follows: Note that the start with parameter of the sequence cannot be modified. When modifying a sequence, be aware that the minimum value of the ascending sequence should be less than the maximum value.
ALTER SEQUENCE [schema.] Sequence_name
[INCREMENT by integer]
[MAXVALUE integer| Nomaxvalue]
[MINVALUE integer| Nominvalue]
[cycle| Nocycle]
[CACHE interger| NOCACHE];

Example 6: Demonstrates how to set up a new MaxValue, and turns on cycle for the toys_seq sequence.
ALTER SEQUENCE Toys_seq
MAXVALUE 5000
CYCLE;

You can query the dictionary view user_sequences to see the details of the sequence that the user created

4. Delete sequence syntax: Drop SEQUENCE toys_seq;

Oracle Learning article 10: Sequences