Sequences

What are sequences, and why are they used? A sequence is an automatic numbering tool that is very useful when you're inserting rows in a table and want to have one of the columns automatically filled in with sequential numbers . Suppose you were loading an AUTHORS table from a spreadsheet, or even manually, and you needed to have a unique, numerical identifier for each author as the rows were inserted. You could manually type in a number, or you could use the Oracle sequence feature to help you. The syntax is easy:

1. Create the sequence using the SEQUENCE command.

2. Use it with an INSERT statement.

Here's an example of sequence creation:

 CREATE SEQUENCE AUTHOR_SEQ increment by 1 start with 333333334; 

You can start anywhere and increment by any amount, and you can specify a maximum amount. If you increment by a negative number, the sequence will be descending.

Here's how we use a sequence:

 INSERT INTO AUTHORS            VALUES (AUTHOR_SEQ.NextVAL, 'Dr. Guerrilla'); 

And here's what the AUTHORS table contains:

Author_ID	Author
123456789Guerrilla Test Author
333333334Dr. Guerrilla [1]
111111111Guerrilla Test Author2
222222222Guerrilla Test Author3
333333333Waiver Administration

^[1] 333333334 is the one that came from the INSERT statement using the Create SEQUENCE AUTHOR_SEQ command shown above.

To delete a sequence, use the DROP commandthat is, DROP SEQUENCE AUTHOR_SEQ, for example. That's it.