Sequences

"A Sequence is simply an automatic counter, which generates sequential numbers whenever required."
Oracle provides an object called a Sequence that can generate numeric values. The value generated can have a maximum of 38 digits.
 A sequence can be defined to: 

• Generate numbers in ascending or descending order.
• Provide intervals between numbers.
• Caching of sequence numbers in memory to speed up their availability .

A sequence is an independent object and can be used with any table that requires its output.
 

Creating a Sequences :

The minimum information required for generating numbers using a sequence is:

• The starting number.
• The maximum number that can be generated by a sequence.
• The increment value for generating the next number.

Syntax:

   CREATE SEQUENCE <Sequence Name>
   [INCREMENT BY <IntegerValue> 
   START WITH <Integer Value>
   MAXVALUE <Integer Value>
   MINVALUE <Integer Value>  CYCLE CACHE ]

 INCREMENT BY:
 Specifies the interval between sequence numbers. It can be any positive or negative value but not zero. If this clause is omitted, the default value is 1. 
 MAXVALUE And MINVALUE : 
 Specifies the maximum or minmum value that a sequence can generate.
 START WITH:
Specifies the first sequence number to be generated. The default for an ascending sequence is the sequence minimum value (1) and for a descending sequence, it is the maximum value (-1). 
 CYCLE:
Specifies that the sequence continues to generate repeat values after reaching either its maximum value.
 CACHE: 
Specifies how many values to generate in advance and to keep in memory for faster access.Minimum value is two for this option.

Oracle provides two pseudo columns  NEXTVAL and CURVAL to access the values generated by the Sequence.

Altering a  Sequence  :

 A sequence once created can be altered.   This is achieved by using the ALTER SEQUENCE statement.
 The START value of the sequence cannot be altered .

Syntax:

   ALTER SEQUENCE <SequenceName> [INCREMENT BY <IntegerValue> MINVALUE <IntegerValue> ] 

Destroying  a Sequence:

Syntax:

DROP SEQUENCE sequencename;

Example:

---

• Prev
• Next

---

Questions

---

---