Oracle随机函数之dbms_random使用详解
网络编程 2021-07-05 16:08www.168986.cn编程入门
dbms_random是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用法
–可以设置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 suessfully pleted
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 suessfully pleted
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 suessfully pleted
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 suessfully pleted
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
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 suessfully pleted
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 suessfully pleted
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 suessfully pleted
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 suessfully pleted
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
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程：电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示：编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程