November 9, 2009

Creating an Oracle Sequence in SQL

A sequence can be created using the following command:
CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.
SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?
SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;
Sequence created.
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
1
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
4
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
7

1 comment: