What is Sequence?
A sequence is a database object that automatically generates the incremented list of numeric values according to rule as specified in sequence specification.
For example to insert employee number automatically in column (EMPLOYEE_NO) of Table, when a new record is inserted in the table, then we use sequence.
Sequence values are generated in Ascending or Descending order.
Sequences are not associated with tables; they are used by the application. There are two values in sequence –
- CURRVAL – Provide Current value of Sequence.
- NEXTVAL – Provide Next value of sequence.
CREATE SEQUENCE <sequence_name> [<sequence_parameter_list>] [RESET BY <subquery>]
|<sequence_name>||It is the name of the sequence.|
|[<sequence_parameter_list>]||It specifies one or more sequence parameters.|
|START WITH <start_value>||It describes the starting sequence value.|
|INCREMENT BY <increment_value>||This specifies the value to be incremented from the last value assigned for each time when new sequence value generated. The default is 1.|
|MAXVALUE <max_value>||This specifies maximum value ,which can be generated by the sequence. <max_value> can be between -4611686018427387903 and 4611686018427387902.|
|NO MAXVALUE||When the NO MAXVALUE is specified, for an ascending sequence, the maximum value will be 4611686018427387903 and the minimum value for a descending sequence will be -1.|
|MINVALUE <min_value> / NO MINVALUE||It specifies the minimum value that a sequence can generate. <min_value> can be between -4611686018427387904 and 4611686018427387902. When the NO MINVALUE is used, the minimum value for an ascending sequence is 1|
|CYCLE||CYCLE directive specifies that sequence number will be restarted after it reaches its maximum or minimum value.|
|NO CYCLE||Default option.NO CYCLE directive specifies that sequence number will not be restarted after it reaches its maximum or minimum value.|
|CACHE <cache_size> /||The cache size specifies which range of sequence numbers will be cached in a node. <cache_size> must be unsigned integer.|
|NO CACHE||Default option. NO CACHE directive specifies that the sequence number will not be cached in a node.|
|RESET BY <subquery>||It specifies that during the restart of the database, the database automatically executes the <subquery> and the sequence value is restarted with the returned value.|
We will create a sequence with named DHK_SCHEMA.EMP_NO, which will create incremented value of the sequence by +1 each time, when the sequence is used.
Sequence Script –
CREATE SEQUENCE DHK_SCHEMA.EMP_NO START WITH 100 INCREMENT BY 1.
Here we will use object “sequence” in below example to increment the value of employee no by +1 each time the select query is executed. In the query, the “nextval” can be used for serial number generation or same type of requirement.
Use of Sequence –
SELECT DHK_SCHEMA.EMP_NO.nextval FROM DUMMY;
OUTPUT – 100,101,102………So on every execution of above select query.