Re: RANDOM NUMBER GENERATOR

From: Paul Russell <prussel6_at_removethis.ford.com>
Date: Thu, 9 Sep 1999 10:41:06 +0100
Message-ID: <7r7va7$d8k3@eccws1.dearborn.ford.com>

Random number generator, courtesy of Steven Fuerstien:

rem Package RND: Generate random numbers rem
rem This package should be installed as SYS. It generates a sequence of rem random 38-digit Oracle numbers. The expected length of the sequence rem is about power(10,28), which is hopefully long enough. The generator rem used is called Delayed Fibonacci: m(i) := result = m(i)+m(i+24 mod 55).
rem
rem Numbers can be fetched through SQL statements, for example rem

rem      insert into a values (rnd.val);
rem      select rnd.val from dual;
rem      variable x number;
rem      execute :x := rnd.val;
rem      update a set a2=a2+1 where a1 < :x;

rem
rem When no seed is provided, the package is originally initialized rem with the current user name and the time down to the current second. rem If this package is seeded twice with the same seed, then accessed rem in the same way, it will produce the same results in both cases.

drop public synonym rnd
/
drop package rnd
/

create package rnd is

• initialize the generator using a user-supplied string as the seed procedure init (seed in varchar2); pragma restrict_references (init, WNDS);
• get a random Oracle number x, 0.0 <= x < 1.0 function val return number; pragma restrict_references (val, WNDS);
• get a random Oracle number x, LOW <= x < HIGH function val (low in number, high in number) return number; pragma restrict_references (val, WNDS);
• get a random number from a normal distribution function norm return number; pragma restrict_references (norm, WNDS);
• produce a random string function STRING (opt char, len number) /* "opt" specifies that the returned string may contain: 'u','U' : upper case alpha characters only 'l','L' : lower case alpha characters only 'a','A' : alpha characters only (mixed case) 'x','X' : any alpha-numeric characters (upper) 'p','P' : any printable characters */ return varchar2; -- string of <len> characters (max 60) pragma restrict_references (STRING, WNDS);

  type num_array is table of number index by binary_integer; end;
/

create package body rnd is
  mem num_array; -- big internal state hidden from the user

  counter    binary_integer;       -- counter through the results
  other      binary_integer;
  saved_norm number;               -- unused random normally distributed

value

• initialize the generator with any string procedure init (seed in varchar2) is junk varchar2(2000); piece varchar2(20); randval number; j binary_integer; begin saved_norm := null; counter := 0; other := 24; junk := to_single_byte(seed); for i in 0..54 loop piece := substr(junk,1,19); randval := 0; j := 1; for j in 1..19 loop randval := 1e2*randval + nvl(ascii(substr(piece,j,1)),0.0); end loop;

      randval := randval*1e-38 + i*.01020304050607080910111213141516171819;
      mem(i)  := mod( randval, 1.0 );
      junk    := substr(junk,20);

    end loop;

    for j in 0..10 loop

      for i in 0..54 loop
        randval := mem(mod(i+55-1, 55)) * 1e24;
        randval := mod( randval, 1.0) + trunc(randval)*1e-38;
        randval := mem(i)+randval;
 if (randval >= 1.0) then
          randval := randval - 1.0;
        end if;
 mem(i) := randval;
      end loop;

    end loop;
  end init;

• give values to the user function val return number is randval number; begin counter := counter + 1; if counter >= 55 then counter := 0; for i in 0..30 loop randval := mem(i+24) + mem(i); if (randval >= 1.0) then randval := randval - 1.0; end if; mem(i) := randval; end loop; for i in 31..54 loop randval := mem(i-31) + mem(i); if (randval >= 1.0) then randval := randval - 1.0; end if; mem(i) := randval; end loop; end if; return mem(counter); end val;
• give values in a certain range function val ( low in number, high in number) return number is begin return (val*(high-low))+low; end val;
• Random numbers in a normal distribution.
• Pilfered from Knuth volume 2. function norm return number is -- 38 decimal places: Mean 0, Variance 1 v1 number; v2 number; r2 number; fac number; begin if saved_norm is not null then -- saved from last time v1 := saved_norm; -- to be returned this time saved_norm := null; else r2 := 2; while r2 > 1 or r2 = 0 loop -- Find two independent uniform variables v1 := 2*val-1; v2 := 2*val-1; r2 := v1*v1 + v2*v2; -- r2 is radius, that is, distance from 0 end loop; -- 0 < r2 <= 1: in unit circle /* Now derive two independent normally-distributed variables */ fac := sqrt(-2*ln(r2)/r2); v1 := v1*fac; -- to be returned this time saved_norm := v2*fac; -- to be saved for next time end if; return v1; end norm;

  function STRING (opt char, len number)     return varchar2 is -- string of <len> characters     optx char (1) := lower(opt);

    lo   number;
    rng  number;
    n    number;

    xstr varchar2 (60) := null;
  begin

    if    optx = 'u' then -- upper case alpha characters only
      lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
    elsif optx = 'l' then -- lower case alpha characters only
      lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
    elsif optx = 'a' then -- alpha characters only (mixed case)
      lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
    elsif optx = 'x' then -- any alpha-numeric characters (upper)
      lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
    elsif optx = 'p' then -- any printable characters
      lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
    else
      lo := 65; rng := 26;   -- default to upper case

    end if;

    for i in 1 .. least(len,60)
    loop

      /* Get random ASCII character value in specified range */
      n := lo + trunc(rng * val); -- between lo and (lo + rng -1)

      /* Adjust for split range */
      if    optx = 'A' and n > 90 then
        n := n+6; -- exclude ASCII characters 5B to 60
      elsif optx = 'X' and n > 57 then
        n := n+7; -- exclude ASCII characters 3A to 40
      end if;

      xstr := xstr||chr(n); -- Append character to string

    end loop;

    return xstr;

  end STRING;

begin
  init(to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')||user); end;
/

create public synonym rnd for rnd
/
grant execute on rnd to public
/

Xiaofei Zhang <xz_at_liveoaktelecom.com> wrote in message news:37D6D2BE.6C04B2CB_at_liveoaktelecom.com...

> hey yall.
> i am trynna write a random number generator, can i have some samples?
> also, how do i display a line of text after i run it.(in SQL> )?
> that is all, thanks for ur time
>
> --
> Xiaofei Zhang

Received on Thu Sep 09 1999 - 04:41:06 CDT