Re: Looking for RANDOM number generator technique in PL/SQL

From: Stewart Burnett <stewart_at_burnettms.nospam.demon.co.uk>
Date: Thu, 25 Feb 1999 12:26:33 -0000
Message-ID: <7b3fhm$vcb$1@hagen.cloud9.co.uk>

The following was posted a few hours ago in comp.databases.oracle.server by Thomas Kyte

in oracle8 there is a dbms_random package. for before oracle8 there is this (it
returns the same stuff as rand() does in C):

create or replace package random
is
pragma restrict_references( random, WNDS, RNPS );

procedure srand( new_seed in number );

function rand return number;
pragma restrict_references( rand, WNDS );

procedure get_rand( r OUT number );

function rand_max( n IN number ) return number; pragma restrict_references( rand_max, WNDS);

procedure get_rand_max( r OUT number, n IN number );

end random;
/

create or replace package body random
is

multiplier constant number         := 22695477;
increment constant number        := 1;
"2^32" constant number := 2 ** 32;
"2^16" constant number         := 2 ** 16;
"0x7fff" constant number         := 32767;

Seed number := 1;
--
procedure srand( new_seed in number )
is
begin
Seed := new_seed;
end srand;
--
function rand return number
is
begin
seed := mod( multiplier * seed + increment, "2^32" ); return bitand( seed/"2^16", "0x7fff" ); end rand;
--
procedure get_rand( r OUT number )
is
begin
r := rand;
end get_rand;
--
function rand_max( n IN number ) return number is
begin
return mod( rand, n ) + 1;
end rand_max;
--
procedure get_rand_max( r OUT number, n IN number ) is
begin
r := rand_max( n );
end get_rand_max;
--
begin
select userenv( 'SESSIONID' ) into seed from dual; end random;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Nicholas Wakefield <nicholas.wakefield_at_btinternet.com> wrote in message news:7b3eq9$bb8$1_at_mendelevium.btinternet.com...
>Anyone out there got a technique for generating random numbers in SQL or
>PL/SQL
>
>TIA
>
>
Received on Thu Feb 25 1999 - 06:26:33 CST