DBMS_RADOM——Oracle中的隨機函數
?
?
??? 最近閑著沒事，又看了一下PLSQL的Package，今天來學習一下DBMS_RADOM包，簡單了解Oracle取隨機函數的方法。最后還附上在Oracle中另一種可以取到隨機數的方法。OK，首先按照慣例，先來看一下dbms_random包在Oracle中自帶的注釋說明：
?
*****************************************************************************
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包調用隨機數的方法大致有4種：
?
1、dbms_random.normal
?
??? 這個函數不帶參數，能返回normal distribution的一個number類型，所以基本上隨機數會在-1到1之間。
?
??? 簡單測試了一下，產生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
?
??? 這個也沒有參數，返回一個從-power(2,31)到power(2,31)的整數值
?
?
3、dbms_random.value
?
??? 這個函數分為兩種，一種是沒有參數，則直接返回0-1之間的38位小數
?
??? SQL > column value format 9.99999999999999999999999999999999999999
??? SQL > select dbms_random.value from dual;
?
??????????????????????????????????????? VALUE
??? -----------------------------------------
????? .58983014999643548701631750396301271752
?
?
??? 第二種是加上兩個參數a、b，則返回值在a、b之間的38位小數
?
??? SQL > column value format 999.999999999999999999999999999999999999
??? SQL > select dbms_random.value(100,500) value from dual;
?
??????????????????????????????????????? VALUE
??? -----------------------------------------
???? 412.150194612502916808701157054098274240
?
??? 注意：無論前面幾位，小數點之后都是38位
?
?
4、dbms_random.string
?
??? 這個函數必須帶有兩個參數，前面的字符指定類型，后面的數值指定位數(最大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來確定隨機數的起始點，對于相同的seed而言，隨機數的任意一次變化都將是確定的。
??? 就是說，如果在某一時刻調用了seed，之后第一次產生的隨機數是4，第二次是6，第三次是1，那么當你再次調用相同的seed之后，一次產生的隨機數還是4、6、1
?
??? seed有兩種，一種是數值型的，一種是字符型(最大長度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參數，注釋說的很清楚了：
?
??? -- 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，但是后面的那段經過實驗發現不是這么回事，每次生成的字符串都千差萬別，不知道為什么。
?
??? 在具體應用中，除了可以用來插入生成唯一的標識符外，還可以用來取表中的任意一條記錄：
?
??? select * from (select * from t2 order by sys_guid()) where rownum=1;
?
?