A sequence is a database object that generates an automatically incremented list of numeric values according to the rules defined in the sequence specification.
v The specification of numeric values is generated in an ascending or descending order at defined increment interval, and the numbers generated by a sequence can be used by applications, for example, to identify the rows and columns of a table.
v 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.
Sequences are not associated with table; they are used by applications, which can use CURRVAL in a SQL statement to get the current value generated by a sequence and NEXTVAL to generate the next value in the defined sequence.
There are two values in sequence –
CURRVAL – Provide Current value of Sequence.
NEXTVAL – Provide Next value of sequence.
CREATE SEQUENCE <sequence_name> [<common_sequence_parameter_list>] [RESET BY <subquery>]
Syntax Elements
<sequence_name> ::= [<schema_name>.]<identifier><schema_name> ::= <identifier><common_sequence_parameter_list> ::= <common_sequence_parameter>, ...<common_sequence_parameter> ::= <sequence_parameter_start_with>| <basic_sequence_parameter><basic_sequence_parameter> ::= INCREMENT BY n| MAXVALUE n| NO MAXVALUE| MINVALUE n| NO MINVALUE| CYCLE| NO CYCLE<sequence_parameter_start_with> ::= START WITH n
INCREMENT BY
Defines the amount the next sequence value is incremented from the last value assigned. The default is 1. Specify a negative value to generate a descending sequence. An error is returned if the INCREMENT BY value is 0
START WITH
Defines the starting sequence value. If you do not specify a value for the START WITH clause, MINVALUE is used for ascending sequences and MAXVALUE is used for descending sequences.
MAXVALUE
Defines the largest value generated by the sequence and must be between 0 and 4611686018427387903.
NO MAXVALUE
When MAXVALUE is not specified, the maximum value for an ascending sequence is 4611686018427387903 and the maximum value for a descending sequence is -1.
MINVALUE
The minimum value of a sequence can be specified after MINVALUE and is between 0 and 4611686018427387903.
NO MINVALUE
When MINVALUE is not specified, the minimum value for an ascending sequence is 1 and the minimum value for a descending is -4611686018427387903.
CYCLE
The sequence number will be reused after it reaches its maximum or minimum value.
NO CYCLE
Default option. The sequence number will not be reused after it reaches its maximum or minimum value.
RESET BY
During the restart of the database, the database automatically executes the RESET BY statement and the sequence value is restarted with the specified value from the statement after RESET BY.
If RESET BY is not specified, the sequence value is stored persistently in the database. During the restart of the database, the next value of the sequence is generated from the saved sequence value.
You can use database sequences to perform the following operations:
Generate unique, primary key values, for example, to identify the rows and columns of a table
Coordinate keys across multiple rows or tables
Example for creating a sequence:
Sequence seq is created, then CURRVAL and NEXTVAL are used to get the values from the sequence:
create sequence "KABIL_PRACTICE"."SEQ" start with 1;
NEXTVAL returns 1:
SELECT "KABIL_PRACTICE"."SEQ".NEXTVAL FROM DUMMY;
Note: Whenever I execute the above query it returns NEXT value. Such as when I execute it again it through the output like,
To Drop a SEQUENCE:
drop sequence "KABIL_PRACTICE"."SEQ";
Example For INCREMENT BY:
This specifies the value to be incremented from the last value assigned for each time when new sequence value generated. The default is 1.
CODE:
create sequence "KABIL_PRACTICE"."SEQ_INC" start with 1 increment by 2;
Then Execute this code:
SELECT "KABIL_PRACTICE"."SEQ_INC".NEXTVAL FROM DUMMY;
Result:
Again execute the above code:
Here, you may note the value changed from “1” to “3” i.e. Incremented by 2.
Example for MAXVAL:
CODE:
create sequence "KABIL_PRACTICE"."SEQ_MAXVAL" start with 1 increment by 2 MAXVALUE 5;
Then Execute this code:
SELECT "KABIL_PRACTICE"."SEQ_MAXVAL".NEXTVAL FROM DUMMY;
Result:
Again execute the above code till it reaches the maximum value.
Now it reached it maximum value. If you execute the above code it through an error like,
i.e. Sequence is exhausted.
NOTE: NO MAXVALUE
When MAXVALUE is not specified, the maximum value for an ascending sequence is 4611686018427387903 and the maximum value for a descending sequence is -1.
Example for MINVALUE:
CODE:
create sequence "KABIL_PRACTICE"."SEQ_MINVAL" start with 1 increment by -3 MAXVALUE 5 MINVALUE -5;
NOTE: Here I incremented it by “-3”. Default value is “1” and I mentioned that the MAXIMUM Value is “5” and MINVALUE “-5”. Whenever we use MINVALUE in SEQUENCE we have to mention the MAXVALUE. Otherwise the sequence wont be create.
Then Execute this code until it reaches it MINVALUE:
SELECT "KABIL_PRACTICE"."SEQ_MAXVAL".MINVAL FROM DUMMY;
Now it reaches it “MINIMUMVALUE”. if you execute the above code it through’s the error like,
Note:
NO MINVALUE:
Example for CYCLE:
create sequence "KABIL_PRACTICE"."SEQ_CYCLE" start with 1 increment by 1 MAXVALUE 3 cycle;
Then Execute this code until it reaches it MAXVALUE:
SELECT "KABIL_PRACTICE"."SEQ_CYCLE".NEXTVAL FROM DUMMY;
Now it reaches its end value and again it starts from "1" i.e., Cycle.
NOTE:
NO CYCLE:
v Default option.
v When the NO CYCLE directive is used the sequence number will not be restarted after it reaches its maximum or minimum value.
v 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.
Example for Inserting a created sequence in an Existing table:
CODE:
1. Create a table.
create column table "KABIL_PRACTICE"."EMP_NAME_SEQ"("EMP_NAME" VARCHAR(10));2. Insert a records into the table.
INSERT INTO "KABIL_PRACTICE"."EMP_NAME_SEQ" VALUES('KABIL');
INSERT INTO "KABIL_PRACTICE"."EMP_NAME_SEQ" VALUES('DHANUSH');
INSERT INTO "KABIL_PRACTICE"."EMP_NAME_SEQ" VALUES('ANUPRIYA');
INSERT INTO "KABIL_PRACTICE"."EMP_NAME_SEQ" VALUES('PRIYANKA');
INSERT INTO "KABIL_PRACTICE"."EMP_NAME_SEQ" VALUES('ARAVIND');
1. Create a sequence.
Code:
create sequence "KABIL_PRACTICE"."INSERT_SEQ" start with 101;
2. Alter the table by adding One additional column named as “EMP_ID”.
Result:
3. Insert a created sequence into the table:
Code:
update "KABIL_PRACTICE"."EMP_NAME_SEQ" set "EMP_ID" = "KABIL_PRACTICE"."INSERT_SEQ".NEXTVAL;
Result:
IMPORTANT POINTS:
Description
A
sequence generates unique integers for use by multiple users. Use CURRVAL to
get the current value of the sequence and NEXTVAL to get the next value of the
sequence. CURRVAL is only valid after calling NEXTVAL in a session.
You can use CURRVAL and
NEXTVAL only in:
v The
select list of a SELECT statement which is not contained in a subquery, or view
v The
select list of a subquery in an INSERT statement
v The
VALUES clause of an INSERT statement
v The
SET clause of an UPDATE statement
You are not allowed to
use CURRVAL and NEXTVAL in:
v The
WHERE clause of a SELECT statement.
v A
subquery in a DELETE, SELECT, UPDATE, REPLACE or UPSERT statement.
v A
SELECT statement in a CREATE VIEW statement.
v A
SELECT statement with the DISTINCT operator.
v A
SELECT statement with a GROUP BY clause.
v A
SELECT statement with the UNION, INTERSECT, or MINUS set operator.
v The
DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.
Thank You for visiting my blog...
Share Your Comments...
its very useful for me kabil thank you
ReplyDelete