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