| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sequences
This is a multi-part message in MIME format.
------=_NextPart_000_0539_01BFEC08.FBEBEEA0 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Glen
Just some basic info.
With regards to the use of sequences which are actually separate Oracle =
objects like
tables and indexes, they are used in INSERT statements along with the =
pseudocolumn
NEXTVAL which generates a new sequence number or CURRVAL which says =
which is
the last sequence value used.
e.g. To create a sequence that will start with the value of 1000 and =
increment by 1
such that the sequence is 1000, 1001, 1002, etc. this could be the SQL =
statement used
( there are other options which you could check in your SQL Reference =
manual or=20
Oracle CD documentation ) :
CREATE SEQUENCE employee_empno_seq
START WITH 1000
INCREMENT BY 1
NOCACHE;=20
( by default Oracle will generate 20 sequence values and place them in =
the cache for
future use by users )
The sequence could then be referenced in an INSERT statement using =
NEXTVAL e.g:
INSERT INTO employee( empno, name )
VALUES (employee_empno.NEXTVAL, 'SMITH');
And this would generate the next value in the sequence and insert it =
into the empno column
of the employee table. Since we didn't use the sequence yet, =
employee_empno.NEXTVAL
would generate the value 1000. And if we were to reference the sequence =
again in another
INSERT statement e.g.
INSERT INTO employee( empno, ename )
VALUES ( employee_empno.NEXTVAL, 'GREEN');
A row with the values 1001 for the empno and GREEN for the ename would =
now be inserted.
So each time NEXTVAL is referenced it generates a new unique value for =
the sequence. Follow?
Hope this info helped : )
Regards
Natasha
I wanted to ask how people are handling the insertion of unique keys =
in Oracle. Please understand that my experience with generating unique =
keys comes from an Informix background, wherein you can have a column =
with datatype of 'serial'. This is an integer column which gets =
generated at insert time with the next value automatically. You can =
then check the sqlca area (return buffer) for the inserted value.
=20
I know in Oracle you can accomplish this with the SEQUENCE function. =
As this is not automatically inserted by Oracle, how is the unqiue key =
value inserted? Do most people use a before trigger, stored procs, or =
call the sequence themselves? If using a client program (c, java), how =
is the value for the unique key returned to the program which performed =
the insert?
Examples, methods, design recommendations welcome...
------=_NextPart_000_0539_01BFEC08.FBEBEEA0 Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Glen</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Just some basic info.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>With =
regards to the=20
use of sequences which are actually separate Oracle objects=20
like</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>tables =
and indexes,=20
they are used in INSERT statements along with the=20
pseudocolumn</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D770075615-12072000>NEXTVAL which=20
generates a new sequence number or CURRVAL which says which=20
is</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>the =
last sequence=20
value used.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D770075615-12072000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>e.g. =
To create a=20
sequence that will start with the value of 1000 and increment by=20
1</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>such =
that the=20
sequence is 1000, 1001, 1002, etc. this could be the SQL statement=20
used</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>( =
there are=20
other options which you could check in your SQL Reference manual or=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>Oracle =
CD documentation ) :</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D770075615-12072000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>CREATE =
SEQUENCE=20
employee_empno_seq</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>START =
WITH=20
1000</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D770075615-12072000>INCREMENT BY=20
1</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D770075615-12072000>NOCACHE;=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>( by =
default Oracle=20
will generate 20 sequence values and place them in the cache=20
for</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>future =
use by users=20
)</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV>The sequence could then be referenced in an INSERT statement using =
NEXTVAL=20
e.g:</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>INSERT =
INTO=20
employee( empno, name )</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>VALUES =
(employee_empno.NEXTVAL, 'SMITH');</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D770075615-12072000></SPAN></FONT> </DIV>
<DIV>And this would generate the next value in the sequence and insert =
it into=20
the empno column</DIV>
<DIV>of the employee table. Since we didn't use the sequence=20
yet, employee_empno.NEXTVAL</DIV>
<DIV>would generate the value 1000. And if we were to reference the =
sequence=20
again in another</DIV>
<DIV>INSERT statement e.g.</DIV>
<DIV> </DIV>
<DIV>INSERT INTO employee( empno, ename )</DIV>
<DIV>VALUES ( employee_empno.NEXTVAL, 'GREEN');</DIV>
<DIV> </DIV>
<DIV>A row with the values 1001 for the empno and GREEN for the =
ename would=20
now be inserted.</DIV>
<DIV>So each time NEXTVAL is referenced it generates a new unique value =
for the=20
sequence. Follow?</DIV>
<DIV> </DIV>
<DIV>Hope this info helped : )</DIV>
<DIV> </DIV>
<DIV>Regards</DIV>
<DIV>Natasha</DIV>
<DIV> </DIV></FONT></DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: =
0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A href=3D"mailto:Glenn.Travis_at_wcom.com" =
title=3DGlenn.Travis_at_wcom.com>Glenn=20
Travis</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
href=3D"mailto:ORACLE-L_at_fatcity.com"=20
title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> =
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, July 12, 2000 =
1:23=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> sequences</DIV> <DIV><BR></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D770075615-12072000>I =wanted to ask=20
 |
 |