| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: [Newbie] Serial Keys - How to implement
A copy of this was sent to "David C. Harrison" <dch_at_dchsoftware.co.uk>
(if that email address didn't require changing)
On 23 Jun 1998 07:07:23 GMT, you wrote:
>
>I guess this is probably a RTFM, but the product has yet to be delivered,
>so
>I haven't got a manual yet, bummer.
>
>Anyhoo...
>
>How would I go about implementing an equivelant to the Informix serial data
>type
>in an Oracle database?
>
with a sequence..
>If I have a table like this:
>
> CREATE TABLE customer
> (
> customer INTEGER NOT NULL,
> name CHAR(32) NOT NULL,
> PRIMARY KEY (customer)
> );
>
create sequence my_seq;
create or replace trigger customer_bifer
before insert on customer
for each row
begin
select my_seq.nextval into :new.customer from dual;
end;
/
>How do I make a statement like this:
>
> INSERT INTO customer
> VALUES (0, 'Baney Rubble');
>
and now that will work....
>auto increment the value of the primary key?
>
>I guess I'm going to need a trigger/procedure pair, but do I need to create
>a
>table that stores the last used key? (give it row level locking, select and
>update
>in one transaction?) or do I just do a select MAX(customer) in the proc?
>
no... sequences are non-blocking number generators. many people can use at the same time without blocking. btw: when you look at sequences you'll see a 'currval' for getting the last value from the sequence. CURRVAL is *always* specific to your session. currval will not tell you the value someone else last selected -- currval is always the last value YOU selected...
>Any and all advice much appreciated.
>
>Free T-Shirt for the best answer! (no kidding)
>
>Thanks!
>
>--------------------------------------------------------------------------
>David C. Harrison
>DCH Software Limited
>Tel: +44 (0)1276 450031
>Fax: +44 (0)1276 450039
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 23 1998 - 05:28:52 CDT
 |
 |