Oracle随机函数之dbms_random使用详解
dbms_random是oracle提供的一个随机函数包,以下介绍一些dbms_random的常用示例:
dbms_random.value用法:
生成一个大于等于0,小于等于1的38位小数
生成一个指定范围内的数</ p>
dbms_random.normal用法
获取正态分布的随机数
dbms_random.string用法
获取指定字符串
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)的
--SESSION 1
--SESSION 2
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
相关推荐
oraclemch 2020-11-06
Seandba 2020-08-16
dbasunny 2020-08-16
娜娜 2020-06-22
专注前端开发 2020-10-21
苏康申 2020-11-13
vitasfly 2020-11-12
liuyang000 2020-09-25
FellowYourHeart 2020-10-05
赵继业 2020-08-17
whyname 2020-08-16
拼命工作好好玩 2020-08-15
langyue 2020-08-15
写程序的赵童鞋 2020-08-03
Accpcjg 2020-08-02
tydldd 2020-07-30
好记忆也需烂 2020-07-28