DBMS_RANDOM package usage in Oracle

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;

Result:

 

Type 2: FUNCTION value (low in number, high in number) return number;

Note:

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.

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

 

FUNCTION string (opt CHAR, len NUMBER) RETURN VARCHAR2;

Note:

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.

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

■ 'X', 'X'-returning string in uppercase alpha-numericcharacters

■ 'P', 'P'-returning string in any printable characters. Otherwise the returning string is in uppercase alphacharacters.

Len:

Length of the returned string.

Example:

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

Result:

3. dbms_random.random Method

 

FUNCTIONrandomRETURN BINARY_INTEGER;

Note:

Random returns a value of the BINARY_INTEGER type to generate a random number of any size.

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

 

FUNCTIONnormal return number;

Note:

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.

Example:

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

Result:

5. dbms_random.seed Method

 

PROCEDURE seed (val IN BINARY_INTEGER); or PROCEDURE seed (val IN VARCHAR2 );

Note:

It is 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.

Example:

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