oracle random function, oracle random function

Requires DBMS random

Oracle's PL SQL provides the generation of random numbers and random strings.

The list is as follows:

1. Decimal (0 1).

select dbms_ from dual;

2. Decimals (0 100) within the specified range

select dbms_,100) from dual;

3. An integer (0 100) within the specified range

select trunc(dbms_,100)) from dual;

4. A string of random numbers with a length of 20.

select substr(cast(dbms_ as varchar2(38)),3,20) from dual;

5. Normally distributed random numbers.

select dbms_ from dual;

6. Random strings.

select dbms_, length) from dual;

The possible values of OPT are as follows:

u','u':Majuscule.

l','l':B.

a','a': Uppercase and lowercase letters.

x','x': Numbers, capital letters.

p','p': printable characters.

7. Random dates.

select to_date(2454084+trunc(dbms_,365)),'j') from dual

Use the following statement to get the cardinality of the specified date.

select to_char(sysdate,'j') from dual;

8. Generate GUID

select sys_guid() from dual;

1、dbms_

This function has no arguments and returns a number type of normal distribution, so basically the random number will be between -1 and 1.

2、dbms_

This one also has no arguments and returns an integer value from -power(2,31) to power(2,31).

3、dbms_

There are two types of this function, one is that there are no arguments, and it directly returns 38 decimal places between 0-1.

4、dbms_

This function must have two parameters, the first character specifies the type, and the following value specifies the number of digits (up to 60).

The first form of the value function returns a random number greater than or equal to 0 and less than 1; The second form returns a random number greater than or equal to low and less than high. Here's an example of how it might be used:

sql> select dbms_, dbms_,18) from dual;

value dbms_,18)

The DBMS Random package provides a built-in random number generator.

In oracle, the process of selecting the seed is called initialization, and before executing the random function, you can not perform the initialization explicitly, and the oracle will automatically initialize with the date, user ID, and process ID. Of course, it can also be explicitly initialized or called custom initialization.

The val parameter represents the seed number.

The string length can be up to 2000

The return value of the random function can be in the range of [ -231231, so if we want to get a pseudo-random result in the range [-1,1]:

If it is [0,1], take the absolute value.

If you want to think of [m,n] (m[m,n] (mIn the example of the random function, we have already discussed the interval [0,1] and the interval [m,n](m, which returns a random number on [0,1] when there are no parameters.

The low parameter represents the minimum value and the high parameter represents the maximum value, returning a random number of masks in the [low,high] interval.

The opt parameter controls the case format of the string, and the optional values and meanings are as follows:

u'or'u': Indicates a capital letter.

l' or 'l': Indicates lowercase letters.

a' or 'a': Indicates a mix of case and case.

x' or 'x': Indicates a mix of capital letters and numbers.

p' or 'p': Indicates any displayable character.

len indicates the length of the string.

In the DBMS Random package in Oracle, there are a number of functions that generate random numbers and random strings, and the following two are commonly used.

dbms random value function.

This function is used to generate a random number in two ways.

Produces a bit-precision random number between and (without and ) The syntax is .

dbms_random value return number;This usage does not contain parameters.

Produces a random number within the specified range of spike imitation silver precision syntax is.

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, and the parameter high specifies the upper limit of the random number to be generated, and the generated random number may be equal to the lower limit, but it is definitely less than the upper limit, i.e. "low< = random number.

For example, to generate a number between to can be written like this: dbms random value( ).

dbms random string function.

The function produces a random string with a syntax of .

dbms_random string (opt in char len in number) return varchar ;The len parameter specifies the length of the generated string.

The following table describes the allowed values and meanings of the generated strings specified in the opt parameter.

Valid values Meaning 'u' or 'u' Returns a string of uppercase letters 'l' or 'l' Returns a string of lowercase letters 'a' or 'a' Returns a string of uppercase and lowercase letters 'x' or 'x' returns a character string of uppercase letters and numbers 'p' or 'p' returns a string of arbitrary printable characters lishixinzhi article program oracle 201311 19036