Creating and managing sequences

A sequence is a database object in Oracle that is used to generate a number sequence. Sequences are mostly used for primary key values.

We will create a sequence using the CREATE SEQUENCE statement as follows:

-- Create a new sequence
CREATE SEQUENCE emp_seq;

After creating a sequence, we can access its values in SQL statements using the CURRVAL and NEXTVAL pseudo-columns. CURRVAL returns the current value of a sequence and NEXTVAL increments the sequence and returns the new value:

-- Fetch a new value from emp_seq
SELECT emp_seq.NEXTVAL FROM dual;
-- Query the current value of emp_seq
SELECT emp_seq.CURRVAL FROM dual;

Sequences in their simplest form increment by one; however, we can create a sequence to increment/decrement by any value.

We can modify a sequence using the ALTER SEQUENCE statement to change the increment and other sequence properties. This statement affects only future sequence numbers. The following is an example of the ALTER SEQUENCE statement...