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.

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 contribute.geeksforgeeks.org or mail your article to contribute@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