Oracle随机函数之dbms_random使用详解

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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 