Oracle's Dbms_random Package

Dbms_random is a PL/SQL package provided by Oracle for generating random data and characters. It has the following functions.

Of these, the Initialize,random,terminate function is deprecated in oracle11g and is mainly used for backwards compatibility. Examples of each function are shown below

1. INITIALIZE

Initializes the Dbms_random package with a seed value.

By default, the Dbms_random package is initialized based on the user, time, and session, so that even the same statement generates a different value each time, but this creates a problem in the test environment, what if I want to generate random sequences every time? The Initialize function solves this problem very well by setting the same seed value, and each random sequence generated will be the same.

Grammar:

Dbms_random. INITIALIZE (
Val in Binary_integer);

Example:

Sql> BEGIN  2Dbms_random.initialize ( -); 3     forIinch 1..TenLOOP4Dbms_output.put_line (dbms_random.random); 5    ENDLOOP; 6  END; 7  /1632847797515993696598044751131809137-865013504-4070756262128226600-448154892-1371178596472933400PL/SqlprocedureSuccessfully completed.

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

2. NORMAL

The normal function returns a set of numbers that obey a normal distribution. This normal distribution has a standard deviation of 1 and a expected value of 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.

Grammar:

Dbms_random. NORMAL
RETURN number;

Example:

SQL>Select from dual;    NORMAL----------. 321082788

3. RANDOM

The random return value is in the range: [ -2^31, 2^31) and returns an integer.

Grammar:

Dbms_random. RANDOM
RETURN Binary_integer;

Example:

SQL>Select from dual;    RANDOM-----------1. 363E+

4. SEED

Functions are similar to initialize functions, 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 numeric and character as seed values, while the Initialize function supports only numeric values.

Grammar:

Dbms_random. SEED (
Val in Binary_integer);

Dbms_random. SEED (
Val in VARCHAR2);

Example:

BEGIN    dbms_random.seed ('hello');     for inch 1 Ten LOOP      dbms_output.put_line (round*));    END LOOP; END;

The output is as follows:

 -  in  - 4  the  -  the Panax Notoginseng  - 5

Among them, the maximum range of VARCHAR2 is 2000.

5. STRING

Randomly generated string

Grammar:

Dbms_random. STRING
Opt in CHAR,
Len in number)
RETURN VARCHAR2;

A description of opt and Len is explained below:

As can be seen, opt refers to the format of a string, Len refers to the length of the string.

Example:

Sql> SelectDbms_random.string ('u',Ten) value fromdual; VALUE--------------------Mcpezleqoosql> SelectDbms_random.string ('L',Ten) value fromdual; VALUE--------------------Laufaquflnsql> SelectDbms_random.string ('a',Ten) value fromdual; VALUE--------------------Vjeetxlittsql> SelectDbms_random.string ('x',Ten) value fromdual; VALUE--------------------Lamdgze22esql> SelectDbms_random.string ('P',Ten) value fromdual; VALUE--------------------4LF=Q'(FP

6. TERMINATE

After the Dbms_random package is used, it is terminated with the function. This function is not recommended for use in 11GR1.

Grammar:

Dbms_random. TERMINATE;

Example:

SQL>exec  dbms_random. TERMINATE;PL/procedure successfully completed.

7. VALUE

Grammar:

Dbms_random. VALUE
RETURN number;

Dbms_random. VALUE (
Low in number,
High in number)
RETURN number;

For the first usage, the returned value has a range greater than or equal to 0, less than 1, and a decimal with 38-bit precision.

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

Example:

Sql> SelectDbms_random.value fromdual; VALUE----------.291782963SQL> SelectDbms_random.value (Ten, -) fromDual;dbms_random. VALUE (Ten, -)------------------------          12.4079412

Summarize:

The value function returns a decimal number of 38 bits of precision, which can be verified in the following way.

Sql> SelectDbms_random.value fromdual; VALUE----------.511020102SQL>Col value for 999999.9999999999999999999999999999999999999999999999999SQL> SelectDbms_random.value fromdual; VALUE---------------------------------------------------------.1590863051775181450023750363985770254400000000000SQL> /VALUE---------------------------------------------------------.5831363280913832608492096535119024112700000000000

Deliberately set the Value column format to 999999.9999999999999999999999999999999999999999999999999, of course, as long as the number of decimal digits more than 38 bits, in this case, 49 bits, by Dbms_ Random.value random return values, it is not difficult to find that the last generated value, although 49 bits, but the last 11 bits are 0, in other words, the valid value is only 38 bits.

Reference:

1. http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_random.htm#ARPLS040

2. http://zhangzhongjie.iteye.com/blog/1948930

Oracle's Dbms_random Package