Sequence

From Oracle FAQ

A sequence is a database object that generates unique numbers, mostly used for primary key values. One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.

[edit] History

Sequences were introduced with the Transaction Processing Option in Oracle 6.

[edit] Examples

Create a simple sequence:

CREATE SEQUENCE empseq;
 Sequence created.

Selecting from the sequence:

SQL> select empseq.nextval from dual;
   NEXTVAL
----------
         1

SQL> select empseq.nextval from dual;
   NEXTVAL
----------
         2

Note that nextval and currval returns the same value for each row of a select:

SQL> select empseq.nextval, empseq.currval, empseq.nextval, empseq.currval from dual;
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         3          3          3          3

SQL> select empseq.nextval, empseq.currval, empseq.nextval, empseq.currval 
  2  from (select 1 from dual union all select 2 from dual)
  3  /
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         4          4          4          4
         5          5          5          5

Creating a more complicated sequence:

CREATE SEQUENCE my_sequence
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
   INCREMENT BY 2
   CACHE 5;

Reset a sequence to a predetermined value, say from 100 to 50:

SQL> ALTER SEQUENCE seq1 INCREMENT BY -50;
SQL> SELECT seq1.nextval FROM dual;
SQL> ALTER SEQUENCE seq1 INCREMENT BY 1;

[edit] Also see

• ORA-02287, sequence number not allowed here.
• AutoNumber and Identity columns

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#