Re: SQL to Generate Random Numbers?
Date: Sun, 13 Mar 1994 23:23:56 GMT
Message-ID: <RSELIGMA.94Mar13152357_at_seinfeld.oracle.com>
In article <CMHA06.Cnu_at_freenet.carleton.ca> ah513_at_FreeNet.Carleton.CA (Doug Harris) writes:
>>In a previous article, am907_at_FreeNet.Carleton.CA (David Laderoute) says:
>>
>>... Is there, for example, a "random" number generator available
>>in SQL, which will allow the extraction of random records from the
>>database? Any help would be much appreciated--thanks.
>
> Although not possible in straight SQL, it should be possible to
>write a random number function in PL/SQL. If anyone has written such
>a thing I would be VERY interested in getting it.
Here is a PL/SQL implementation of the linear congruential method of generating random numbers. It's in the form of a PL/SQL package, so it should be easy to hook up to existing applications.
- 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 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 */
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Russ Seligman "If there's nothing wrong with me, maybe Oracle Corporation there's something wrong with the universe." rseligma_at_oracle.com --Beverly Crusher, ST:TNG-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- Received on Mon Mar 14 1994 - 00:23:56 CET