Sequence Basics

This shows how to create a basic sequence. CurrVal will show what number was last used. Whenever NextVal is referenced, the sequence gets incremented.

Set Feedback Off Heading Off
Drop Table TestTbl;
Create Table TestTbl
      ( Id   Number(4),
        Note Varchar2(20) );

Drop Sequence Test_Seq;
Create Sequence Test_Seq
         Increment By 1 Start With 1
         NoMaxValue NoCache NoCycle;

Insert Into TestTbl Values(Test_Seq.Nextval, 'Apple');
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 1

Insert Into TestTbl Values(Test_Seq.Nextval, 'Nuts' );
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 2

Select 'ID: '  ||ID         ||' '||
       'Note: '||Note
 from TestTbl;

ID: 1 Note: Apple
ID: 2 Note: Nuts

Select 'CurrVal: '||Test_Seq.CurrVal ||' '||
       'NextVal: '||Test_Seq.NextVal
  From Dual;

CurrVal: 3 NextVal: 3

/

CurrVal: 4 NextVal: 4