Oracle隨機(jī)函數(shù)之dbms_random使用詳解

更新時(shí)間：2012年12月03日 15:18:28 作者：

dbms_random是oracle提供的一個(gè)隨機(jī)函數(shù)包,本文將詳細(xì)介紹函數(shù)的使用,需要的朋友可以參考下

dbms_random是oracle提供的一個(gè)隨機(jī)函數(shù)包，以下介紹一些dbms_random的常用示例：
dbms_random.value用法：
生成一個(gè)大于等于0，小于等于1的38位小數(shù)

 
-- FUNCTION value RETURN NUMBER; 
select dbms_random.value from dual; 
SQL> select dbms_random.value from dual; 
VALUE 
---------- 
0.61011338 

復(fù)制代碼代碼如下:

 
-- FUNCTION value RETURN NUMBER; 
select dbms_random.value from dual; 
SQL> select dbms_random.value from dual; 
VALUE 
---------- 
0.61011338 

生成一個(gè)指定范圍內(nèi)的數(shù)</ p>

復(fù)制代碼代碼如下:

 
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 

復(fù)制代碼代碼如下:

 
-- 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用法
獲取正態(tài)分布的隨機(jī)數(shù)

復(fù)制代碼代碼如下:

 
select dbms_random.normal from dual; 
SQL> select dbms_random.normal from dual; 
NORMAL 
---------- 
-1.7330759 

復(fù)制代碼代碼如下:

 
select dbms_random.normal from dual; 
SQL> select dbms_random.normal from dual; 
NORMAL 
---------- 
-1.7330759 

dbms_random.string用法
獲取指定字符串

復(fù)制代碼代碼如下:

 
/* "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; 

復(fù)制代碼代碼如下:

 
--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用法
–可以設(shè)置seed來(lái)確定隨機(jī)數(shù)的起始點(diǎn)，對(duì)于相同的seed而言，隨機(jī)數(shù)的任意一次變化都將是確定的。
– 就是說(shuō)，如果在某一時(shí)刻調(diào)用了seed，之后第一次產(chǎn)生的隨機(jī)數(shù)是4，第二次是6，第三次是1，
– 那么當(dāng)你再次調(diào)用相同的seed之后，一次產(chǎn)生的隨機(jī)數(shù)還是4、6、1
– seed有兩種，一種是數(shù)值型的，一種是字符型(最大長(zhǎng)度2000)的

復(fù)制代碼代碼如下:

 
SELECT USERENV('SESSIONID') 
FROM DUAL; 
BEGIN 
dbms_random.seed(6); 
END; 
/ 
SELECT DBMS_RANDOM.value 
FROM DUAL 
CONNECT BY LEVEL < 10; 

復(fù)制代碼代碼如下:

 
SELECT USERENV('SESSIONID') 
FROM DUAL; 
BEGIN 
dbms_random.seed(6); 
END; 
/ 
SELECT DBMS_RANDOM.value 
FROM DUAL 
CONNECT BY LEVEL < 10; 

--SESSION 1

復(fù)制代碼代碼如下:

 
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 

復(fù)制代碼代碼如下:

 
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

復(fù)制代碼代碼如下:

 
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 

復(fù)制代碼代碼如下:

 
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 