Oracle dbms_random package usage

1. dbms_random.value Method

Dbms_random is a package that can generate random values or strings. This package includes several functions, including initialize (), seed (), terminate (), value (), normal (), random (), and string (), but value () is the most commonly used, value () is generally used in two ways, the first
Function value return number;
If there is no parameter in this usage, a 38-bit precision value is returned, ranging from 0.0 to 1.0, but not 1.0, as shown in the following example:
SQL> set serverout on
SQL> begin
2 For I in 1 .. 10 Loop
3 dbms_output.put_line (round (dbms_random.value * 100 ));
4 end loop;
5 end;
6/
46
19
45
37
33
57
61
20
82
8

The PL/SQL process is successfully completed.

SQL>

The second value has two parameters: the first parameter indicates the lower limit and the second parameter indicates the upper limit. A number between the lower limit and the upper limit is generated, but the upper limit is not included, "There is no end to learning" refers to the second type, as follows:
SQL> begin
2 For I in 1 .. 10 Loop
3 dbms_output.put_line (trunc (dbms_random.value (1,101 )));
4 end loop;
5 end;
6/
97
77
13
86
68
16
55
36
54
46

The PL/SQL process is successfully completed.

2. dbms_random.string Method

Some user management programs may need to create random passwords for users. You can use dbms_random.string of 10 Gb to implement this function.

For example:
SQL> select dbms_random.string ('P', 8) from dual;
 
Dbms_random.string ('P', 8)
--------------------------------------------------------------------------------
3q <m "YF [
 
The meaning of the first parameter:
■ 'U', 'U'-returning string in uppercase Alpha characters
■ 'L', 'l'-returning string in lowercase Alpha characters
■ 'A', 'a'-returning string in mixed case Alpha characters
■ 'X', 'X'-returning string in uppercase alpha-numeric
Characters
■ 'P', 'P'-returning string in any printable characters.
Otherwise the returning string is in uppercase alpha
Characters.
P indicates printable, that is, the string is composed of any printable characters.

The second parameter indicates the length of the returned string.
3. dbms_random.random Method
Random returns a value of the binary_integer type to generate a random number of any size.
Example of difference between the value and dbms_random.value:
Order by dbms_random.value;
This statement is used to achieve random sorting of records.
In addition:
What is the difference between dbms_random.value and dbms_random.random?
1. Order by dbms_random.value: Calculate a random number for each row in the result set. dbms_random.value is a column in the result set (although this column is not in the select list), and then sort by this column, the order is random.
2. Check the DESC information to see the difference between the vlue and random functions. Value Returns the number type and the returned value is between 1 and 0, random returns the binary_integer type (a number stored in binary format. It is said that the computing efficiency is higher than the number, but I have not tested it, but the value range must be smaller than the number. For details, check the information)
If you want to implement random sorting, use the value function.

4. dbms_random.normal Method

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.

5. dbms_random.send Method

Used to generate a random number seed. The seed is set to generate a random number repeatedly for debugging. Otherwise, it is difficult to schedule different tasks.