Re: Random number generator in plsql

>Does someone has a routine which enables me to generate random numbers in
>PLSQL ?
This is a repost of a previous article from this newsgroup. This is not my code nor have I made use of it. It is just something I kept on file for future reference. If you intend to use it, you may want to contact the original poster listed below for his permission or any questions you may have.

Regards,

--
Ray Dawson
Oracle Applications Specialist
Mastech Systems Corp.

---------- Forwarded message ----------
Date: Wed, 11 Jun 1997 13:10:30 +0100
From: Brian Ewins <Brian.Ewins_at_gssec.bt.co.uk>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Random selection of records in PL/SQL

doid wrote:


> 

> HShukla538 wrote:

> >

> > How can i select a true random record in pl/sql

> 


I've done this in the past using code like:

foo:=random.next_int(range)+offset;
select * into rec
from bar
where key>foo
and rownum=1;

(ie I have a numeric key in the table, I generate a random
number based on the offset and range of the values of this
key (offset=min(key), range=max(key)-min(key)).)

The random number generator I used varied; in one case I
was doing this in perl, so I used its random number generator
to help build the sql; in other cases I've used a random number
generator I converted from one in Numerical Recipes in C
(Oxford University Press). The generator was a linear
congruential generator with shuffling and had a pretty long
period, so was random enough for almost any purpose.

The code is included below - use it at your own risk. It's 
not as efficient as it could be, but I didn't want to spend much
time on it, other than to get a working conversion.

	Hope this helps.
		Brian

=========================================BEGIN
set verify off
set feedback off

-- Random number generator, adapted from 'Numerical Recipes in C',
-- second edition, p 282. A generator due to L'Ecuyer with Bays-Durham
-- shuffle... ie, a simple 'linear congruential' generator, with a 
-- table of the last 32 generated numbers, from which one is chosen to
-- be handed out and replaced with the latest number, using a second
generator,
-- so that the combined period is very long (> 2 x 10^18).
-- There are 4 public functions: 
-- seed(x) seeds the random sequence with x.
--      since the sequence only has good random properties if we let it
go,
--      this should be called sparingly; the code has been expicitly
written
--      to allow the random number tables to persist.
-- seed on its own (no arguments) seeds the generator using the current
time.
--      this is used at the end of this file to seed the generator for
the first
 time
-- next_num : no parameters. returns a number in the range 0..1
excluding
--      endpoints. The random numbers have a uniform deviate and are
statistical
ly
--      pretty good at being random...
-- next_int(x): given a maximum value x, return integers in the range
0..x,
--      including 0 but not including x.
-- next_string(x): returns a string x chars long, consisting of the
letters
--      a-z. This is intended for use in creating 'cookie' type session
persiste
nce
--      for the web server.

-- static store required to make data persist:
drop table random_persist;
create table random_persist 
(id varchar2(10),value number)
tablespace tsdata
storage (initial 10k next 10k pctincrease 0);

create or replace package random is
        -- constants
        -- first linear congruential generator
        IM1     constant number := 2147483563;
        IA1     constant number := 40014;
        IQ1     constant number := 53668;
        IR1     constant number := 12211;

        -- syntactic sugar...
        IMM1    constant number := IM1-1;
        AM      constant number := 1.0/IM1;

        -- second generator, used for shuffling
        IM2     constant number := 2147483399;
        IA2     constant number := 40692;
        IQ2     constant number := 52774;
        IR2     constant number := 3791;

        -- size of shuffle table
        NTAB    constant number := 32;
        NDIV    constant number := (1+IMM1/NTAB);

        -- precision (32 bit), maximum number.
        EPS     constant number := 1.2e-7;
        RNMX    constant number := 1.0-EPS;

        -- PUBLIC INTERFACE
        procedure seed(seed_val in number default null);
        function next_num return number;
        function next_int(max_int integer) return integer;
        function next_string(chars in integer) return varchar2;

        --PRIVATE UTILITIES
        function get_persistent(id varchar2) return number;
        procedure set_persistent(id varchar2, value number);
end random;
/
show errors;
create or replace package body random is

        -- seed function. this should only be used for test purposes;
        -- the call at the end of this file does the initialisation
        -- normally. Note that the generator is intended to be static
        -- across database _instances_ so that we don't try to generate
        -- the same numbers several times in succession.
        procedure seed(seed_val in number default null) is
                idum1 number;
                k number;
                j integer;
        begin
                if seed_val is null then
                        --set seed from current time.
                        idum1:=to_number(to_char(sysdate,'SSSS'));
                else
                        idum1:=floor(seed_val);
                end if;
                if idum1<1 then 
                        idum1:=1;
                end if;
                set_persistent('idum2',idum1);
                for j in reverse 0..(NTAB+7)
                loop
                        k:=floor(idum1/IQ1);
                        idum1:=IA1*(idum1-k*IQ1)-k*IR1;
                        if idum1<0 then 
                                idum1:=idum1+IM1;
                        end if;

                        if j<NTAB then 
                                set_persistent(to_char(j),idum1);
                        end if;
                end loop;
                set_persistent('iy',get_persistent('0'));
                set_persistent('idum1',idum1);
        end seed;

        function next_num return number is
                idum1 number;
                idum2 number;
                iy number;
                temp number;
                k number;
                j integer;
        begin
                -- restore the persisted values
                idum1:=get_persistent('idum1');
                idum2:=get_persistent('idum2');
                iy:=get_persistent('iy');

                --generator1 begins here.
                --uses Schrager's method for calculating
                --mod without overflow. Not really necessary
                --here; number's are much bigger than 32 bit.
                k:=floor(idum1/IQ1);
                idum1:=IA1*(idum1-k*IQ1)-k*IR1;
                if idum1<0 then 
                        idum1:=idum1+IM1;
                end if;

                -- second generator.
                k:=floor(idum2/IQ2);
                idum2:=IA2*(idum2-k*IQ2)-k*IR2;
                if idum2<0 then 
                        idum2:=idum2+IM2;
                end if;

                -- shuffle/combine generators
                j:=floor(iy/NDIV);
                iy:=get_persistent(to_char(j))-idum2;
                if (iy<1) then 
                        iy:=iy+IMM1;
                end if;

                -- before returning, set the new persistents...
                set_persistent(to_char(j),idum1);
                set_persistent('idum1',idum1);
                set_persistent('idum2',idum2);
                set_persistent('iy',iy);

                --prevent endpoints being returned
                temp := AM*iy;
                if temp>RNMX then 
                        temp:=RNMX;
                end if;

                return temp;
        end next_num;

        -- maintain persistent storage
        function get_persistent(id varchar2) return number is
        retval number;
        begin
                select value into retval
                from random_persist
                where id=get_persistent.id;
                return retval;
        end get_persistent;

        procedure set_persistent(id varchar2,value number) is
        small_string random_persist.id%type;
        begin
                small_string:=substr(id,1,length(small_string));

                insert into random_persist
                (id,value)
                select 
                small_string, set_persistent.value
                from dual where not exists
                (
                        select null
                        from random_persist
                        where id=small_string
                );

                update random_persist
                set value=set_persistent.value
                where id=set_persistent.id;

        end set_persistent;

        -- convenience function to return integers up to a specified
range.
        function next_int(max_int integer) return integer is
        begin
                return floor(next_num*max_int);
        end next_int;

        -- return the random number as a base 26 number, with digits
        -- as letters in the range a-z. This prevents the user seeing
        -- both the internal representation and the full precision of
the
        -- number, to increase security. 32-bit randoms will return
        -- trailing 'AAA...' strings for more than about 10 chars.
        function next_string(chars in integer) return varchar2 is
        next_letter integer;
        i integer;
        temp number;
        retval varchar2(32);
        begin
                retval:='';
                temp:=next_num;
                for i in 1 .. chars
                loop
                        next_letter:=floor(temp*26);
                        temp:=(temp*26)-next_letter;
                       
retval:=concat(retval,CHR(next_letter+ASCII('a')));
                end loop;
                return retval;
        end next_string;
end random;
/
show errors;

-- seed random number from the time.
exec random.seed
===================================END


--
****====----                                              Brian Ewins. 
Fax: (44) 141 220 6100                          Tel: (44) 141 220 6121
 "It's time we face reality, my friends...
      We're not exactly rocket scientists." --Gary Larson ----====****