我想……
能不能把8i中的代码直接拿到7.3.4中去运行
可以么？呵呵
因超长，把前面的注释去掉了 ：）
CREATE OR REPLACE PACKAGE dbms_random AS
-- 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,32) <= random < power(2,32) )
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;
/
CREATE OR REPLACE PACKAGE BODY 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);
lo NUMBER;
rng NUMBER;
n NUMBER;
xstr VARCHAR2 (60) := NULL;
BEGIN
IF optx = 'u' THEN -- upper case alpha characters only
lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
ELSIF optx = 'l' THEN -- lower case alpha characters only
lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
ELSIF optx = 'a' THEN -- alpha characters only (mixed case)
lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper)
lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
ELSIF optx = 'p' THEN -- any printable characters
lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
ELSE
lo := 65; rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,60) LOOP
/* Get random ASCII character value in specified range */
n := lo + TRUNC(rng * value); -- between lo and (lo + rng -1)
/* Adjust FOR split range */
IF optx = 'A' AND n > 90 THEN
n := n+6; -- exclude ASCII characters 5B to 60
ELSIF optx = 'X' AND n > 57 THEN
n := n+7; -- exclude ASCII characters 3A to 40
END IF;
xstr := xstr||chr(n); -- Append character to string
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;
/
DROP PUBLIC SYNONYM dbms_random;
/
CREATE PUBLIC SYNONYM dbms_random FOR sys.dbms_random;
/
GRANT EXECUTE ON dbms_random TO public;
/
|
