Re: Strange PB !?
Date: Sun, 13 Jun 1999 07:54:22 -0500
Message-ID: <7k09qr$be3$1_at_news1.inlink.com>
[Quoted] Steve Cosner wrote in message <7jveev$h7g$1_at_nntp.csufresno.edu>...
>In article <7jv0j6$l84$1_at_nnrp1.deja.com>,
>Wassim <net2000_at_francemel.com> wrote:
>>I've in many of my PL/SQL procedures to create a new record in a
table
>>just with Key code = Last key + 1, (key_code is the table primary
key)
>>
>>--------------------------------------------------------------------
>>last_key_code := 0;
>>begin
>>select max(key_code) into last_key_code from myTable;
>>exception when OTHERS then last_key_code:=0; -- when myTable is
empty !!
>>end;
>>
>>htp.print(last_key_code);
>>
>>insert into myTable values (last_key_code+1,...);
>>--------------------------------------------------------------------
>
>Yes, a sequence is the accepted way to insert a new key value. But
>your way would work, as well. It is just that when there are no rows
>in your table, the max function returns null. It does not produce an
>exception as you are trying to trap.
>
Actually, there are circumstances in a multi-user database where using "max(key_code) + 1" will NOT work, i.e. what happens when someone else inserts a record (during the fraction of a second) between your calculation and your insert? Your insert fails...
A sequence is by far the best way to do this. There are other ways, but they either do no more than duplicate the functionality of a sequence, or they involve locking the table so nobody else can insert. Received on Sun Jun 13 1999 - 14:54:22 CEST