Re: Random selection of records in PL/SQL

From: Brian Ewins <Brian.Ewins_at_gssec.bt.co.uk>
Date: 1997/06/11
Message-ID: <339E95B6.5A9A@gssec.bt.co.uk>

doid wrote:

> 
> HShukla538 wrote:
> >
> > How can i select a true random record in pl/sql
> 

I've done this in the past using code like:

foo:=random.next_int(range)+offset;
select * into rec
from bar
where key>foo
and rownum=1;

(ie I have a numeric key in the table, I generate a random number based on the offset and range of the values of this key (offset=min(key), range=max(key)-min(key)).)

The random number generator I used varied; in one case I was doing this in perl, so I used its random number generator to help build the sql; in other cases I've used a random number generator I converted from one in Numerical Recipes in C (Oxford University Press). The generator was a linear congruential generator with shuffling and had a pretty long period, so was random enough for almost any purpose.

The code is included below - use it at your own risk. It's not as efficient as it could be, but I didn't want to spend much time on it, other than to get a working conversion.

	Hope this helps.
		Brian

=========================================BEGIN

set verify off
set feedback off

• Random number generator, adapted from 'Numerical Recipes in C',
• second edition, p 282. A generator due to L'Ecuyer with Bays-Durham
• shuffle... ie, a simple 'linear congruential' generator, with a
• table of the last 32 generated numbers, from which one is chosen to
• be handed out and replaced with the latest number, using a second generator,
• so that the combined period is very long (> 2 x 10^18).
• There are 4 public functions:
• seed(x) seeds the random sequence with x.
• since the sequence only has good random properties if we let it go,
• this should be called sparingly; the code has been expicitly written
• to allow the random number tables to persist.
• seed on its own (no arguments) seeds the generator using the current time.
• this is used at the end of this file to seed the generator for the first time
• next_num : no parameters. returns a number in the range 0..1 excluding
• endpoints. The random numbers have a uniform deviate and are statistical ly
• pretty good at being random...
• next_int(x): given a maximum value x, return integers in the range 0..x,
• including 0 but not including x.
• next_string(x): returns a string x chars long, consisting of the letters
• a-z. This is intended for use in creating 'cookie' type session persiste nce
• for the web server.
• static store required to make data persist: drop table random_persist; create table random_persist (id varchar2(10),value number) tablespace tsdata storage (initial 10k next 10k pctincrease 0);

create or replace package random is

• constants
• first linear congruential generator IM1 constant number := 2147483563; IA1 constant number := 40014; IQ1 constant number := 53668; IR1 constant number := 12211;
• syntactic sugar... IMM1 constant number := IM1-1; AM constant number := 1.0/IM1;
• second generator, used for shuffling IM2 constant number := 2147483399; IA2 constant number := 40692; IQ2 constant number := 52774; IR2 constant number := 3791;
• size of shuffle table NTAB constant number := 32; NDIV constant number := (1+IMM1/NTAB);
• precision (32 bit), maximum number. EPS constant number := 1.2e-7; RNMX constant number := 1.0-EPS;
• PUBLIC INTERFACE procedure seed(seed_val in number default null); function next_num return number; function next_int(max_int integer) return integer; function next_string(chars in integer) return varchar2;

        --PRIVATE UTILITIES
        function get_persistent(id varchar2) return number;
        procedure set_persistent(id varchar2, value number);

end random;
/
show errors;
create or replace package body random is

• seed function. this should only be used for test purposes;
• the call at the end of this file does the initialisation
• normally. Note that the generator is intended to be static
• across database _instances_ so that we don't try to generate
• the same numbers several times in succession. procedure seed(seed_val in number default null) is idum1 number; k number; j integer; begin if seed_val is null then --set seed from current time. idum1:=to_number(to_char(sysdate,'SSSS')); else idum1:=floor(seed_val); end if; if idum1<1 then idum1:=1; end if; set_persistent('idum2',idum1); for j in reverse 0..(NTAB+7) loop k:=floor(idum1/IQ1); idum1:=IA1*(idum1-k*IQ1)-k*IR1; if idum1<0 then idum1:=idum1+IM1; end if;

                        if j<NTAB then 
                                set_persistent(to_char(j),idum1);
                        end if;
                end loop;
                set_persistent('iy',get_persistent('0'));
                set_persistent('idum1',idum1);
        end seed;

        function next_num return number is
                idum1 number;
                idum2 number;
                iy number;
                temp number;
                k number;
                j integer;
        begin
                -- restore the persisted values
                idum1:=get_persistent('idum1');
                idum2:=get_persistent('idum2');
                iy:=get_persistent('iy');

                --generator1 begins here.
                --uses Schrager's method for calculating
                --mod without overflow. Not really necessary
                --here; number's are much bigger than 32 bit.
                k:=floor(idum1/IQ1);
                idum1:=IA1*(idum1-k*IQ1)-k*IR1;
                if idum1<0 then 
                        idum1:=idum1+IM1;
                end if;

                -- second generator.
                k:=floor(idum2/IQ2);
                idum2:=IA2*(idum2-k*IQ2)-k*IR2;
                if idum2<0 then 
                        idum2:=idum2+IM2;
                end if;

                -- shuffle/combine generators
                j:=floor(iy/NDIV);
                iy:=get_persistent(to_char(j))-idum2;
                if (iy<1) then 
                        iy:=iy+IMM1;
                end if;

                -- before returning, set the new persistents...
                set_persistent(to_char(j),idum1);
                set_persistent('idum1',idum1);
                set_persistent('idum2',idum2);
                set_persistent('iy',iy);

                --prevent endpoints being returned
                temp := AM*iy;
                if temp>RNMX then 
                        temp:=RNMX;
                end if;

                return temp;
        end next_num;



        -- maintain persistent storage

        function get_persistent(id varchar2) return number is
        retval number;
        begin
                select value into retval
                from random_persist
                where id=get_persistent.id;
                return retval;
        end get_persistent;

        procedure set_persistent(id varchar2,value number) is
        small_string random_persist.id%type;
        begin
                small_string:=substr(id,1,length(small_string));

                insert into random_persist
                (id,value)
                select 
                small_string, set_persistent.value
                from dual where not exists
                (
                        select null
                        from random_persist
                        where id=small_string
                );

                update random_persist
                set value=set_persistent.value
                where id=set_persistent.id;

        end set_persistent;



        -- convenience function to return integers up to a specified

range.
        function next_int(max_int integer) return integer is
        begin
                return floor(next_num*max_int);
        end next_int;



        -- return the random number as a base 26 number, with digits

        -- as letters in the range a-z. This prevents the user seeing

        -- both the internal representation and the full precision of

the


        -- number, to increase security. 32-bit randoms will return

        -- trailing 'AAA...' strings for more than about 10 chars.

        function next_string(chars in integer) return varchar2 is
        next_letter integer;
        i integer;
        temp number;
        retval varchar2(32);
        begin
                retval:='';
                temp:=next_num;
                for i in 1 .. chars
                loop
                        next_letter:=floor(temp*26);
                        temp:=(temp*26)-next_letter;
                       
retval:=concat(retval,CHR(next_letter+ASCII('a')));
                end loop;
                return retval;
        end next_string;

end random;
/
show errors;

• seed random number from the time. exec random.seed ===================================END

--
****====----                                              Brian Ewins. 
Fax: (44) 141 220 6100                          Tel: (44) 141 220 6121
 "It's time we face reality, my friends...
      We're not exactly rocket scientists." --Gary Larson ----====****

Received on Wed Jun 11 1997 - 00:00:00 CDT