SQL | SEQUENCES

Sequence is a set of integers 1, 2, 3, … that are generated and supported by some database systems to produce unique values on demand.

• A sequence is a user defined schema bound object that generates a sequence of numeric values.
• Sequences are frequently used in many databases because many applications require each row in a table to contain a unique value and sequences provides an easy way to generate them.
• The sequence of numeric values is generated in an ascending or descending order at defined intervals and can be configured to restart when exceeds max_value.

Different features of sequences:

1. Sequence is database object that generate produce integer values in sequential order.

2. It automatically generates primary key and unique key values

3. It may be ascending or descending order.

4. It can be used for multiple tables.

5. Sequence numbers are stored and generated independently of tables

6. It saves a time by reducing application code.

7. It is used to generate unique integers.

8. It is used to create an auto number field.

9. Useful when you need to create a unique number to act as a primary key

10. Oracle provides an object called as a Sequence that can generate numeric values. The value generated can have maximum of 38 digits

11. Provide intervals between numbers.

Syntax:

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;

sequence_name: Name of the sequence.

initial_value: starting value from where the sequence starts. 
Initial_value should be greater than or equal 
to minimum value and less than equal to maximum value.

increment_value: Value by which sequence will increment itself. 
Increment_value can be positive or negative.

minimum_value: Minimum value of the sequence.
maximum_value: Maximum value of the sequence.

cycle: When sequence reaches its set_limit 
it starts from beginning.

nocycle: An exception will be thrown 
if sequence exceeds its max_value.

Example

Following is the sequence query creating sequence in ascending order.

• Example 1:

CREATE SEQUENCE sequence_1
start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;

• Above query will create a sequence named sequence_1.Sequence will start from 1 and will be incremented by 1 having maximum value 100. Sequence will repeat itself from start value after exceeding 100.
• Example 2: Following is the sequence query creating sequence in descending order.

CREATE SEQUENCE sequence_2
start with 100
increment by -1
minvalue 1
maxvalue 100
cycle;

• Above query will create a sequence named sequence_2.Sequence will start from 100 and should be less than or equal to maximum value and will be incremented by -1 having minimum value 1.
• Example to use sequence : create a table named students with columns as id and name.

CREATE TABLE students
( 
ID number(10),
NAME char(20)
);

• Now insert values into table

INSERT into students VALUES(sequence_1.nextval,'Ramesh');
INSERT into students VALUES(sequence_1.nextval,'Suresh');

• where sequence_1.nextval will insert id’s in id column in a sequence as defined in sequence_1. 
• Output:

 ______________________
| ID  |      NAME      |
------------------------
|  1  |     Ramesh     |
|  2  |     Suresh     |            
 ----------------------

This article is contributed by ARSHPREET SINGH. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save