Re: SQL*Plus random function

From: Russ Seligman <rseligma_at_oracle.com>
Date: 03 Feb 1995 01:35:28 GMT
Message-ID: <RSELIGMA.95Feb2173528_at_seinfeld.oracle.com>

In article <3gosvj$mas_at_newsbf02.news.aol.com> davidjroth_at_aol.com (DavidJRoth) writes:
>>Does anyone know of a good pseudo random number function which can
>>be constructed solely of existing SQL*Plus arithmetic functions?
>...
>In the interim you can write your own function as a PL/SQL function.
>In Oracle 7.1 and hire you can reference your own functions anywhere you
>can reference Oracle built in functions.

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 03 1995 - 02:35:28 CET