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用法
?C可以设置seed来确定随机数的起始点，对于相同的seed而言，随机数的任意一次变化都将是确定的。
?C 就是说，如果在某一时刻调用了seed，之后第一次产生的随机数是4，第二次是6，第三次是1，
?C 那么当你再次调用相同的seed之后，一次产生的随机数还是4、6、1
?C 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

频道导航

Oracle随机函数之dbms_random使用详解

您可能感兴趣的文章: