Re: Oracle function that generate random number

dschoen_at_lysis.ch (Dominique Schoenenberger) writes:

>Reply-To: dschoen_at_lysis.ch
>Distribution: worldwide
>
>In the oracle distribution, it doesn t exist any function that generate
>random numbers.
>I would like to define this function.
>This function would then allow me to retrieve a random row. Something who
>look like
>
>Select random_function (pass_in_variables) from table_name.
>
>Does anyone know or already programmed a plsql function who do this ?
>--
>Dominique
>dschoen_at_lysis.ch

the following plsql package works just like the random functions in C (rand, and srand). It returns the same series of numbers rand will in a C program. It can be used in a query:

        select random.rand from dual;

It can be used to generate data into a table:

	insert into foobar
	select random.rand from all_users;

The function srand (seed random) can be used to return the same series of random numbers.

The function rand_max will return a random number that falls in some range.

If you want to use this in a pre 7.1 database, remove the pragmas. They allow the functions in the package to be called from SQL statements (and hence was not supported in 7.0).

It makes use of the 'undocumented' bitand function.

It is as is, no warranty implied.....

create or replace package random
is

        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 );

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
	srand( userenv( 'SESSIONID' ) );
end random;
/