Working with Sequences in an Oracle database

Have you ever wondered how bank account numbers have been created in such a way that no two people can have the same account number? Better still how about Identity numbers like university matriculation number, staff id or Passport numbers. How are they generated without the possibility of a duplication? 
This is made possible because of an object that does the tracking called a SEQUENCE.

Sequences are schema objects that help to provide unique sequential values by generating unique integers.

Sequences offer the opportunity of automating your unique values for you.

Let’s look at this scenario

Imagine you resumed in an organization that has over a million customers with unique customer ids.

You were asked to manually create three (3) new customers and provide them with their new customer id. (Note that these customer ids are unique and you must avoid duplication).

Without sequence this will be very challenging for you as it will entail the following if you were to do it manually:

·         Request for the last customer id (if at all it was documented)

·         Continue from the last customer id.

This manual method is prone to a lot of duplication errors hence why the automation was done using sequence.

You can create a new sequence in Oracle database using the CREATE SEQUENCE statement.

When creating a new sequence using the CREATE SEQUENCE statement, you must specify a name for the sequence. You can also use other clauses to further define the sequence.

The following are the commonly used clauses of the CREATE SEQUENCE statement:

·         START WITH – Specifies an initial value for the sequence. For an ascending sequence, the default value of the START WITH clause is the minimum value, which is specified by the MINVALUE clause, of the ascending sequence. For a descending sequence, the default value of the START WITH clause is the maximum value, which is specified by the MAXVALUE clause, of the descending sequence. 

·         INCREMENT BY – Specifies how to generate the sequence numbers after the first number. The default value of the INCREMENT BY clause is 1. You can specify any nonzero integer less than 29 digits as the value for the INCREMENT BY clause. When the INCREMENT BY clause has a negative value, then a descending sequence with progressively lower numbers is generated. An INCREMENT BY clause with a positive value generates an ascending sequence with progressively higher numbers.

·         MAXVALUE – Specifies the highest number that a sequence can generate. The default value of the MAXVALUE clause is the NOMAXVALUE constant. The MAXVALUE Constant evaluates to 1027 for an ascending sequence and −1 for a descending sequence. 

·         MINVALUE – Specifies the lowest value that a sequence can generate. The default value of the MINVALUE clause is the NOMINVALUE constant. The NOMINVALUE constant evaluates to −1026 or an ascending sequence and −1 for a descending sequence.

Note  

This tutorial will be done using SQLPLUS command prompt.

Click here to view this tutorial using Oracle LIVE SQL

Now let’s create a sequence and implement it on the staffs table.

1. Log in to SQLPLUS as HR

2. Type in the following Sql:

CREATE SEQUENCE id_seq

START WITH 1

INCREMENT  BY 1

MAXVALUE 1000 MINVALUE 1;     

This is what we have done:

·         We have created a sequence called id_seq.

·         The first number will be 1 and every subsequent number will be an increment of 1.

·         It should continue like this until it reaches 1000 which is the maximum number we have designed it to reach.

Now how do we implement this sequence?

Let’s use this exercise to understand how sequences are used

Exercise

1.    Create a table called CUSTOMERS with the following columns and data type

Column	Data type
CUST_ID	number(5)
CUST_FIRST_NAME	varchar2(30)
CUST_LAST_NAME	varchar2(30)
BUSINESS_EMAIL	varchar2(50)

Create table CUSTOMERS

(CUST_ID  number(5) not null,

CUST_FIRST_NAME varchar2(30),

CUST_LAST_NAME varchar2(30),

BUSINESS_EMAIL varchar2(50));

2.    Create a sequence called CUST_ID_SEQ to generate unique values for the CUST_ID column in the CUSTOMERS table.

CREATE SEQUENCE CUST_ID_SEQ

START WITH 1

INCREMENT  BY 1

MAXVALUE 10000 MINVALUE 1;     

 3.    Insert the values below into our CUSTOMERS table

CUST_ID	CUST_FIRST_NAME	CUST_LAST_NAME	BUSINESS_EMAIL
	Jamiu	Musa	jmusa@exp.com
	Ada	Obi	aobi@pst.com
	Kunle	Afolabi	kafolabi@aim.com
	Mike	Peters	mpeters@go.co.uk

Notice I did not put any value in the CUST_ID column. This is because I want the sequence generator to input it automatically for me.

This is where we will be initiating the sequence generator using the NEXTVAL command

INSERT INTO CUSTOMERS (CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME, BUSINESS_EMAIL)

VALUES (CUST_ID_SEQ.NEXTVAL,'Jamiu','Musa','jmusa@exp.com');

COMMIT;

Notice the highlighted. We invoked the sequence generator we created earlier called CUST_ID_SEQ and appended it with NEXTVAL command. This command will fetch the last sequence and insert the next value after it (Simple?)

Now let’s input the rest of the data. Please do not forget to use the COMMIT command to save the inserts.

INSERT INTO CUSTOMERS (CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME, BUSINESS_EMAIL)

VALUES (CUST_ID_SEQ.NEXTVAL,'Ada','Obi','aobi@pst.com');

INSERT INTO CUSTOMERS (CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME, BUSINESS_EMAIL)

VALUES (CUST_ID_SEQ.NEXTVAL,'Kunle','Afolabi','kafolabi@aim.com');

INSERT INTO CUSTOMERS (CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME, BUSINESS_EMAIL)

VALUES (CUST_ID_SEQ.NEXTVAL,'Mike','Peters','mpeters@go.co.uk');

COMMIT;

4.    Let us now query our COSTUMERS table

Set lin 300

Select * from CUSTOMERS;

Notice that the sequence has generated the unique values for our CUST_ID column.

And that’s how it’s used.

Resetting a Sequence

A sequence can be reset by simply dropping (deleting) the sequence and recreating it.

Let me show you with a few exercises:

1.    Create a sequence called TEST_SEQ

CREATE SEQUENCE TEST_SEQ

START WITH 1

INCREMENT BY 1

MAXVALUE 1000 MINVALUE 1; 

2.    Generate 3 sequences. We will do this by generating it on a scratch book table called a DUAL table. Dual table is a temporary table for drafts data.

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

3.    Drop (delete) the sequence TEST_SEQ

Drop sequence TEST_SEQ;

4.    Recreate the sequence

CREATE SEQUENCE TEST_SEQ

START WITH 1

INCREMENT BY 1

MAXVALUE 1000 MINVALUE 1; 

5.    Let’s generate 3 more sequences with our dual table.

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

Notice that the sequence has started all over again.

So that’s the basic way sequences are restarted.

Starting a Sequence from a specific number

You can set a sequence to start from a specific number.

Let’s do our last exercise:

1.    Drop the TEST_SEQ

Drop sequence TEST_SEQ;

2.    Recreate it to start from 24

CREATE SEQUENCE TEST_SEQ

START WITH 24

INCREMENT BY 1

MAXVALUE 1000 MINVALUE 1;  

3.    Let’s test our sequence out

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

select TEST_SEQ.NEXTVAL from DUAL;

And that’s it about working with Sequences.

I hope this post has been interesting as much as it was informative.

Would you like to read more on Sequences?

Click here to go to the oracle site

Stay tuned for more.