On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, March 3, 2009

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

No comments:

Post a Comment