Re: Random Number Generator In PL/SQL

From: Chuck Erickson <beachst.cerick01_at_eds.com>
Date: 1997/08/20
Message-ID: <33FB89DE.5E34_at_eds.com>#1/1


There are several random number generators floating around including the one sent out by Thomas Kyte below and another below that which Oracle distributes in some demo code.

[Quoted] Can anyone provide further information on the origin of these and on the applicability or soundness of these generators. Has anyone done any type of statistical analysis validating either one. Any comments would be appreciated.
Thanks.

Thomas Kyte wrote:
>
> On 18 Jul 1997 16:57:36 GMT, Anthony B. Cillo <tmaxwell_at_crl.crl.com> wrote:
>
> >Anyone have any code to do this?
> >
> >
> >Thanks
> >
> >Tony
> >
>
> Here is one....
>
> create or replace package random
> is
> pragma restrict_references( random, WNDS, RNPS );
>
> procedure srand( new_seed in number );
>
> function rand return number;
> pragma restrict_references( rand, WNDS );
>
> procedure get_rand( r OUT number );
>
> function rand_max( n IN number ) return number;
> pragma restrict_references( rand_max, WNDS);
>
> procedure get_rand_max( r OUT number, n IN number );
>
> end random;
> /
>
> create or replace package body random
> is
> multiplier constant number := 22695477;
> increment constant number := 1;
> "2^32" constant number := 2 ** 32;
> "2^16" constant number := 2 ** 16;
> "0x7fff" constant number := 32767;
> Seed number := 1;
> --
> procedure srand( new_seed in number )
> is
> begin
> Seed := new_seed;
> end srand;
> --
> function rand return number
> is
> begin
> seed := mod( multiplier * seed + increment, "2^32" );
> return bitand( seed/"2^16", "0x7fff" );
> end rand;
> --
> procedure get_rand( r OUT number )
> is
> begin
> r := rand;
> end get_rand;
> --
> function rand_max( n IN number ) return number
> is
> begin
> return mod( rand, n ) + 1;
> end rand_max;
> --
> procedure get_rand_max( r OUT number, n IN number )
> is
> begin
> r := rand_max( n );
> end get_rand_max;
> --
> begin
> select userenv( 'SESSIONID' ) into seed from dual;
> end random;
> /
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

From ows/demo/util.sql
...
create or replace package random is
/* Linear congruential random number generator */  

/* Returns random integer between [0, r-1] */ function rndint(r in number) return number;  

/* Returns random real between [0, 1] */ function rndflt return number;  

end; /* package random */
/  

/********** the package body: **********/  

create or replace package body random is /* Linear congruential random number generator */  

m constant number:=100000000;  /* initial conditions */ 
m1 constant number:=10000;     /* (for best results) */ 
b constant number:=31415821;   /*                    */ 
 
a number;                      /* seed */ 
 
the_date date;                 /*                             */ 
days number;                   /* for generating initial seed */ 
secs number;                   /*                             */ 
 
/*-------------------------- mult ---------------------------*/ 
/* Private utility function */  

function mult(p in number, q in number) return number is

   p1 number; 
   p0 number; 
   q1 number; 
   q0 number; 

begin
   p1:=trunc(p/m1); 
   p0:=mod(p,m1); 
   q1:=trunc(q/m1); 
   q0:=mod(q,m1); 

   return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m)); end; /* mult */  
/*-------------------------- rndint --------------------------*/ 
/* Returns random integer between [0, r-1] */  

function rndint (r in number) return number is begin

   /* generate a random number and set it to be the new seed */    a:=mod(mult(a,b)+1,m);  

   /* convert it to integer between [0, r-1] and return it */    return(trunc((trunc(a/m1)*r)/m1));
end; /* rndint */  

/*-------------------------- rndflt --------------------------*/ 
/* Returns random real between [0, 1] */  

function rndflt return number is
begin

   /* generate a random number and set it to be the new seed */    a:=mod(mult(a,b)+1,m);  

   /* return it */
   return(a/m);
end; /* rndflt */  

begin /* package body random */

   /* Generate an initial seed "a" based on system date */

   /* (Must be connected to database.)                  */ 
   the_date:=sysdate;
   days:=to_number(to_char(the_date, 'J'));    secs:=to_number(to_char(the_date, 'SSSSS'));    a:=days*24*3600+secs;
end; /* package body random */
/
show errors Received on Wed Aug 20 1997 - 00:00:00 CEST

Original text of this message