sequence
A sequence is a database object from
which multiple users can generate unique sequence of numbers.
Syntax :
CREATE SEQUENCE <sequence
name> INCREMENT BY
<integer> START WITH <starting
integer> MAXVALUE <upper limit integer> (or NOMAXVALUE) MINVALUE <integer> (or NOMINVALUE) CYCLE or NOCYCLE CACHE <integer> (or
NOCACHE) ORDER or NOORDER
Sequence name - is the name of the sequence to be created.
INCREMENT BY – This optional parameter, specifies the interval between sequence numbers. This value
can be any positive or negative, but it cannot be zero. If this value is
negative, then the sequence descends. If the increment is positive, then the
sequence ascends. If you omit this clause, the interval is 1.
START WITH - specifies the first sequence number to be generated.
CYCLE - specifies that the sequence continues to generate values
after reaching maximum value. This is optional parameter.
CACHE - specifies how many values of the sequence to be kept in
memory. It ensures faster access. This
is optional parameter.
ORDER - guarantees that sequence numbers are generated in order of
request. You may want to use this option if you are using the sequence numbers
as timestamps This is optional parameter.
We can retrieve sequence values in SQL statements
with following pseudo columns :
CURRVAL - Teturns the current value of the sequence
NEXTVAL - increments the sequence and returns the new value.
Example
CREATE
SEQUENCE sampleseq
INCREMENT BY 2 START WITH 10
SQL>SELECT sampleseq.nextval from dual;
NEXTVAL
----------
10
SQL>SELECT sampleseq.nextval from dual;
NEXTVAL
----------
12
SQL>SELECT sampleseq.currval from dual;
CURRVAL
----------
12
SQL>SELECT sampleseq.currval from dual;
CURRVAL
----------
12
SQL>SELECT sampleseq.nextval from dual;
NEXTVAL
----------
14
DROP
SEQUENCE
-
To remove sequence from database.
Syntax :
DROP SEQUENCE <sequencename> ;
Example :
SQL>drop sequence sampleseq;
Sequence dropped.
No comments:
Post a Comment