Creating and Dropping a Sequence With SQL

A sequence is a database object that generates unique sequential values, often used for primary and unique keys. You can refer to sequence values in SQL statements with the CURRVAL and NEXTVAL pseudocolumns.

To generate a sequence number, you call the sequence using the CURRVAL or NEXTVAL keywords. You must qualify CURRVAL and NEXTVAL with the name of the sequence, such as employees_seq.CURRVAL or employees_seq.NEXTVAL. Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Example: Creating a Sequence Using SQL shows how to create a sequence that can be used with the employees table. The sequence can also be used with other tables. For more information about sequences, see "Managing Sequences".

Creating a Sequence Using SQL

-- create a new sequence to use with the employees table
-- this sequence starts at 1000 and increments by 1
CREATE SEQUENCE new_employees_seq START WITH 1000 INCREMENT BY 1;

-- to use the sequence, first initialize the sequence with NEXTVAL
SELECT new_employees_seq.NEXTVAL FROM DUAL;

-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES 
  (new_employees_seq.CURRVAL, 'Pilar', 'Valdivia', 'pilar.valdivia',
  '555.111.3333', '01-SEP-05', 'AC_MGR', 9100, .1, 101, 110);

-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Valdivia';

Example: Dropping a Sequence Using SQL shows how to drop the sequence that you previously created.

Dropping a Sequence Using SQL

-- drop the sequence
DROP SEQUENCE new_employees_seq;

For information about creating and dropping a sequence with the Object Browser page, see "Creating a Sequence" and "Dropping a Sequence".