Functions for generating random numbers in Oracle databases

The Dbms_random package in Oracle encapsulates a number of functions that generate random numbers and random strings, with the following two commonly used:

Dbms_random. Value function

This function is used to produce a random number, and there are two ways to use it:

1. Produces a 1-bit precision random number between 0 and 1 (not including 0 and 38), and the syntax is:

Dbms_random. VALUE return number;

This usage does not contain parameters.

2. Produces a random number of 38-bit precision within a specified range, and the syntax is:

Dbms_random. VALUE (Low in Number,high in number) return number;

This usage contains two parameters, the parameter low is used to specify the lower bound of the random number to be generated, the parameter high specifies the upper bound, and the generated random. Note that the generated random number may be equal to the lower limit, but definitely less than the upper limit, that is, "low<= random number

For example: to produce a number between 1 and 100 can be written like this: Dbms_random. VALUE (1,100)

Dbms_random. String function

The function produces a random string with the syntax:

Dbms_random. STRING (opt in Char,len in number) return VARCHAR2;

Parameter len Specifies the length of the generated string.

Parameter opt specifies the style of the generated string, the allowable values, and the meanings they represent, as shown in the following table:

Take value	Meaning
' U ' or ' u '	Returns a string consisting of uppercase letters
' L ' or ' l '	Returns a string consisting of lowercase letters
' A ' or ' a '	Returns a string consisting of uppercase and lowercase letters
' x ' or ' x '	Returns a string of uppercase and numeric characters
' P ' or ' P '	Returns a string that consists of any printable character

For example: to produce a string that has a length of 6 and includes only uppercase letters, you can write this: Dbms_random. STRING (' U ', 6)