Re: PL/SQL and Random Number Generation????

On Mon, 16 Dec 1996 08:45:34 -1000, Michael Cretan <mcretan_at_ozemail.com.au> wrote:

>Does anybody out there have a cute way of generating
>random numbers in PL/SQL ?
>
>Thanks in advance,
>
>
>Mike
>
>---
>Mike Cretan Phone Australia + (04) 11 22 6410
>Oracle Consultant Fax Australia + (02) 62 34 2988

I won't attest to it's "cute-ness" but it works.....

You can use it in sql as well. we use it to randomly select rows from a relatively small (couple hundred) row table, eg:

select random.rand, a.*
  from T a
 order by 1
/

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
Oracle Government
tkyte_at_us.oracle.com
                         
http://govt.us.oracle.com   

---- Check out Oracle Governments web site!  -----
     Follow the link to "Tech Center"
     and then downloadable Utilities for some free software...


-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation