Re: lowest free number

In <31CD92EA.3DEE_at_fhzinfo.fh-bielefeld.de>, Michael Daehling <daehling_at_fhzinfo.fh-bielefeld.de> writes:
>Hi,
>
>I want to get the lowest free number of a column. The column is a continuous
>number and the primary key of the table. I want to recycle the old numbers
>after delete.
>I think a PL/SQL-function can do this - or is there a simple solution?
>
>
>Thanks in advance
>
>Michael Daehling
>--
>------------------------------------------------------------------------------
>Michael Daehling
>FH-Bielefeld Email: daehling_at_fhzinfo.fh-bielefeld.de
>Willhelm-Bertelsmann-Str.10 Tel.: +49 521 106 2334
>33602 Bielefeld
>------------------------------------------------------------------------------
>
Michael,

Here's two methods, hope one of them helps;

method 1
use on on-delete trigger to store deleted numbers into a 'discarded' table, then a simple select min(value) from discard; would quickly give you the required number (if empty then choose the next sequence number) (thanks Liam)

method 2
PL/SQL
function next_num is
count :=1 -- or lowest possible value
for rec in (select col from table

            order by col)
loop

    if count < rec.col -- should give min value

       return count
    end if
end loop
return count -- if non missing, give next value

• (or replace with sequence name) end; the syntax not is exactly right - but i don't have time to find out

Bye,
Steve

opinions expressed are mine and do not
necessarily represent those of my employer Received on Mon Jun 24 1996 - 00:00:00 CEST