随机数发生器: CREATE OR REPLACE package pk_rand is /***************************************************************** * Written by Malcolm Turner Dec 1999 * based on a earlier Forms3 procedure * This package returns a random number between 0 and 1 * Optionally, the range returned may be set with * set_range(lower,upper) * in which case the number returned will be an integer. * This may be over-ridden with the optional switch in this procedure * For repeatability in testing, the initial "seed" value used * may be set by the user using procedure set_seed * * * * * This package is offered freely to the Oracle community in the * hope that it will be useful. * * * Please send any comments/suggestions to * * [email protected] * * ******************************************************************/ procedure set_range(p_lower in number,p_upper in number,p_integer in varchar2 := 'Y'); procedure set_seed(p_seed in number); procedure rand(p_result out number) ; function f_rand return number; pragma restrict_references(f_rand,WNDS); end; /CREATE OR REPLACE package body pk_rand is v_range_lower number := 0; v_range_upper number := 1; v_lastval number; v_rand number; v_result number; v_integer boolean := FALSE; cursor c_seed is select to_char(sysdate,'SSSSSDDDHHSSSSS') from dual;procedure set_seed(p_seed in number) is begin v_lastval := p_seed; end;procedure set_range(p_lower in number,p_upper in number ,p_integer in varchar2 := 'Y') is begin v_range_lower := p_lower; v_range_upper := p_upper; if p_integer = 'Y' then v_integer := TRUE; else v_integer := FALSE; end if; end;procedure rand(p_result out number) is begin p_result := f_rand; end;function f_rand return number is /* Fixed values used in generation */ a number := 25214903917; c number := 11; m number := power(2,48); begin /* Compute next random number */ v_rand := ((a * v_lastval + c ) mod m ); /* Store it as seed value in next calculation */ v_lastval := v_rand; /* Get output between 0 and 1 */ v_rand := v_rand / m; /* Multiply up to required range */ if v_integer then /* Increment the range by 1 to allow for truncation */ v_result := v_rand * ((v_range_upper +1) - v_range_lower ) + v_range_lower; v_result := trunc(v_result); else /* Just multiply to expand the answer */ v_result := v_rand * (v_range_upper - v_range_lower ) + v_range_lower; end if; return v_result; end;/* And now the initialisation code */ begin open c_seed; fetch c_seed into v_lastval; close c_seed; end; / 借花献佛,希望有用!:)
to_char(sysdate,'MMDDHHMISS')只能到秒,不知道什么函数可以精确到毫秒
谢谢
建立一个 sequence 做一个函数,得到唯一的数值即可。
CREATE OR REPLACE package pk_rand
is
/*****************************************************************
* Written by Malcolm Turner Dec 1999
* based on a earlier Forms3 procedure
* This package returns a random number between 0 and 1
* Optionally, the range returned may be set with
* set_range(lower,upper)
* in which case the number returned will be an integer.
* This may be over-ridden with the optional switch in this procedure
* For repeatability in testing, the initial "seed" value used
* may be set by the user using procedure set_seed
* *
* *
* This package is offered freely to the Oracle community in the
* hope that it will be useful.
* *
* Please send any comments/suggestions to
*
* [email protected]
*
*
******************************************************************/
procedure set_range(p_lower in number,p_upper in number,p_integer in varchar2 := 'Y');
procedure set_seed(p_seed in number);
procedure rand(p_result out number) ;
function f_rand return number;
pragma restrict_references(f_rand,WNDS);
end;
/CREATE OR REPLACE package body pk_rand
is
v_range_lower number := 0;
v_range_upper number := 1;
v_lastval number;
v_rand number;
v_result number;
v_integer boolean := FALSE;
cursor c_seed is
select to_char(sysdate,'SSSSSDDDHHSSSSS')
from dual;procedure set_seed(p_seed in number)
is
begin
v_lastval := p_seed;
end;procedure set_range(p_lower in number,p_upper in number
,p_integer in varchar2 := 'Y')
is
begin
v_range_lower := p_lower;
v_range_upper := p_upper;
if p_integer = 'Y' then
v_integer := TRUE;
else
v_integer := FALSE;
end if;
end;procedure rand(p_result out number)
is
begin
p_result := f_rand;
end;function f_rand return number is
/* Fixed values used in generation */
a number := 25214903917;
c number := 11;
m number := power(2,48);
begin
/* Compute next random number */
v_rand := ((a * v_lastval + c ) mod m );
/* Store it as seed value in next calculation */
v_lastval := v_rand;
/* Get output between 0 and 1 */
v_rand := v_rand / m;
/* Multiply up to required range */
if v_integer then
/* Increment the range by 1 to allow for truncation */
v_result := v_rand * ((v_range_upper +1) - v_range_lower ) +
v_range_lower;
v_result := trunc(v_result);
else
/* Just multiply to expand the answer */
v_result := v_rand * (v_range_upper - v_range_lower ) + v_range_lower;
end if;
return v_result;
end;/* And now the initialisation code */
begin
open c_seed;
fetch c_seed
into v_lastval;
close c_seed;
end;
/
借花献佛,希望有用!:)