what do you mean by sequence in structured query language [SQL]?

Problem statement: what do you mean by sequence in SQL, can you write a script for creating the sequence & using it into the database?

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

1. A sequence is a user defined schema bound object that generates a sequence of numeric values.
2. Sequences are frequently used in many databases because many applications requires each row in a table to contain unique value & sequences provides an easy way to generate them.
3. The sequence of numeric values is generated in an ascending or descending order at defined intervals & 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 ;

Description:

• sequence_name: Name of the sequence
• initial_value: starting value from where 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 cycle reaches its set_limit it starts from beginning.
• NOCYCLE: An exception will be thrown if sequence exceeds its maximum_value

[a] Example: creating sequence in ascending order

      CREATE SEQUENCE sequence1

      start with 1

      increment by 1

      minvalue 0

      maxvalue 1000

      cycle ;

Illustrations: Above script will create a sequence by name sequence1. Sequence will starts from 1 & will be incremented by 1 having maximum value 100. Sequence will repeat itself from start value after exceeding 1000.

[b] Example: creating sequence in descending order

      CREATE SEQUENCE sequence2

      start with 100

      increment by -1

      minvalue 1

      maxvalue 100

      cycle ;

Illustration: Above query will create a sequence named sequence2. Sequence will starts from 100 & should be less than or equal to maximum value & will be incremented by -1 having minimum value 1

[c] Example to use sequence: create a table named employee with columns as id & name.

      CREATE TABLE employee

      (

         ID number(10),

         NAME varchar(20)

      );

     Let's insert values into the table:

     INSERT into employee VALUES(sequence1.nextval, 'Parth');

     INSERT into employee VALUES(sequence1.nextval, 'Madhu');

     INSERT into employee VALUES(sequence1.nextval, 'Rajnish');

     INSERT into employee VALUES(sequence1.nextval, 'Karthik');

      

Here sequence1.nextval will insert id's in id column in a sequence as defined in sequence1

Output:

           ID  |   NAME

           --- ---------------

           1    |   Parth

           2    |   Madhu

           3    |   Rajnish

           4    |   Karthik