SEQUENCE
A sequence is a database object used to generate sequence numbers for rows in the tables. It can be used for producing unique primary key values. A sequence is created using the CREATE SEQUENCE command.
A sequence is a user created database object that can be shared by multiple users to generate unique integers.
A typical usage for sequence is to create a primary key value ,which must be unique for each row. Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
Is typically used to create primary key value.
Replaces application code
SYNTAX: -
CREATE SEQUENCE sequence_name [INCREMENT] BY integer value START WITH integer value MAXVALUE integervalue
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.
MIN VALUE:
Specifies the sequence minimum value.
MAX VALUE:
Specifies the maximum 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).
EXAMPLE: -
SQL> CREATE SEQUENCE empcode INCREMENT BY 2 START WITH 100;
Sequence created.
REFERENCING A SEQUENCE
Once a sequence is created SQL can be used to view the values held in the cache. To simply view sequence value use a select sentence as described below:
EXAMPLE: -
SQL> SELECT empcode.NEXTVALfrom dual;
NEXTVAL
---------
100
This will display the next value held in the cache on the VDU screen. Every time NEXTVAL references a sequence its output is automatically incremented from the old value to the new value ready for use.
A sequence is a database object used to generate sequence numbers for rows in the tables. It can be used for producing unique primary key values. A sequence is created using the CREATE SEQUENCE command.
A sequence is a user created database object that can be shared by multiple users to generate unique integers.
A typical usage for sequence is to create a primary key value ,which must be unique for each row. Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
Is typically used to create primary key value.
Replaces application code
SYNTAX: -
CREATE SEQUENCE sequence_name [INCREMENT] BY integer value START WITH integer value MAXVALUE integervalue
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.
MIN VALUE:
Specifies the sequence minimum value.
MAX VALUE:
Specifies the maximum 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).
EXAMPLE: -
SQL> CREATE SEQUENCE empcode INCREMENT BY 2 START WITH 100;
Sequence created.
REFERENCING A SEQUENCE
Once a sequence is created SQL can be used to view the values held in the cache. To simply view sequence value use a select sentence as described below:
EXAMPLE: -
SQL> SELECT empcode.NEXTVALfrom dual;
NEXTVAL
---------
100
This will display the next value held in the cache on the VDU screen. Every time NEXTVAL references a sequence its output is automatically incremented from the old value to the new value ready for use.