亚洲第一区精品日韩在线播放,亚洲欧美日韩中文高清www777,国产精品久久久久久亚洲影视

Oracle中的隨機函數(shù)

??? 沒事做簡單看了一下Oracle隨機函數(shù)的取法：

dbms_random

CREATE OR REPLACE PACKAGE SYS.dbms_random AS

??? ------------

??? --? OVERVIEW

??? --

??? --? This package should be installed as SYS.? It generates a sequence of

??? --? random 38-digit Oracle numbers.? The expected length of the sequence

??? --? is about power(10,28), which is hopefully long enough.

??? --

??? --------

??? --? USAGE

??? --

??? --? This is a random number generator.? Do not use for cryptography.

??? --? For more options the cryptographic toolkit should be used.

??? --

??? --? By default, the package is initialized with the current user

??? --? name, current time down to the second, and the current session.

??? --

??? --? If this package is seeded twice with the same seed, then accessed

??? --? in the same way, it will produce the same results in both cases.

??? --

??? --------

??? --? EXAMPLES

??? --

??? --? To initialize or reset the generator, call the seed procedure as in:

??? --????? execute dbms_random.seed(12345678);

??? --??? or

??? --????? execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));

??? --? To get the random number, simply call the function, e.g.

??? --????? my_random_number BINARY_INTEGER;

??? --????? my_random_number := dbms_random.random;

??? --??? or

??? --????? my_random_real NUMBER;

??? --????? my_random_real := dbms_random.value;

??? --? To use in SQL statements:

??? --????? select dbms_random.value from dual;

??? --????? insert into a values (dbms_random.value);

??? --????? variable x NUMBER;

??? --????? execute :x := dbms_random.value;

??? --????? update a set a2=a2+1 where a1 < :x;

??? -- Seed with a binary integer

??? PROCEDURE seed(val IN BINARY_INTEGER );

??? PRAGMA restrict_references (seed, WNDS );

??? -- Seed with a string (up to length 2000)

??? PROCEDURE seed(val IN VARCHAR2 );

??? PRAGMA restrict_references (seed, WNDS );

??? -- Get a random 38-digit precision number, 0.0 <= value < 1.0

??? FUNCTION value RETURN NUMBER ;

??? PRAGMA restrict_references ( value , WNDS );

??? -- get a random Oracle number x, low <= x < high

??? FUNCTION value (low IN NUMBER , high IN NUMBER ) RETURN NUMBER ;

??? PRAGMA restrict_references ( value , WNDS );

??? -- get a random number from a normal distribution

??? FUNCTION normal RETURN NUMBER ;

??? PRAGMA restrict_references (normal, WNDS );

??? -- get a random string

??? 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

????????? */

??????? RETURN VARCHAR2 ;? -- string of <len> characters (max 60)

??? PRAGMA restrict_references ( string , WNDS );

??? -- Obsolete, just calls seed(val)

??? PROCEDURE initialize(val IN BINARY_INTEGER );

??? PRAGMA restrict_references (initialize, WNDS );

??? -- Obsolete, get integer in ( -power(2,31) <= random < power(2,31) )

??? FUNCTION random RETURN BINARY_INTEGER ;

??? PRAGMA restrict_references (random, WNDS );

??? -- Obsolete, does nothing

??? PROCEDURE terminate;

??? TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;

END dbms_random;

??? 簡單得說，通過dbms_random包調(diào)用隨機數(shù)的方法大致有4種：

1、dbms_random.normal

??? 這個函數(shù)不帶參數(shù)，能返回normal distribution的一個number類型，所以基本上隨機數(shù)會在-1到1之間。

??? 簡單測試了一下，產(chǎn)生100000次最大能到5左右：

??? SQL> declare
????? 2??? i number:=0;
????? 3??? j number:=0;
????? 4? begin
????? 5??? for k in 1 .. 100000 loop
????? 6??? i:= dbms_random.normal;
????? 7????? if i > j
????? 8??????? then j:=i;
????? 9????? end if;
?????10??? end loop;
?????11??? dbms_output.put_line(j);
?????12? end;
?????13? /

??? 5.15325081797418404136433867107468983182
?
??? PL/SQL procedure successfully completed

2、dbms_random.random

??? 這個也沒有參數(shù)，返回一個從-power(2,31)到power(2,31)的整數(shù)值

3、dbms_random.value

??? 這個函數(shù)分為兩種，一種是沒有參數(shù)，則直接返回0-1之間的38位小數(shù)

??? SQL > column value format 9.99999999999999999999999999999999999999
??? SQL > select dbms_random.value from dual;

??????????????????????????????????????? VALUE
??? -----------------------------------------
????? .58983014999643548701631750396301271752

??? 第二種是加上兩個參數(shù)a、b，則返回值在a、b之間的38位小數(shù)

??? SQL > column value format 999.999999999999999999999999999999999999
??? SQL > select dbms_random.value(100,500) value from dual;

??????????????????????????????????????? VALUE
??? -----------------------------------------
?????412.150194612502916808701157054098274240

??? 注意：無論前面幾位，小數(shù)點之后都是38位

4、dbms_random.string

??? 這個函數(shù)必須帶有兩個參數(shù)，前面的字符指定類型，后面的數(shù)值指定位數(shù)(最大60)

??? 類型說明：

??? '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 > column value format a30
??? SQL > select dbms_random.string('u',30) value from dual;

??? VALUE
??? ------------------------------
??? VTQNLGISELPXEDBXKUZLXKBAJMUTIA

??? SQL > select dbms_random.string('l',30) value from dual;

??? VALUE
??? ------------------------------
??? uqygsbquingfqdytpgjvdoblxeglgu

??? SQL > select dbms_random.string('a',30) value from dual;

??? VALUE
??? ------------------------------
??? NGTGkQypuSWhBfcrHiOlQwOUXkqJjy

??? SQL > select dbms_random.string('x',30) value from dual;

??? VALUE
??? ------------------------------
??? UVWONYJMXT31VEFPD736WJCJ5QT6BD

??? SQL > select dbms_random.string('p',30) value from dual;

??? VALUE
??? ------------------------------
??? :mak$(WT4M_7c/+f[_XUscf$P Zcq{

5、關于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)的

??? -- Seed with a binary integer

??? PROCEDURE seed(val IN BINARY_INTEGER );

??? PRAGMA restrict_references (seed, WNDS );

??? -- Seed with a string (up to length 2000)

??? PROCEDURE seed(val IN VARCHAR2 );

??? PRAGMA restrict_references (seed, WNDS );

6、關于initialize

??? 一個integer參數(shù)，注釋說的很清楚了：

??? -- Obsolete, just calls seed(val)

??? PROCEDURE initialize(val IN BINARY_INTEGER );

??? PRAGMA restrict_references (initialize, WNDS );

sys_guid()

??? 官方文檔的說明如下：

??? SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

??? 簡單得說就是，隨機生成一個32位的RAW，但是后面的那段經(jīng)過實驗發(fā)現(xiàn)不是這么回事，每次生成的字符串都千差萬別，不知道為什么。

??? 在具體應用中，除了可以用來插入生成唯一的標識符外，還可以用來取表中的任意一條記錄：

??? select ?* from ( select * from t2 order by sys_guid()) where rownum = 1 ;

-The End-

posted on 2008-10-24 10:09 decode360-3 閱讀(4476) 評論(0) 編輯收藏所屬分類: Oracle

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Oracle用戶鎖定等操作 ORA_ROWSCN [轉(zhuǎn)] SQLPlus中的COPY指令學習 utl_file包介紹數(shù)據(jù)庫一致性研究【Oracle SCN】 Oracle不同版本同主機安裝記錄物化視圖幾個知識點 TKPROF使用實例 TKPROF使用學習關于Views的Updatable問題

Decode360's Blog

公告

常用鏈接

隨筆分類(299)

隨筆檔案(299)

文章分類(26)

新聞分類(24)

收藏夾(5)

Blog列表

IT網(wǎng)站

My Link

最新隨筆

最新評論