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. In Oracle, we first need to create the sequence. The syntax for creating a sequence 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.

Next, assume we have a table as follows:

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 will specify that we want to use the sequence and the NEXTVAL function in the INSERT INTO statement as follows:

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 © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact