Re: Random Number generator?

From: Russ Seligman <rseligma_at_oracle.com>
Date: 17 Feb 1995 20:03:31 GMT
Message-ID: <RSELIGMA.95Feb17120331_at_seinfeld.oracle.com>


In article <3htmbm$dql_at_uwm.edu> rao_at_ee.uwm.edu (Praveen Rao) writes: > Is there a place where I can find a random number generator in plsql. > I want to be able to call a plsql function where returns a random number.

At the end of this note is a PL/SQL package that implements a linear congruential random number generator. You can call it using the following syntax:

  x := random.rndint(10); -- returns an integer between 0 and 9 inclusive

  x := random.rndflt; -- returns floating point between 0 and 1 inclusive  

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Russ Seligman                 "If there's nothing wrong with me, maybe
Oracle Corporation             there's something wrong with the universe."
rseligma_at_us.oracle.com           --Beverly Crusher, ST:TNG
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-  
  • the package spec: **********

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: **********

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 */   Received on Fri Feb 17 1995 - 21:03:31 CET

Original text of this message