Use Dbms_random to generate random numbers in Oracle

Some random numbers are often generated during testing, and for this reason, Oracle provides dbms_random
This package to produce random numbers is very useful. Under the summary below:

This package has several functions such as initialize (), seed (), terminate (), value (), Normal (), random (), string (), and so on.
1) Dbms_random.value
Without parameters, a numeric value with a 38-bit precision is returned, ranging from 0.0 to 1.0, excluding 1.0,
Like what:
For I in 1..10 loop
Dbms_output.put_line (Round (dbms_random.value*100));
End Loop;


If you have the upper and lower bounds, for example:
Dbms_random.value (1,101) generates a number between the lower bound and the upper bound, but does not contain the upper bound

2) Dbms_random.string method


Delect dbms_random.string (' P ', 4) from dual;
The first parameter has the following meanings
U ', ' u '-return uppercase
' L ', ' l '-return lowercase letters
' A ', ' a '-return uppercase and lowercase mixed letters
' x ', ' x '-uppercase and lowercase letters + numbers mixed
' P ', ' p '-
P represents printable, which is a string consisting of any printable character
The second parameter represents the length of the string returned.

3) Dbms_random.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.

4) Dbms_random.random method
Random returns a value of type Binary_integer that produces a random number of any size
Examples of differences with Dbms_random.value:
Order by Dbms_random.value;
This statement function is to implement the random ordering of records
Other than that:
Dbms_random.value and
Dbms_random.random What is the difference between the two?
1. ORDER BY Dbms_random.value calculates a random number for each row of the result set, Dbms_random.value is a column of the result set (although the column is not in the select list), and then sorted by that column, the order is naturally random.
2. Look at the DESC message and know the difference between the vlue and the random functions, value returns the number type, and the returned value is between 1 and 0, and the random return is the Binary_integer type

5) Generate random dates
Oracle takes the date as a key date in the past (if you're curious, I can tell you that this date is January 1, 4712 BC) and the integer offset to save. This means that you can randomly generate a date within a specified range by looking for an integer corresponding to the start date you want, and then adding a random integer to it.

Using the To_char function and the ' J ' format code, you can generate an internal date number for today's date:

SELECT to_char (sysdate, ' J ') from DUAL;

For example, to generate an arbitrary date within 2012 years, you can first determine the date integer of January 1, 2012;

SELECT To_char (to_date (' 01/01/12 ', ' mm/dd/yy '), ' J ') from DUAL;

The result of the system is 2455928. So to generate any date in that year, we're going to use a dbms_random with Low_value equals 2455928 and High_value equals the 2455928+365 parameter. VALUE, then convert it to date:

SELECT to_date (TRUNC (dbms_random. VALUE (2455928,2455928+365)), ' J ') from DUAL;

6) A comprehensive example:
SELECT level empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (Dbms_random. VALUE (+, 500000), 2) salary,
DECODE (ROUND (dbms_random. VALUE (1, 2)), 1, ' M ', 2, ' F ') gender,
To_date (ROUND (dbms_random. VALUE (1, 28))
|| ‘-‘
|| ROUND (Dbms_random. VALUE (1, 12))
|| ‘-‘
|| ROUND (Dbms_random. VALUE (1900, 2010)),
' Dd-mm-yyyy '
) DOB,
Dbms_random. STRING (' x ', Dbms_random. VALUE (+)) address
From DUAL
CONNECT by level < 10000;

Use Dbms_random to generate random numbers in Oracle