请问oracle中怎样实现产生随机数（300分）
我在做个毕业设计，里面要产生测试数据，测试数据是一定范围的随机数，如100-200内的。请大家帮我，我看过以前的贴子，说有一个DBMS_RANDOM包，可我查了许多书，上面都没有这个包。到底怎样实现？ 问题点数：100、回复次数：13Top
1 楼zhuzhichao（竹之草）回复于 2002-03-07 10:07:07 得分 0
select dbms_random.value(100,200) from dual;Top
2 楼zhuzhichao（竹之草）回复于 2002-03-07 10:08:46 得分 0
select round(dbms_random.value(100,200)) from dual;Top
3 楼haitianj10（海之吻）回复于 2002-03-07 11:25:02 得分 0
不行啊，炎龙大哥，我用的是O8I,平台WIN2000，
这是第一条语句的错误：
select dbms_random.value(100,200) from dual
*
ERROR 位于第 1 行:
ORA-00904: 无效列名select round(dbms_random.value(100,200)) from dual
这是地二条的错误：
*
ERROR 位于第 1 行:
ORA-00904: 无效列名
是不是O8I里没这个包？请继续帮我。非常感谢！
Top
4 楼rwq_（笨笨@天外有天山外有山）回复于 2002-03-07 12:07:27 得分 20
SQL> select dbms_random.value(100,200) from dual ;
DBMS_RANDOM.VALUE(100,200)
--------------------------
186.960862
---------------------------------------------------------
我用的是ORACLE817+WIN2000，你的不行？不会吧！
你用select object_name,object_type from dba_objects
where object_name like '%DBMS_RANDOM%'查一下有没有这个包！Top
5 楼haitianj10（海之吻）回复于 2002-03-07 12:33:07 得分 0
我用的是8。1。6的，没有这个包。怎么办？帮帮我好吗？我会给你们每人200分。谢谢！Top
6 楼jornk（咸鱼）回复于 2002-03-07 12:38:12 得分 0
基于sysdate，自己写一个简单的random函数。
我的O8.1.6也没有这个包Top
7 楼jornk（咸鱼）回复于 2002-03-07 12:39:37 得分 0
让rwq_(风云浪子) 把sys.dbms_random包寄给你吧。Top
8 楼haitianj10（海之吻）回复于 2002-03-07 13:05:38 得分 0
行吗？rwq_(风云浪子) ，给你500分。
commander576@263.net
Top
9 楼java_jin（探索者）回复于 2002-03-07 13:26:41 得分 0
qTop
10 楼java_jin（探索者）回复于 2002-03-07 13:32:43 得分 0
我也想要jin_21@sina.com
谢谢了，风云浪子哥！！Top
11 楼xzou（亡狼补齿）回复于 2002-03-07 14:08:27 得分 60
用sys用户登陆，提交Ora81\RDBMS\ADMIN\dbmsrand.sql即可
例如：
@D:\ORACLE\Ora81\RDBMS\ADMIN\dbmsrand.sqlTop
12 楼rwq_（笨笨@天外有天山外有山）回复于 2002-03-07 14:51:43 得分 20
哈，你得把分给 xzou(亡狼补齿）啦！（dbmsrand.sql）
我干脆把代码放出来吧！
------------------------------------------------------------
Rem
Rem $Header: dbmsrand.sql 14-apr-97.15:57:11 rwessman Exp $
Rem
Rem dbmsrand.sql
Rem
Rem Copyright (c) Oracle Corporation 1997, 1998. All Rights Reserved.
Rem
Rem NAME
Rem dbmsrand.sql - RANDom number generation package
Rem
Rem DESCRIPTION
Rem This produces random numbers
Rem
Rem NOTES
Rem MODIFIED (MM/DD/YY)
Rem rjenkins 02/02/98 - faster, more precision, more options, auto init
Rem rwessman 04/14/97 - Renamed package to conform to naming standards.
Rem rwessman 04/14/97 - PL/SQL random number package
Rem rwessman 04/14/97 - Renamed from network_src/admin/random.sql.
Rem
CREATE OR REPLACE PACKAGE 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,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;
Top
13 楼rwq_（笨笨@天外有天山外有山）回复于 2002-03-07 14:51:57 得分 0
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;
/
Top
