SQL > Advanced SQL > SEQUENCE And NEXTVAL

Oracle uses the concept of SEQUENCE to create numerical primary key values as we add rows of data into a table. Whereas numerical primary key population for MySQL and SQL Server is tied to individual tables, in Oracle the SEQUENCE construct is created separately and is not tied to an individual table.

Syntax

The syntax for creating a sequence in Oracle is:

CREATE SEQUENCE SEQUENCE_NAME
[START WITH {Initial_Value}]
[INCREMENT BY {interval}];

{Initial_Value} is the starting value of the sequence, and {interval} is the interval between consecutive sequence numbers. Both [START WITH] and [INCREMENT BY] are optional fields. If they are not specified, the default {Initial_Value} and {interval} are both 1.

Example

Assume we have a table with the following structure:

Table USER_TABLE

Useridinteger
Last_Namevarchar(50)
First_Namevarchar(50)

and we want to use the following sequence to generate the userid:

CREATE SEQUENCE SEQ_USER START WITH 5 INCREMENT BY 5;

We specify that we want to use the sequence and the NEXTVAL function in the INSERT INTO statements in the following order:

INSERT INTO USER_TABLE VALUES (SEQ_USER.NEXTVAL, 'Washington', 'George');

INSERT INTO USER_TABLE VALUES (SEQ_USER.NEXTVAL, 'Jefferson', 'Thomas');

Now the table has the following two rows:

Table USER_TABLE

UseridLast_NameFirst_Name
5WashingtonGeorge
10JeffersonThomas

It is worth noting that a sequence is independent of a table. In other words, a sequence can be used to generate primary key values for multiple tables, and the sequence continues even if it is being applied to a different table. So, let's say for example we have a second table, Table NEW_USERS, which has the same structure as table USER_TABLE, and we issue the following SQL command after executing the two SQL commands above:

INSERT INTO NEW_USER VALUES (SEQ_USER.NEXTVAL, 'Adams', 'John');

Table NEW_USER will have the following row:

Table NEW_USER

UseridLast_NameFirst_Name
15AdamsJohn

Userid is 15 because that is the next value after 10.

Next: SQL Rank




Copyright © 2017   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact