Using Sequences in SQL

Consider the BOOK_LOAN table. Here we list all the details about the books borrowed by the borrower. In this table we can have multiple borrowers and books being entered multiple times. There is no guarantee on how many times it can occur or in which sequence. Hence we cannot determine the primary key of the table by using single column. We have already used composite key for this reason. But maintaining such key is also difficult in BOOK_LOAN table. But if we maintain a serial number like we do while listing the values in papers, it will be easy to track the order, number of records etc. But this will be difficult while inserting the records, as any user will not know which is last entry being entered. Hence he might have to query the table first to see last serial number and then insert the next value into the table. In order to avoid this lengthy process with serial numbers, SQL introduces ‘Sequences’ which is a automated numbers and can be incremented dynamically without checking the tables. But these are independent of tables / views. Sequence holds the numbers, which can be incremented by one or as specified by the user. Its current value can be viewed by querying it like we do any other tables. The general syntax to create a sequence is as shown below:

CREATE SEQUENCE sequence_name
START WITH initial_value – Initial value of the sequence
INCREMENT BY increment_value – value by which sequence needs to be incremented
MAXVALUE maximum_value – maximum value that it can be incremented to
CYCLE|NOCYCLE; — when max value is reached if it has to start from initial value or not

Let us create a sequence seq_bookloan_id, whose initial value is 100 and get incremented by 1 each time. Let the maximum value be 1000000 and let it restart when max value is reached. Though we have named it as seq_bookloan_id, it does not have any mapping with BOOK_LOAN table. We can use it independent of any table in DB.

CREATE SEQUENCE seq_bookloan_id
START WITH 100
INCREMENT BY 1
MAXVALUE 1000000
CYCLE;

Current value of the sequence can be obtained by using CURRVAL keyword with sequence name. But it will throw an error for the first time. This is because sequence is created but not yet initialized. It will be initialized only when it is used for the first time using NEXTVAL.

SELECT seq_bookloan_id.CURRVAL AS current_value FROM DUAL;

–Increments the sequence by the number specified at declaration; here it initializes the sequence

SELECT seq_bookloan_id.NEXTVAL AS current_value FROM DUAL;

Now if we write query to find current value, it will display the result.

SELECT seq_bookloan_id.CURRVAL AS current_value FROM DUAL;

SELECT seq_bookloan_id.NEXTVAL AS current_value FROM DUAL;

Let us see how to use sequence while inserting the records into table. Let us create a new column ID in BOOK_LOAN table to hold the serial number. Now we can insert the values into BOOK_LOAN like below:
Each time when we insert a record, next value of the sequence is made to populate. Hence it will guarantee that unique values are inserted into the table.

INSERT INTO BOOK_LOAN (ID,
           BRWR_ID, 
           ISBN,
           BOOK_CP_NUMBER,
           LOAN_DATE,
           DUE_DATE)
       VALUES (seq_bookloan_id.NEXTVAL,
          'ROS_1005', 
          '9789350871249', 
           2,
           TO_DATE ('5-Jun-15', 'DD-MON-YY'), 
           TO_DATE ('15-Jun-15', 'DD-MON-YY'));

Here name of the sequence is no way related to table. Its name is give so, just to identify with which it has to be used. We can use this sequence number while inserting records into other tables like below.

INSERT INTO CATEGORY (ID,
           CATEGORY_ID,
           CATEGORY_NAME)
        VALUES (seq_bookloan_id.NEXTVAL,
           'DBMS_1000',
           'Database');

This means that tables may not get the serial number if it is used with other tables or simply used in a query with NEXTVAL. Hence it should be taken care to use with same table and hence the names similar to table names.

Translate »