<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    Decode360's Blog

    業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

      BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
      302 隨筆 :: 26 文章 :: 82 評論 :: 0 Trackbacks

    ??? 沒事做簡單看了一下Oracle隨機函數的取法:

    ?

    ?

    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調用隨機數的方法大致有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 ;

    ?





    -The End-

    posted on 2008-10-24 10:09 decode360-3 閱讀(4480) 評論(0)  編輯  收藏 所屬分類: Oracle
    主站蜘蛛池模板: 亚洲av永久无码精品天堂久久| 亚洲精品无码久久毛片| 亚洲妇熟XXXX妇色黄| 美女羞羞喷液视频免费| 日本特黄特色免费大片| 美国毛片亚洲社区在线观看| 日韩视频在线免费观看| 国产精品亚洲片在线花蝴蝶| 国产免费观看黄AV片| 日韩色视频一区二区三区亚洲| 国产国产人免费人成免费视频| 国产精品亚洲专一区二区三区| www.91亚洲| 成人A毛片免费观看网站| 亚洲6080yy久久无码产自国产| 光棍天堂免费手机观看在线观看| 亚洲日韩激情无码一区| 无人在线观看免费高清| 亚洲白色白色永久观看| 在线观看视频免费国语| 男人扒开添女人下部免费视频| 中文字幕久久亚洲一区| 99ee6热久久免费精品6| 亚洲中文字幕无码mv| 亚洲成A∨人片天堂网无码| 国产99精品一区二区三区免费 | 久久水蜜桃亚洲av无码精品麻豆| 免费视频一区二区| 亚洲色欲色欲www| 国产女高清在线看免费观看| 一级日本高清视频免费观看| 亚洲日韩图片专区第1页| 大学生a级毛片免费观看| 久久精品免费网站网| 亚洲一区免费视频| 亚洲精品WWW久久久久久| 一级毛片aaaaaa免费看| 亚洲av午夜国产精品无码中文字| 色噜噜AV亚洲色一区二区| 亚洲一级毛片免费看| 亚洲精品乱码久久久久久下载|