«Back to Home

Oracle Jump Start
Topics

How To Use Sequence (autonumber) in Oracle

Description

A sequence is an object, which is used to generate a sequence number(autonumber) in Oracle.

Sequence

A sequence is an object, which is used to generate a sequence of numbers in Oracle. You can use sequence to automatically generate the primary key values.
Sequence of numbers are generated independently of tables, so the same sequence is used for one or more multiple tables.
When a sequence is created, you can access the values in SQL statement.

Syntax
  1. Create sequence sequence_name  
  2. Minvalue value  
  3. Maxvalue value  
  4. Start with value  
  5. Increment by value  
  6. Cache value;  
Creation of sequences

Sequences are created by the use of the create sequence statement.

Minimum value

Specify the minimum value of the sequences.

Maximum value

Specify the maximum value the sequence, which can be generated.

Start with value

Specify the first sequence number, which you want to be generate.

Increment By

How to increment the sequence. The integer value can be any positive or negative value but it cannot be zero.
If it has the positive value, the value is in an ascending order. If the value is negative, the value is in descending order.

Cache

Cache is used for the speed access but cache memory is deleted, when the database is shutdown.
Cache default value is 20.

Example
  1. CREATE SEQUENCE Employee_seq  
  2. MINVALUE 1  
  3. MAXVALUE 999999999999999999999999999  
  4. START WITH 7008  
  5. INCREMENT BY 1  
  6. CACHE 20;  
1

Inserting values
  1. INSERT INTO Employee  
  2. (Emp_id, Emp_name, Emp_job)  
  3. VALUES  
  4. (Employee_seq.NEXTVAL, 'Blick', 'marketing');  
  5. INSERT INTO Employee  
  6. (Emp_id, Emp_name, Emp_job)  
  7. VALUES  
  8. (Employee_seq.NEXTVAL, 'Andrew', 'HR');  
  9. INSERT INTO Employee  
  10. (Emp_id, Emp_name, Emp_job)  
  11. VALUES  
  12. (Employee_seq.NEXTVAL, 'Bhusan', 'Clerk');  
  13. INSERT INTO Employee  
  14. (Emp_id, Emp_name, Emp_job)  
  15. VALUES  
  16. (Employee_seq.NEXTVAL, 'Wynk', 'Sales');  
2 

Drop Sequence

First, you have to create a sequence in Oracle and you need to drop a sequence in your database.

Syntax
  1. Drop Sequence Sequence_name;  
Sequence name

Sequence name is a name, which you want to drop sequence in your database.

Example
  1. Drop sequence Employee_seq;  
3

Summary

Thus, we learnt, a sequence is an object, which is used to generate a sequence number (autonumber) in Oracle. We learnt, how to use sequence in Oracle with the examples.