Dbms_random usage of Oracle Random Functions

Dbms_random is a random function package provided by oracle. The following describes common examples of dbms_random:
Dbms_random.value usage:
Generate a 38-digit decimal number greater than or equal to 0 and less than or equal to 1 Copy codeThe Code is as follows: -- FUNCTION value return number;
Select dbms_random.value from dual;
SQL> select dbms_random.value from dual;
VALUE
----------
0.61011338

Copy codeThe Code is as follows: -- FUNCTION value return number;
Select dbms_random.value from dual;
SQL> select dbms_random.value from dual;
VALUE
----------
0.61011338

Generate a number within a specified range </p>Copy codeThe Code is as follows: select dbms_random.value (, 0)
From dual;
SQL> select dbms_random.value (100,0)
2 from dual;
DBMS_RANDOM.VALUE (100,0)
------------------------
20.7742244285517

Copy codeThe Code is as follows: -- FUNCTION value (low in number, high in number) return number;
Select dbms_random.value (100,0)
From dual;
SQL> select dbms_random.value (100,0)
2 from dual;
DBMS_RANDOM.VALUE (100,0)
------------------------
20.7742244285517

Dbms_random.normal usage
Obtain the random number of a normal distribution.Copy codeThe Code is as follows: select dbms_random.normal from dual;
SQL> select dbms_random.normal from dual;
NORMAL
----------
-1.7330759

Copy codeThe Code is as follows: select dbms_random.normal from dual;
SQL> select dbms_random.normal from dual;
NORMAL
----------
-1.7330759

Dbms_random.string usage
Obtains a specified string.Copy codeThe Code is as follows:/* "opt" specifies that the returned string may contain:
'U', 'U': upper case alpha characters only
'L', 'L': lower case alpha characters only
'A', 'A': alpha characters only (mixed case)
'X', 'x': any alpha-numeric characters (upper)
'P', 'P': any printable characters
*/
SQL>
Select
Dbms_random.string ('U', 10)
From dual
Union all
Select
Dbms_random.string ('U', 10)
From dual
Union all
Select
Dbms_random.string ('l', 10)
From dual
Union all
Select
Dbms_random.string ('l', 10)
From dual
Union all
Select
Dbms_random.string ('A', 10)
From dual
Union all
Select
Dbms_random.string ('A', 10)
From dual
Union all
Select
Dbms_random.string ('x', 10)
From dual
Union all
Select
Dbms_random.string ('x', 10)
From dual
Union all
Select
Dbms_random.string ('P', 10)
From dual
Union all
Select
Dbms_random.string ('P', 10)
From dual;

Copy codeThe Code is as follows: -- FUNCTION string (opt char, len NUMBER)
/* "Opt" specifies that the returned string may contain:
'U', 'U': upper case alpha characters only
'L', 'L': lower case alpha characters only
'A', 'A': alpha characters only (mixed case)
'X', 'x': any alpha-numeric characters (upper)
'P', 'P': any printable characters
*/
SQL>
Select
Dbms_random.string ('U', 10)
From dual
Union all
Select
Dbms_random.string ('U', 10)
From dual
Union all
Select
Dbms_random.string ('l', 10)
From dual
Union all
Select
Dbms_random.string ('l', 10)
From dual
Union all
Select
Dbms_random.string ('A', 10)
From dual
Union all
Select
Dbms_random.string ('A', 10)
From dual
Union all
Select
Dbms_random.string ('x', 10)
From dual
Union all
Select
Dbms_random.string ('x', 10)
From dual
Union all
Select
Dbms_random.string ('P', 10)
From dual
Union all
Select
Dbms_random.string ('P', 10)
From dual;

DBMS_RANDOM.STRING ('U', 10)
----------
TXREHAICRI
VDTMXZORVB
Udavjpudfb
Hvfqhjjdgz
TZoanQzxtX
SiATLEZXQa
2LWWZ3H3L5
ZF6MKKG1R7
# \ J5IPva (W
SJe/srX: ZB
10 rows selected
Dbms_random.seed usage
-You can set seed to determine the starting point of the random number. For the same seed, any change of the random number will be determined.
-That is to say, if seed is called at a certain time point, the random number generated for the first time is 4, the second is 6, and the third is 1,
-When you call the same seed again, the random number generated at a time is 4, 6, and 1.
-Seed has two types: numeric and linear (maximum length: 2000 ).Copy codeThe Code is as follows: select userenv ('sessionid ')
From dual;
BEGIN
Dbms_random.seed (6 );
END;
/
SELECT DBMS_RANDOM.value
FROM DUAL
Connect by level <10;

Copy codeThe Code is as follows: select userenv ('sessionid ')
From dual;
BEGIN
Dbms_random.seed (6 );
END;
/
SELECT DBMS_RANDOM.value
FROM DUAL
Connect by level <10;

-- SESSION 1Copy codeThe Code is as follows: SQL> SELECT USERENV ('sessionid ')
2 from dual;
USERENV ('sessionid ')
--------------------
15140521
SQL> BEGIN
2 dbms_random.seed (100 );
3 END;
4/
PL/SQL procedure successfully completed
SQL> SELECT DBMS_RANDOM.value
2 FROM DUAL
3 connect by level <10;
VALUE
----------
0.53801770
0.67499536
0.65362270
0.76351985
0.29859834
0.40522032
0.99551636
0.39565580
0.18074760
9 rows selected

Copy codeThe Code is as follows: SQL> SELECT USERENV ('sessionid ')
2 from dual;
USERENV ('sessionid ')
--------------------
15140521
SQL> BEGIN
2 dbms_random.seed (100 );
3 END;
4/
PL/SQL procedure successfully completed
SQL> SELECT DBMS_RANDOM.value
2 FROM DUAL
3 connect by level <10;
VALUE
----------
0.53801770
0.67499536
0.65362270
0.76351985
0.29859834
0.40522032
0.99551636
0.39565580
0.18074760
9 rows selected

-- SESSION 2Copy codeThe Code is as follows: SQL> SELECT USERENV ('sessionid ')
2 from dual;
USERENV ('sessionid ')
--------------------
15140517
SQL> BEGIN
2 dbms_random.seed (100 );
3 END;
4/
PL/SQL procedure successfully completed
SQL> SELECT DBMS_RANDOM.value
2 FROM DUAL
3 connect by level <10;
VALUE
----------
0.53801770
0.67499536
0.65362270
0.76351985
0.29859834
0.40522032
0.99551636
0.39565580
0.18074760
9 rows selected

Copy codeThe Code is as follows: SQL> SELECT USERENV ('sessionid ')
2 from dual;
USERENV ('sessionid ')
--------------------
15140517
SQL> BEGIN
2 dbms_random.seed (100 );
3 END;
4/
PL/SQL procedure successfully completed
SQL> SELECT DBMS_RANDOM.value
2 FROM DUAL
3 connect by level <10;
VALUE
----------
0.53801770
0.67499536
0.65362270
0.76351985
0.29859834
0.40522032
0.99551636
0.39565580
0.18074760
9 rows selected