Sequences in oracle
A sequence is an auto generator increment or decrement number.
Syntax for creating Sequence
Create sequence sequence_name
[ starts with n
Increment by n
Min value n / No minvalue
Max value n / No maxvalue
Cycle / No cycle
Cache n / No cache
Order / No order ];
A default sequence i.e. created without any options, always start with 1, is in ascending order and values are incremented by 1.
Now Let understand OPTIONAL part written in square bracket in syntax.
INCREMENT BY Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
START WITH Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.
MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than STARTWITH and must be greater than MINVALUE.
NOMAXVALUE Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.
CYCLE Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers.
NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.
ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys. ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
Nextval and Currval
Oracle provides two pseudo columns – Nextval and Currval to access the value generated by the sequence.
Syntax
sequence_name.currval :- Returns the current value of sequence.
sequence_name.nextval :- Returns the next value of sequence.
Destroying a Sequence
Syntax
Drop sequence sequence_name;
Example
Selecting client table
Creating sequence
Selecting currval using dual table
Selecting nextval using dual table
Creating another sequence
Selecting client table
Insert row using sequence
Selecting client table
Insert another row using sequence
Selecting client table
Update row using sequence
Selecting client table
Dropping sequence
Selecting sequence after dropping
I hope this article will help you.