The CREATE SEQUENCE statement is used in SQL to create a new sequence object that can generate unique numbers. A sequence is often used when you need a unique identifier for rows in a table. Here's the general syntax for creating a sequence:
CREATE SEQUENCE sequence_name
[INCREMENT BY increment_value]
[START WITH start_value]
[MINVALUE min_value]
[MAXVALUE max_value]
[CYCLE | NO CYCLE]
[CACHE cache_size | NOCACHE];
Parameters:
- sequence_name: The name of the sequence you want to create.
- INCREMENT BY increment_value: The value that will be added to the current sequence value each time a new value is generated. The default is 1.
- START WITH start_value: The value from which the sequence will start. The default is 1.
- MINVALUE min_value: The minimum value that the sequence can generate. The default is the same as the START WITH value.
- MAXVALUE max_value: The maximum value that the sequence can generate. The default is 2,147,483,647 for a standard integer sequence.
- CYCLE: Indicates that the sequence should restart at the MINVALUE after reaching the MAXVALUE. NO CYCLE (the default) means that the sequence will stop generating values once the MAXVALUE is reached.
- CACHE cache_size: Defines how many sequence numbers are preallocated and stored in memory for faster access. The default is 20.
- NOCACHE: Specifies that no sequence numbers should be preallocated.
Example:
Here is an example of how to create a sequence named order_sequence that starts at 1000, increments by 5, has a maximum value of 10000, and cycles when it reaches the maximum:
CREATE SEQUENCE order_sequence
INCREMENT BY 5
START WITH 1000
MINVALUE 1000
MAXVALUE 10000
CYCLE
CACHE 10;
Using the Sequence:
Once the sequence is created, you can use it to generate unique numbers. Here's how to obtain the next value from the sequence:
SELECT NEXTVAL('order_sequence');
You can also use CURRVAL to get the current value of the sequence without advancing it:
SELECT CURRVAL('order_sequence');
Remember that sequences are commonly used for unique identifiers or primary keys in database tables but can be applied in various situations where unique values are necessary.