Re: Generate random numbers in PL/SQL, any good ways? Is there an internal?

In article <d7hansl.774016863_at_dtek.chalmers.se>, d7hansl_at_dtek.chalmers.se (Hans Lindberg) wrote:

> Hi!
> I'm looking for a way to generate test data with certain probabilities
> for the different values of the data. I can't find any random number
> generator in neither the SQL ref man nor the PL/SQL ditto. Am I missing
> the obvious or isn't there any? Do I have to implement a my own random
> function? In that case, anyone know of a decent one? Nothing super, just
> so it looks fairly randomuous(?). I remember something called 147-something
> but i can't remember how it went.
>
> Hans Lindberg Tel: 031-183151 EMail d7hansl_at_dtek.chalmers.se
> Gullspaangs Kraft AB
> ---------------------------------------------------------------------------
> / / / "-Fashion is a form of ugliness so intolerable
> / / / that we have to alter it every six months."
> /------/ /
> / / / --Oscar Wilde
> / / /.....
> ---------------------------------------------------------------------------
Here something that was posted a few months ago. I haven't used it myself but it looks like it should do the job.

-- 
Dave Erwin
Lockheed Missiles & Space Company
erwin_at_lmsc.lockheed.com
======= 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 */