Oracle DBMS_RANDOM package details

DBMS_RANDOM is a PLSQL package provided by Oracle to generate random data and characters. The initialize, random, and terminate functions are not recommended in Oracle11g.

DBMS_RANDOM is a PL/SQL package provided by Oracle to generate random data and characters. The initialize, random, and terminate functions are not recommended in Oracle11g.

DBMS_RANDOM is a PL/SQL package provided by Oracle to generate random data and characters. It has the following functions.

Among them, the initialize, random, and terminate functions are not recommended in Oracle11g and are mainly used for backward compatibility. The following is an example of each function.

1. INITIALIZE

Use a seed value to initialize the DBMS_RANDOM package.

By default, the DBMS_RANDOM package is initialized based on the user, time, and session. In this way, the values generated each time are different for the same statement, but this produces a problem, in the test environment, what should I do if I want to generate the same random sequence each time? The INITIALIZE function solves this problem well. By setting the same seed value, the random sequence generated each time will be the same.

Syntax:

DBMS_RANDOM.INITIALIZE (
Val IN BINARY_INTEGER );

Example:

SQL> BEGIN
Dbms_random.initialize (100 );
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (dbms_random.random );
End loop;
END;
/
751599369
1131809137
-865013504
-407075626
-448154892
-1371178596

PL/SQL procedure successfully completed.

Even in different sessions and users, the 10 values randomly generated are the same.

2. NORMAL

The NORMAL function returns a group of numbers that follow the NORMAL distribution. The standard deviation of this normal distribution is 1, and the expected value is 0. 68% of the values returned by this function are between-1 and + 1, 95% between-2 and + 2, and 99% between-3 and + 3.

Syntax:

DBMS_RANDOM.NORMAL
Return number;

Example:

SQL> select dbms_random.normal from dual;

NORMAL
----------
. 321082788

3. RANDOM

The range of returned values of RANDOM is [-2 ^ 31, 2 ^ 31). The returned values are integers.

Syntax:

DBMS_RANDOM.RANDOM
RETURN binary_integer;

Example:

SQL> select dbms_random.random from dual;

RANDOM
----------
-1.363E + 09

4. SEED

The function is similar to the INITIALIZE function. In fact, the INITIALIZE function is eliminated, and the recommended alternative function is the SEED function. Unlike the INITIALIZE function, the SEED function supports both numerical values and characters as the SEED values, while the INITIALIZE function only supports numerical values.

Syntax:

DBMS_RANDOM.SEED (
Val IN BINARY_INTEGER );

DBMS_RANDOM.SEED (
Val IN VARCHAR2 );

Example:

BEGIN
Dbms_random.seed ('hello ');
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (round (dbms_random.value * 100 ));
End loop;
END;

The output is as follows:

58
71
33
4
39
53
93
37
20
5

The maximum range of VARCHAR2 is 2000.

5. STRING

Randomly generated string

Syntax:

DBMS_RANDOM.STRING
Opt in char,
Len in number)
RETURN VARCHAR2;

The description of opt and len is as follows:

It can be seen that opt refers to the string format, and len refers to the length of the string.

Example:

SQL> select dbms_random.string ('U', 10) value from dual;

VALUE
--------------------
MCPEZLEQOO

SQL> select dbms_random.string ('l', 10) value from dual;

VALUE
--------------------
Laufaqufln

SQL> select dbms_random.string ('A', 10) value from dual;

VALUE
--------------------
VjEetXlItt

SQL> select dbms_random.string ('x', 10) value from dual;

VALUE
--------------------
LAMDGZE22E

SQL> select dbms_random.string ('P', 10) value from dual;

VALUE
--------------------
4LF = Q' (fP

6. TERMINATE

Use this function to terminate the DBMS_RANDOM package. This function is not recommended in 11gR1.

Syntax:

DBMS_RANDOM.TERMINATE;

Example:

SQL> exec DBMS_RANDOM.TERMINATE;

PL/SQL procedure successfully completed.

7. VALUE

Syntax:

DBMS_RANDOM.VALUE
Return number;

DBMS_RANDOM.VALUE (
Low in number,
High in number)
Return number;

For the first usage, the returned value range is greater than or equal to 0, less than 1, with 38 decimal places of precision.

For the second usage, you can specify the minimum and maximum values. The returned values are greater than or equal to low and less than high.

Example:

SQL> select dbms_random.value from dual;

VALUE
----------
. 291782963

SQL> select dbms_random.value (10, 20) from dual;

DBMS_RANDOM.VALUE (10, 20)
------------------------
12.4079412

Summary:

You can verify that the VALUE function returns 38 decimal places.

SQL> select dbms_random.value from dual;

VALUE
----------
. 511020102

SQL & gt; col value for 999999.9999999999999999999999999999999999999999999999999
SQL> select dbms_random.value from dual;

VALUE
---------------------------------------------------------
. 1590863051775181450023750363985770254400000000000

SQL>/

VALUE
---------------------------------------------------------
. 5831363280913832608492096535119024112700000000000