DBMS_RANDOM package usage in Oracle

Dbms_random is a package that can generate random values or strings. This package includes initialize (), seed (), terminate (), value (), normal (), rando

Dbms_random is a package that can generate random values or strings. This package includes initialize (), seed (), terminate (), value (), normal (), rando

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 common. The following describes the usage of each function in sequence:

1. dbms_random.value Method

First: FUNCTION value return number;

Note:

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.

Example:

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

Note:

Example:

BEGIN
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (trunc (dbms_random.value (1,101 )));
End loop;
END;

Result:

2. dbms_random.string Method

Note:

Parameters:

Opt:

■ '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

Len:

Length of the returned string.

Example:

SELECT dbms_random.string ('P', 8) FROM dual;

Result:

Note:

Example:

SELECT dbms_random.random FROM dual;

Result:

Note: 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. value Returns the number type, and the returned value is between 1 and 0, while random returns the BINARY_INTEGER type (a number stored in binary format, it is said that the calculation 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)

4. dbms_random.normal Method

Note:

Example:

BEGIN
FOR I IN 1 .. 10 LOOP
Dbms_output.put_line (round (dbms_random.normal ));
End loop;
END;

Result:

Note:

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

Example:

Result (no matter how many times it is executed ):