An PL/SQL Random number generator

The code below is a random number generator for Oracle. I needed it in a project to select samples from a table.

To compile it:
Copy the code below in a file, and send it to SQL*PLUS.

The program has been tested with Oracle 7.1 and Oracle 7.3 I did not initialize the random number generator in a some what random way, this makes the sequence of the random number generator each time the same. If you want a random start, use the session id and system time, and some oracle statistics like the number of blocks read etc, as initialization. In Oracle 8.0 a random number generator is provided by Oracle in the packages the standard packages (check!).

create or replace package random as
/* 
Program by Willem Dekker (c) 1998 
based on :  
Stephen K. Park and Keith W. Miller. RANDOM NUMBER GENERATORS: 
GOOD ONES ARE HARD TO FIND. Communications of the ACM, 
New York, NY.,October 1988 p.1192 
The following is a PL/SQL program for generating random numbers. 
The modulus and multiplier have been extensively tested and should 
not be changed except by someone who is a professional Lehmer generator 
writer.  THIS GENERATOR REPRESENTS THE MINIMUM STANDARD AGAINST WHICH 
OTHER GENERATORS SHOULD BE JUDGED. ("Quote from the referenced article's 
authors. " )
*/   
pragma restrict_references(random,wnds,rnds); 
/* Initialize the random number generator with a number from 0 to 2147483647 */ 
procedure srand(s in number); 
pragma restrict_references(srand ,wnds,rnds);
 
/* Gives integer random numbers from 0 to 2147483647 */ 
function rand return number; 
pragma restrict_references (rand ,wnds,rnds);
 
/* Gives integer random numbers from 0 to rmax */  
function mrand(rmax in number) return number; 
pragma restrict_references (mrand ,wnds,rnds); 
/* Test procedure for the random number generator use sql*plus, set serveroutput on,     
execute random.test_random */  
procedure test_random; 
end;
/
create or replace package body random as
seed binary_integer;
m  number := 2147483647;
q  number := 127773;
a  number := 16807;
r  number := 2836;

procedure srand(s in number) is
begin   
seed := s;
end;
function rand return number is 
hi number; 
lo number; 
test number;
begin    
hi   := trunc(seed/q);   
lo   := mod(seed,q);   
	
test := a*lo - r*hi;
	
if (test > 0) then	
seed := test;    
else	
seed := test+ m;    
end if;  
return seed;
end;
function mrand(rmax in number) return number is
m_pow number := 1;
r number;
begin   
if rmax > 0 then       
r:= 0;      
while (rmax > m_pow/100) loop         
r:= random.rand * m_pow + r;         
m_pow := m_pow*m;      
end loop;      
return mod(r,rmax);   
end if;   
return -1;
end;
procedure test_random is
successfulltest number := 1043618065;
success number;
i number ;
n_rand number; 
begin 
srand(1); 
for  i in  1 .. 10001 loop        
n_rand := rand;        
if( i> 9998) then	    
dbms_output.put_line('Sequence'||i||'Seed='||seed );         
end if;	
if( i = 10000) then  	    
if ( seed = successfulltest )  then		
success := 1;            
end if;        
end if;    
end loop; 
	 
if (success = 1) then	
dbms_output.put_line('The random number generator works correctly.');    
else 	
dbms_output.put_line('The random number generator DOES NOT WORK!');    
end if;

end;
begin   
seed := 1;
end;
/

		