欢迎来到代码驿站!

Oracle

当前位置:首页 > 数据库 > Oracle

Oracle随机函数之dbms_random使用详解

时间:2020-10-07 14:27:52|栏目:Oracle|点击:
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

上一篇:linux oracle数据库删除操作指南

栏    目:Oracle

下一篇:windows 7安装ORACLE 10g客户端的方法分享

本文标题:Oracle随机函数之dbms_random使用详解

本文地址:http://www.codeinn.net/misctech/8125.html

推荐教程

广告投放 | 联系我们 | 版权申明

重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

联系QQ:914707363 | 邮箱:codeinn#126.com(#换成@)

Copyright © 2020 代码驿站 版权所有