Sequences in SQL

Sequences:

  Ø Sequences are a set of database objects, which can generate sequential integer value.

  Ø They are used to automatically generate primary key or unique key values.

 ØA sequence can be created in ascending or descending order.

Syntax:

1. Create sequence <sequence_name>

   Start with            à Initial Value

   Increment by      à Increment Value

   Minvalue            à Minimum Value

   Maxvalue           à Maximum Value

   Cycle | Nocycle

   Cache | Nocache;        

Sequence_name:

Ø It specifies the Name of the sequence.

Initial Value:

   Ø  Initial Value Specifies the first sequence number to be generated.

  Ø It should be greater than or equal to minimum value and less than equal to maximum value.

 Increment Value:

   ØIt specifies the interval between sequence numbers. This value can be any positive or negative but it cannot be 0.

Ø      Ø If this value is negative then the sequence descends. If increment is positive, then the sequence ascends.

Minimum value:

   Ø It specifies the Minimum value of the sequence.

Maximum value:

   Ø It specifies the Maximum value of the sequence.

Cycle:

  Ø It Specifies that the sequence continue to generate values after reaching either its maximum or minimum value.

 Nocycle:

   Ø It Specifies that the sequence cannot generate more values after reaching         its maximum or minimum values. The default is NOCYCLE.

Cache:

   Ø It Specifies how many values of the sequence oracle pre allocates and             keeps in memory for faster access.

Nocache:

   Ø  It specifies that values of the sequence are not pre allocated. If you omit         both the cache parameter and the NOCACHE option, Oracle caches 20           sequence numbers by default.

Example: 1

Sequence:

1. Create sequence Product_Seq

   Start with   1      

   Increment by 1  

   Minvalue  0       

   Maxvalue 1000         

   Cycle;

Table: Products

1. Create table Products

   ( Item_no Number,

   Item_name varchar2(50));

Insertion:

1. 1.    Insert into Products (Item_no, Item_name)

       Values (Product_Seq.Nextval,’Book’);

1. 2.    Insert into Products (Item_no, Item_name)

       Values (Product_Seq.Nextval,’Pencil’);

1. 3.    Insert into Products (Item_no, Item_name)

       Values (Product_Seq.Nextval,’Bag’);

Output:

ITEM_NO	ITEM_NAME
1	Book
2	Pencil
3	Bag

Altering Sequence:

The following specifications of a sequence can be altered.

·       Minimum value

·       maximum value

·       Incremental value

·       Number of cached sequence number.

 Example: 2

1.  Create sequence Product_Seq

    Start with   10  

             Increment by 10

             Minvalue  5       

             Maxvalue 100 

             Cycle;

Alter Sequence:

1. Alter sequence Product_Seq maxvalue 1000;

 Dropping Sequence:

Syntax:

1.           Drop sequence <seq_name>;

Example:  

1. Drop sequence  Product_seq;

Display the list of Sequences:

1.           Select * from USER_SEQUENCES;

 

 

 

 

 

 

 

 

 

 

Share: