【DBMS_RANDOM】从 DBMS_RANDOM创建脚本获得随机信息的生成方法

2020-02-06 15:52:07

1.使用desc命令查看dbms_random包的内容
sys@ora10g> desc dbms_random
PROCEDURE INITIALIZE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
VAL                            BINARY_INTEGER          IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
VAL                            BINARY_INTEGER          IN
PROCEDURE SEED
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
VAL                            VARCHAR2                IN
FUNCTION STRING RETURNS VARCHAR2
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OPT                            CHAR                    IN
LEN                            NUMBER                  IN
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
LOW                            NUMBER                  IN
HIGH                           NUMBER                  IN

2.关于该包的创建脚本
CREATE OR REPLACE PACKAGE BODY SYS.dbms_random AS
    mem        num_array;           -- big internal state hidden from the user
    counter    BINARY_INTEGER := 55;-- counter through the results
    saved_norm NUMBER := NULL;      -- unused random normally distributed value
    need_init BOOLEAN := TRUE;     -- do we still need to initialize

    -- Seed the random number generator with a binary_integer
    PROCEDURE seed(val IN BINARY_INTEGER) IS
    BEGIN
   seed(TO_CHAR(val));
    END seed;

    -- Seed the random number generator with a string.
    PROCEDURE seed(val IN VARCHAR2) IS
        junk     VARCHAR2(2000);
        piece    VARCHAR2(20);
        randval NUMBER;
        mytemp   NUMBER;
        j        BINARY_INTEGER;
    BEGIN
        need_init   := FALSE;
        saved_norm := NULL;
        counter     := 0;
        junk        := TO_SINGLE_BYTE(val);
        FOR i IN 0..54 LOOP
            piece   := SUBSTR(junk,1,19);
            randval := 0;
            j       := 1;

            -- convert 19 characters to a 38-digit number
            FOR j IN 1..19 LOOP
                randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
            END LOOP;

            -- try to avoid lots of zeros
            randval := randval*1e-38+i*.01020304050607080910111213141516171819;
            mem(i) := randval - TRUNC(randval);

            -- we've handled these first 19 characters already; move on
            junk    := SUBSTR(junk,20);
        END LOOP;

   randval := mem(54);
        FOR j IN 0..10 LOOP
            FOR i IN 0..54 LOOP

                -- barrelshift mem(i-1) by 24 digits
                randval := randval * 1e24;
                mytemp := TRUNC(randval);
                randval := (randval - mytemp) + (mytemp * 1e-38);

                -- add it to mem(i)
                randval := mem(i)+randval;
                IF (randval >= 1.0) THEN
                    randval := randval - 1.0;
                END IF;

       -- record the result
                mem(i) := randval;
            END LOOP;
        END LOOP;
    END seed;

    -- give values to the user
    -- Delayed Fibonacci, pilfered from Knuth volume 2
    FUNCTION value RETURN NUMBER IS
    randval NUMBER;
    BEGIN
        counter := counter + 1;
        IF counter >= 55 THEN

            -- initialize if needed
            IF (need_init = TRUE) THEN
                seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
                     USER || USERENV('SESSIONID'));
            ELSE
                -- need to generate 55 more results
                FOR i IN 0..30 LOOP
                    randval := mem(i+24) + mem(i);
                    IF (randval >= 1.0) THEN
                        randval := randval - 1.0;
                    END IF;
                    mem(i) := randval;
                END LOOP;
                FOR i IN 31..54 LOOP
                    randval := mem(i-31) + mem(i);
                    IF (randval >= 1.0) THEN
                        randval := randval - 1.0;
                    END IF;
                    mem(i) := randval;
                END LOOP;
            END IF;
            counter := 0;
        END IF;
        RETURN mem(counter);
    END value;

    -- Random 38-digit number between LOW and HIGH.
    FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
    BEGIN
        RETURN (value*(high-low))+low;
    END value;

    -- Random numbers in a normal distribution.
    -- Pilfered from Knuth volume 2.
    FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
        v1 NUMBER;
        v2 NUMBER;
        r2 NUMBER;
        fac NUMBER;
    BEGIN
        IF saved_norm is not NULL THEN     -- saved from last time
            v1 := saved_norm;              -- to be returned this time
            saved_norm := NULL;
        ELSE
            r2 := 2;
            -- Find two independent uniform. variables
            WHILE r2 > 1 OR r2 = 0 LOOP
                v1 := value();
                v1 := v1 + v1 - 1;
                v2 := value();
                v2 := v2 + v2 - 1;
                r2 := v1*v1 + v2*v2; -- r2 is radius
            END LOOP;      -- 0 < r2 <= 1: in unit circle
            /* Now derive two independent normally-distributed variables */
            fac := sqrt(-2*ln(r2)/r2);
            v1 := v1*fac;          -- to be returned this time
            saved_norm := v2*fac; -- to be saved for next time
        END IF;
        RETURN v1;
    END normal;

    -- Random string. Pilfered from Chris Ellis.
    FUNCTION string (opt char, len NUMBER)
        RETURN VARCHAR2 is      -- string of <len> characters
        optx char (1) := lower(opt);
        rng NUMBER;
        n    BINARY_INTEGER;
        ccs VARCHAR2 (128);    -- candidate character subset
        xstr VARCHAR2 (4000) := NULL;
    BEGIN
        IF    ptx = 'u' THEN    -- upper case alpha characters only
            ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
            rng := 26;
        ELSIF ptx = 'l' THEN    -- lower case alpha characters only
            ccs := 'abcdefghijklmnopqrstuvwxyz';
            rng := 26;
        ELSIF ptx = 'a' THEN    -- alpha characters only (mixed case)
            ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
                  'abcdefghijklmnopqrstuvwxyz';
            rng := 52;
        ELSIF ptx = 'x' THEN    -- any alpha-numeric characters (upper)
            ccs := '0123456789' ||
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
            rng := 36;
        ELSIF ptx = 'p' THEN    -- any printable char (ASCII subset)
            ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' ||
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' ||
                  'abcdefghijklmnopqrstuvwxyz' || '{|}~' ;
            rng := 95;
        ELSE
            ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
            rng := 26;          -- default to upper case
        END IF;
        FOR i IN 1 .. least(len,4000) LOOP
            /* Get random integer within specified range */
            n := TRUNC(rng * value) + 1;
            /* Append character to string */
            xstr := xstr || SUBSTR(ccs,n,1);
        END LOOP;
        RETURN xstr;
    END string;

    -- For compatibility with 8.1
    PROCEDURE initialize(val IN BINARY_INTEGER) IS
    BEGIN
   seed(to_char(val));
    END initialize;

    -- For compatibility with 8.1
    -- Random binary_integer, -power(2,31) <= Random < power(2,31)
    -- Delayed Fibonacci, pilfered from Knuth volume 2
    FUNCTION random RETURN BINARY_INTEGER IS
    BEGIN
   RETURN TRUNC(Value*4294967296)-2147483648;
    END random;

    -- For compatibility with 8.1
    PROCEDURE terminate IS
    BEGIN
   NULL;
    END terminate;

END dbms_random;
/

3.从创建过程中抽取出有关的函数和存储过程
FUNCTION value RETURN NUMBER IS
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
FUNCTION string (opt char, len NUMBER)
FUNCTION random RETURN BINARY_INTEGER IS

PROCEDURE seed(val IN BINARY_INTEGER) IS
PROCEDURE seed(val IN VARCHAR2) IS
PROCEDURE initialize(val IN BINARY_INTEGER) IS
PROCEDURE terminate IS

可以从上述抽取出来的信息中，可以快速的得到具体函数和存储过程需要的参数信息。这些信息与DESC获得的信息相结合，可以比较便捷的获得随机信息语句的语法。这也是一个自救的过程。

4.小结
这里没有给出dbms_random包的具体使用方法，Oracle使用这个包实现了随机信息的生成。
这里给出的一个思路：从源头去理解功能的实现方法，我们会得到的更多。

Good luck.

secooler
10.09.15

-- The End --