Xref: alice comp.databases.oracle.server:39518
Path: alice!news-feed.fnsi.net!netnews.com!newspeer1.nac.net!logbridge.uoregon.edu!nntp.teleport.com!news1.teleport.com!not-for-mail
Message-ID: <36B6BABB.120D@teleport.com>
From: GHouck <hksys@teleport.com>
Organization: systems hk
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Random No
References: <796ao0$1v92@hkusud.hku.hk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 94
Date: Tue, 02 Feb 1999 00:43:39 -0800
X-Complaints-To: news@teleport.com
X-Trace: news1.teleport.com 917945034 216.26.11.14 (Tue, 02 Feb 1999 00:43:54 PDT)
NNTP-Posting-Date: Tue, 02 Feb 1999 00:43:54 PDT

Ha Kong Kuen wrote:
> 
> Hi,
> 
> Is there a quick and easy way of generating a random no
> within pl/sql?

Someone (sorry, I lost the author) posted this package a while ago
in one of the Oracle newsgroups:

The package..


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

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


Hope that helps,

Geoff Houck
systems hk
hksys@teleport.com
http://www.teleport.com/~hksys