Dbms_random is a random function package provided by Oracle, here are some common examples of dbms_random:
Dbms_random.value usage:
Generates a 38 decimal number that is greater than or equal to 0, or less than or equal to 1
Copy Code code as follows:
--FUNCTION value return number;
Select Dbms_random.value from dual;
Sql> select Dbms_random.value from dual;
VALUE
----------
0.61011338
Copy Code code as follows:
--FUNCTION value return number;
Select Dbms_random.value from dual;
Sql> select Dbms_random.value from dual;
VALUE
----------
0.61011338
Generates a number within a specified range </p>
Copy Code code as follows:
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
Copy Code code 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
Get random number of normal distribution
Copy Code code as follows:
Select Dbms_random.normal from dual;
Sql> select Dbms_random.normal from dual;
NORMAL
----------
-1.7330759
Copy Code code as follows:
Select Dbms_random.normal from dual;
Sql> select Dbms_random.normal from dual;
NORMAL
----------
-1.7330759
Dbms_random.string usage
Gets the specified string
Copy Code code 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 Code code 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
Ten rows selected
Dbms_random.seed usage
– Seed can be set to determine the starting point for random numbers, and any change in random numbers will be determined for the same seed.
-that is, if you call seed at some point, the first random number that comes up is 4, the second is 6, and the third is 1,
– Then when you call the same seed again, the random number generated at one time is 4, 6, 1.
There are two kinds of –seed, one is numerical type, the other is character type (maximum length 2000)
Copy Code code as follows:
SELECT USERENV (' SESSIONID ')
From DUAL;
BEGIN
Dbms_random.seed (6);
End;
/
SELECT Dbms_random.value
From DUAL
CONNECT by level < 10;
Copy Code code as follows:
SELECT USERENV (' SESSIONID ')
From DUAL;
BEGIN
Dbms_random.seed (6);
End;
/
SELECT Dbms_random.value
From DUAL
CONNECT by level < 10;
--session 1
Copy Code code 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 Code code 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 2
Copy Code code 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 Code code 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 CON Nect by level < 10;
VALUE
----------
0.53801770
0.67499536
0.65362270
0.76351985
0.29859834
0.40 522032
0.99551636
0.39565580
0.18074760
9 rows selected
