Oracle隨機函數之dbms_random使用詳解

dbms_random是oracle提供的一個隨機函數包，以下介紹一些dbms_random的常用樣本：
dbms_random.value用法：
產生一個大於等於0，小於等於1的38位小數 複製代碼 代碼如下:-- FUNCTION value RETURN NUMBER;
select dbms_random.value from dual;
SQL> select dbms_random.value from dual;
VALUE
----------
0.61011338

複製代碼 代碼如下:-- FUNCTION value RETURN NUMBER;
select dbms_random.value from dual;
SQL> select dbms_random.value from dual;
VALUE
----------
0.61011338

產生一個指定範圍內的數</ p> 複製代碼 代碼如下: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

複製代碼 代碼如下:-- 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用法
擷取常態分佈的隨機數 複製代碼 代碼如下:select dbms_random.normal from dual;
SQL> select dbms_random.normal from dual;
NORMAL
----------
-1.7330759

複製代碼 代碼如下:select dbms_random.normal from dual;
SQL> select dbms_random.normal from dual;
NORMAL
----------
-1.7330759

dbms_random.string用法
擷取指定字串 複製代碼 代碼如下:/* "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;

複製代碼 代碼如下:--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用法
–可以設定seed來確定隨機數的起始點，對於相同的seed而言，隨機數的任意一次變化都將是確定的。
– 就是說，如果在某一時刻調用了seed，之後第一次產生的隨機數是4，第二次是6，第三次是1，
– 那麼當你再次調用相同的seed之後，一次產生的隨機數還是4、6、1
– seed有兩種，一種是數值型的，一種是字元型(最大長度2000)的 複製代碼 代碼如下:SELECT USERENV('SESSIONID')
FROM DUAL;
BEGIN
dbms_random.seed(6);
END;
/
SELECT DBMS_RANDOM.value
FROM DUAL
CONNECT BY LEVEL < 10;

複製代碼 代碼如下:SELECT USERENV('SESSIONID')
FROM DUAL;
BEGIN
dbms_random.seed(6);
END;
/
SELECT DBMS_RANDOM.value
FROM DUAL
CONNECT BY LEVEL < 10;

--SESSION 1 複製代碼 代碼如下: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

複製代碼 代碼如下: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 複製代碼 代碼如下: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

複製代碼 代碼如下: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