ORACLE random number DBMS_RANDOM package, oracledbms_random

ORACLE random number DBMS_RANDOM package, oracledbms_random

To put it simply, there are four methods to call random numbers through the dbms_random package:

1. dbms_random.normal

This function can return a number type of normal distribution without parameters. Therefore, the random number ranges from-1 to 1.
After a simple test, the maximum number of 100000 requests is 5:

SQL code

 declare   i number:=;    j number:=;   begin   for k in .. loop    i:= dbms_random.normal;     if i > j      then j:=i;     end if;    end loop;    dbms_output.put_line(j);   end; 

5.15325081797418404136433867107468983182

PL/SQL procedure successfully completed

2. dbms_random.random

This parameter does not exist. An integer from-power () to power () is returned.

3. dbms_random.value

This function can be divided into two types. If there is no parameter, the 38 decimal places between> = 0 and <1 are directly returned.

  SQL > column value format 9.99999999999999999999999999999999999999  SQL > select dbms_random.value from dual;                    VALUE  -----------------------------------------   .58983014999643548701631750396301271752

If two parameters a and B are added, the return value is 38 decimal places between> = a and <B.

  SQL > column value format 999.999999999999999999999999999999999999  SQL > select dbms_random.value(100,500) value from dual;                    VALUE  -----------------------------------------   412.150194612502916808701157054098274240

Note: No matter the first digit, the decimal point is 38 digits.

You can use the trunc () function to get the integer.

4. dbms_random.string

This function must contain two parameters. The previous character specifies the type, and the subsequent value specifies the number of digits (up to 60)

Type description:

  '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  SQL > column value format a30  SQL > select dbms_random.string('u',30) value from dual;  VALUE  ------------------------------  VTQNLGISELPXEDBXKUZLXKBAJMUTIA  SQL > select dbms_random.string('l',30) value from dual;  VALUE  ------------------------------  uqygsbquingfqdytpgjvdoblxeglgu  SQL > select dbms_random.string('a',30) value from dual;  VALUE  ------------------------------  NGTGkQypuSWhBfcrHiOlQwOUXkqJjy  SQL > select dbms_random.string('x',30) value from dual;  VALUE  ------------------------------  UVWONYJMXT31VEFPD736WJCJ5QT6BD  SQL > select dbms_random.string('p',30) value from dual;  VALUE  ------------------------------  :mak$(WT4M_7c/+f[_XUscf$P Zcq{

5. About seed

You can set seed to determine the starting point of the random number. For the same seed, any change of the random number will be determined.

That is to say, if seed is called at a certain time point, and then the first random number is 4, the second is 6, and the third is 1, after you call the same seed again, the random number generated at a time is 4, 6, and 1.

There are two types of seed: numeric type and linear type (Maximum length of 2000 ).

  -- Seed with a binary integer  PROCEDURE seed(val IN BINARY_INTEGER );  PRAGMA restrict_references (seed, WNDS );  -- Seed with a string (up to length 2000)  PROCEDURE seed(val IN VARCHAR2 );  PRAGMA restrict_references (seed, WNDS );

6. About initialize

An integer parameter, which is clearly described in the comment:

 -- Obsolete, just calls seed(val)  PROCEDURE initialize(val IN BINARY_INTEGER );  PRAGMA restrict_references (initialize, WNDS );

This article introduces the ORACLE random number DBMS_RANDOM package, so far, I hope it will help you learn oracle random number.