Oracle隨機函數(shù)之dbms_random使用詳解
更新時間：2012年12月03日 15:18:28 作者：
dbms_random是oracle提供的一個隨機函數(shù)包,本文將詳細介紹函數(shù)的使用,需要的朋友可以參考下
dbms_random是oracle提供的一個隨機函數(shù)包，以下介紹一些dbms_random的常用示例：
dbms_random.value用法：
生成一個大于等于0，小于等于1的38位小數(shù)
-- 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
生成一個指定范圍內(nèi)的數(shù)</ 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用法
獲取正態(tài)分布的隨機數(shù)
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來確定隨機數(shù)的起始點，對于相同的seed而言，隨機數(shù)的任意一次變化都將是確定的。
– 就是說，如果在某一時刻調(diào)用了seed，之后第一次產(chǎn)生的隨機數(shù)是4，第二次是6，第三次是1，
– 那么當你再次調(diào)用相同的seed之后，一次產(chǎn)生的隨機數(shù)還是4、6、1
– seed有兩種，一種是數(shù)值型的，一種是字符型(最大長度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
dbms_random.value用法：
生成一個大于等于0，小于等于1的38位小數(shù)
復制代碼 代碼如下:
-- 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
生成一個指定范圍內(nèi)的數(shù)</ 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用法
獲取正態(tài)分布的隨機數(shù)
復制代碼 代碼如下:
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來確定隨機數(shù)的起始點，對于相同的seed而言，隨機數(shù)的任意一次變化都將是確定的。
– 就是說，如果在某一時刻調(diào)用了seed，之后第一次產(chǎn)生的隨機數(shù)是4，第二次是6，第三次是1，
– 那么當你再次調(diào)用相同的seed之后，一次產(chǎn)生的隨機數(shù)還是4、6、1
– seed有兩種，一種是數(shù)值型的，一種是字符型(最大長度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
相關文章
oracle 數(shù)據(jù)庫連接個各種環(huán)境分析，讓大家較快的解決一些數(shù)據(jù)庫連接問題。2009-07-07
oracle Gateway是Oracle公司提供的一種訪問非Oracle數(shù)據(jù)源的方法,它可以將非Oracle數(shù)據(jù)源(如SQLserver、mysql等)的數(shù)據(jù)看作是oracle數(shù)據(jù)庫中的表,從而可以使用SQL和PLSQL等Oracle語言來訪問這些數(shù)據(jù),本文介紹了使用Oracle通過gateway連接MSSQL的詳細流程2024-12-12
Oracle 數(shù)據(jù)庫 臨時數(shù)據(jù)的處理方法
在Oracle數(shù)據(jù)庫中進行排序、分組匯總、索引等到作時，會產(chǎn)生很多的臨時數(shù)據(jù)。如有一張員工信息表，數(shù)據(jù)庫中是安裝記錄建立的時間來保存的。2009-06-06
VMware中l(wèi)inux環(huán)境下oracle安裝圖文教程（二）ORACLE 10.2.05版本的升級補丁安裝
這篇文章是VMware中l(wèi)inux環(huán)境下oracle安裝圖文教程系列的第二篇，主要介紹了ORACLE 10.2.05版本的升級補丁安裝,需要的朋友可以參考下2014-08-08
oracle生成動態(tài)前綴且自增號碼的函數(shù)分享
這篇文章主要介紹了oracle生成動態(tài)前綴且自增號碼的函數(shù),需要的朋友可以參考下2014-04-04
連接oracle報錯:ora-28001:the?password?has?expired解決辦法
這篇文章主要給大家介紹了關于連接oracle報錯:ora-28001:the?password?has?expired的解決辦法,當遇到ora-28001錯誤時,這通常表示提供的密碼不符合Oracle數(shù)據(jù)庫的密碼策略要求,需要的朋友可以參考下2024-07-07
統(tǒng)計信息主要是描述數(shù)據(jù)庫中表,索引的大小,規(guī)模,數(shù)據(jù)分布狀況等的一類信息,下面這篇文章主要給大家介紹了關于Oracle收集和查看統(tǒng)計信息的方法,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-05-05
這篇文章主要介紹了oracle用戶權限管理使用方法,需要的朋友可以參考下2014-04-04
oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式匯總
最近項目需要進行行轉(zhuǎn)列,經(jīng)過上網(wǎng)查找到了一些解決方法,分享給大家,這篇文章主要給大家介紹了關于oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式,文中通過圖文以及實例代碼介紹的非常詳細,需要的朋友可以參考下2023-05-05
本篇文章是對oracle對select加鎖的方法以及鎖的查詢進行了詳細的分析介紹，需要的朋友參考下2013-05-05