USING A SEQUENCE

Now we are going to know how to use a sequence

After the creation of sequence, it generates sequential numbers for use in our tables. Reference the sequence values by using the NEXTVAL and CURRVAL Pseudo columns.

Pseudo Column-

It behaves like table column, but it is not actually stored in a table.

The available pseudo columns are:-

CURRVAL

NEXTVAL

LEVEL

ROWID

ROWNUM

CURRVAL- it is used to refer to a sequence number that the current user has just generated. Next value must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced. We must qualify CURRVAL with sequence name. When sequencename.CURRVAL is referenced , the last value returned to that users process is displayed

NEXTVAL-it is used to extract successive sequence numbers from a specified sequence. We must qualify NEXTVAL with a sequence name. When we reference sequencename.NEXTVAL , a new sequence number is generated and current sequence number is placed in CURRVAL.

SQL> create table seq_tab(empno number(10),ename varchar2(10),dept number(10));

-- Table created

Now we are going to insert a record into seq_tab table

SQL> insert into seq_tab values(sampleseq.nextval,'CAMERON',sampleseq.currval);

-- 1 row inserted

SQL>select *from seq_tab;

Sampleseq- it is the name of the sequence what we have created in previous lesson.

Nextval- it is pseudo column.

Now, if we again insert the record into same table

SQL> insert into seq_tab values(sampleseq.nextval,'JOHN',sampleseq.currval);

-- 1 row inserted

SQL>select *from seq_tab;

Here we can see that sequence number is automatically generated for empno and currval is displaying for dept which we had used sequence name (sampleseq.nextval) and sampleseq.currval while we are inserting the record i.e 2, 3.

We can use sequence in update statement also

SQL> update seq_tab set empno=sampleseq.nextval where empno=2;

SQL> select *from seq_tab;

We can use update for NEXTVAL only but not for CURRVAL