Sequence

Oracle Sequence

A sequence is a sequence number generator. Sequence is used in a table to automatically populate primary key values or some ids. The sequence generated is incremented, independent of the transaction committing or rollback. If two users concurrently increment the same sequence, then the sequence number each user acquires may have gaps because the other users might have generated.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.  I don't recommend to use one sequence on two or more tables.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudo column, which returns the current value of the sequence, or the NEXTVAL pseudo column, which increments the sequence and returns the new value.

Let's do some exercise to see how sequence works!

SQL>
CREATE SEQUENCE my_sequence 
  INCREMENT BY 1 
  START WITH 1 
  NOMAXVALUE 
  MINVALUE 1 
  NOCYCLE 
  NOCACHE 
  ORDER; 
Sequence created.

SQL> SELECT my_sequence.NEXTVAL 
FROM   dual; 
NEXTVAL
----------
1

SQL> SELECT my_sequence.NEXTVAL 
FROM   dual; 
NEXTVAL
----------
2

SQL> SELECT my_sequence.CURRVAL 
FROM   dual;
CURRVAL
----------
2

SQL> SELECT my_sequence.NEXTVAL 
FROM   dual;
NEXTVAL
----------
3

Here's how you can use sequence in a query in select/insert  SQL statement!
--Select SQL Statement
SELECT Max(salary), 
       my_sequence.currval 
FROM   scott.employee; 

--Insert  SQL Statement:
INSERT INTO scott.employee 
            (id, 
             fname, 
             lname) 
VALUES      ( my_sequence.nextval, 
              'John', 
              'Smith'); 

Create Sequence Syntax:
CREATE SEQUENCE <sequence_name>             Sequence Name
INCREMENT BY <integer>                             Increment or Interval
START WITH <integer>                                   Starting value
MAXVALUE <integer> / NOMAXVALUE     Max sequence number
MINVALUE <integer> / NOMINVALUE             Start sequence number
CYCLE / NOCYCLE                                                   Repeat or Not Repeat
CACHE <#> / NOCACHE                                 Store in Memory Yes/No?
ORDER / NOORDER;                                       Order of Request or not?

Data Dictionary: USER_SEQUENCES, ALL_SEQUENCES, DBA_SEQUENCES
Privilege: CREATE SEQUENCE