Sunday, May 08, 2011

DBMS_RANDOM (Part 1)

Get 40% Off WebWatcher software - Click Here

This is Oracle's built-in random number generator. It produces random numbers >= 0 and < 1. You can run a rudimentary test on it by calling it several times and checking the spread of the numbers returned:
          
SQL> create table dbms_random_test_tab
  2  (col1 number)
  3  /

Table created.

SQL> begin
  2  for a in 1..1000 loop
  3  insert into dbms_random_test_tab
  4  select floor(10*sys.dbms_random.value)+1 from dual;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select col1, count(*)
  2  from dbms_random_test_tab
  3  group by col1
  4  order by col1
  5  /

      COL1   COUNT(*)
---------- ----------
         1        103
         2         99
         3         80
         4        120
         5        104
         6         94
         7         87
         8        109
         9        111
        10         93

10 rows selected.

SQL>


If you run it again, a different set of numbers is returned:

SQL> delete dbms_random_test_tab
  2  /

1000 rows deleted.

SQL> begin
  2  for a in 1..1000 loop
  3  insert into dbms_random_test_tab
  4  select floor(10*sys.dbms_random.value)+1 from dual;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select col1, count(*)
  2  from dbms_random_test_tab
  3  group by col1
  4  order by col1
  5  /

      COL1   COUNT(*)
---------- ----------
         1         85
         2         90
         3        105
         4        113
         5         96
         6        103
         7        114
         8         99
         9        115
        10         80

10 rows selected.

SQL>

No comments: