Re: Special function

From: Steve Davis <spd_at_patrol.i-way.co.uk>
Date: Thu, 25 Jun 1998 22:41:05 GMT
Message-ID: <3592d2a6.0@news.i-way.co.uk>

"Juan Palacios" <jpalacios_at_pananet.com> wrote:

>Does somebody know how to generate random numbers using Developer 2000 and
>PL/SQL?

>I need to access random the records of a table of questions to generate
>exams?

>Thanks

>Juan Palacios
>jpalacios_at_pan-dac.org

Juan,
There is no way to produce truly random numbers on a computer. However, you can use the Linear Congruential Method to produce pseudo-random numbers:

If a[1] (the initial seed) contains a number, a sequence of "random" numbers can be defined by
a[i] := ( a[i-1] * b + 1) mod m

NOTE: This is NOT PL/SQL

In the example below,

r is a "random" number between 0 and m-1

The initial seed for the random number generation is taken from sysdate - if this was run at exactly the same time evry day, the numbers generated would be the same each time.

Before being printed, r is converted to a number between 1 and x (in this case, 25)

r is used as the seed to produce the next value of r

SQL> set serveroutput on
SQL> ;
  1 declare

  2    m number := 100000000;
  3    b number := 31415021;
  4    x number := 25;

  5 r number;
  6 begin
  7 r := to_number(to_char(sysdate, 'HHSSMM'));   8 for c in 1..10 loop

  9      r := mod(r * b + 1, m);
 10      dbms_output.put_line(trunc(r / m * x) + 1);

 11 end loop;
 12* end;

SQL> /
16
24
13
4
21
17
21
16
12
25

PL/SQL procedure successfully completed.

SQL> /
4
13
21
22
20
5
10
13
20
19

PL/SQL procedure successfully completed.

SQL> You might want to change the values m and b - different values will give better/worse randomness.
b should have 1 digit less than m, and should end with 021, 221, 421, 621 or 821 (don't ask me why - I don't know)

If you are generating several sets of numbers, try to use that last value of r as the initial seed for the next set, instead of using the time for each set.

Hope this helps,

Steve Received on Thu Jun 25 1998 - 17:41:05 CDT