Oracle dbms_random Random Number pack detailed

Oracle Dbms_random packages are primarily used to obtain random numbers, can be numbers or letters, and can be mixed. Common functions are as follows:

Dbms_random.value

Generates a 38-bit random decimal number for a specified range (38 digits after the decimal point), and defaults to random numbers with a range of [0,1] if no range is specified.

Oracle's official description is: The VALUE function produces numbers in the range [0,1] with the digits of precision.

For example

Generate a random decimal between 0~1 (including 0, excluding 1):

Select Dbms_random.value from dual;

Return: 0.800625 ...

Generate a random decimal between 1~10 (including 1 and excluding 10):

Select Dbms_random.value (1,10) from dual;

Return: 8.5929 ...

Dbms_random.normal

Generates a random number that conforms to the normal distribution, which 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.

For example

Select Dbms_random.normal from dual;

Return: 1.0748 ...

Dbms_random.random

Generate an integer value from-power (2,31) to power (2,31), noting that the interval is left closed to the right.

The official description is: The RANDOM function produces integers in the range [ -2^^31, 2^^31).

For example

Select Dbms_random.random from dual;

Returns: 2018005364

Randomly get 3 records from the EMP table

SELECT * FROM (SELECT * to Scott.emp ORDER by Dbms_random.random) where RowNum < 4;

Dbms_random.string

Generates a random string of the specified number of bits in the specified pattern, with the following selectable modes:

' U ' or ' u ', return only uppercase letters

' L ' or ' l ', return only lowercase letters

' A ' or ' a ', return uppercase and lowercase letters mixed

' x ' or ' X ', returns a mix of uppercase letters and numbers

' P ' or ' P ', returns any displayed character

For example

Select Dbms_random.string (' U ', ten) from dual;

return: MYYUYFQQHS

Select Dbms_random.string (' L ', ten) from dual;

return: Vuzscusike

Select Dbms_random.string (' A ', ten) from dual;

return: Ozognkaerk

Select Dbms_random.string (' X ', ten) from dual;

return: RC6IZ10FVT

Select Dbms_random.string (' X ', ten) from dual;

return: a1e-=s) w%1

Dbms_random.seed :

Generates a random number at a specified starting point, and any change in random numbers is deterministic for the same seed.

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

Dbms_random. VALUE function

This function is used to produce a random number in two ways:

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

Dbms_random. VALUE RETURN number;

This usage does not include parameters.

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

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

This usage consists of 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 is definitely less than the upper limit, or "low<= random number .

For example: to produce a number between 1 and 100, you can write this: Dbms_random. VALUE (1,100)

Dbms_random. STRING function

The function produces a random string with the following syntax:

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

The parameter len specifies the length of the generated string.

The parameter opt specifies the style of the resulting string, the allowable value and the meaning of the representation 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 consisting of uppercase letters and numbers
' P ' or ' P '	Returns a string that consists of any printable character

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

Attached: pseudo random number and random seed

The first thing to declare is that the computer does not produce an absolute random number, and the computer can only produce "pseudo-random numbers". In fact, an absolute random random number is only an ideal random number, and even if the computer develops, it will not produce a random number of absolute random numbers. A computer can only generate a relative random number, or pseudo-random number.

The so-called random number generator is a certain algorithm to the pre-selected random seed to do a complex operation, with the resulting results to approximate the simulation of a complete random number, this random number is called pseudo-random number. Pseudo-random numbers are selected from a finite set of numbers in the same probability. The selected number is not completely random, but from a practical point of view, its randomness is sufficient. The selection of pseudo-random numbers starts with random seeds, so the selection of random seeds is very important in order to ensure that the pseudo-random numbers are sufficiently "random" for each given. If the random seed is the same, then the random number generated by the same random number generator will be the same. In general, we use parameters associated with the system time as random seeds.

Oracle dbms_random Random Number pack detailed