oracle random函数,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用法

–可以设置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