Re: How to generate a random number at pl/sql ?
From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 26 Apr 1999 16:53:42 GMT
Message-ID: <373199f9.21798324_at_192.86.155.100>
PROCEDURE SEED
Date: Mon, 26 Apr 1999 16:53:42 GMT
Message-ID: <373199f9.21798324_at_192.86.155.100>
A copy of this was sent to hagai sagi <hagais_at_hotmail.com> (if that email address didn't require changing) On Mon, 26 Apr 1999 18:08:57 +0200, you wrote:
>
>Hi i need to generate random numbers
>how i do it with pl/sql language ?
>
>
>Regards
>
>hagais_at_hotmail.com
>
in oracle8.0 and up, use dbms_random:
SQL> desc sys.dbms_random
PROCEDURE INITIALIZE
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEED BINARY_INTEGER IN/OUTFUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEED BINARY_INTEGER INPROCEDURE TERMINATE in 7.x, you can use:
create or replace package random
is
pragma restrict_references( random, WNDS, RNPS );
procedure srand( new_seed in number );
function rand return number; pragma restrict_references( rand, WNDS ); procedure get_rand( r OUT number ); function rand_max( n IN number ) return number; pragma restrict_references( rand_max, WNDS); procedure get_rand_max( r OUT number, n IN number );
end random;
/
create or replace package body random
is
multiplier constant number := 22695477; increment constant number := 1; "2^32" constant number := 2 ** 32; "2^16" constant number := 2 ** 16; "0x7fff" constant number := 32767; Seed number := 1; -- procedure srand( new_seed in number ) is begin Seed := new_seed; end srand; -- function rand return number is begin seed := mod( multiplier * seed + increment, "2^32" ); return bitand( seed/"2^16", "0x7fff" ); end rand; -- procedure get_rand( r OUT number ) is begin r := rand; end get_rand; -- function rand_max( n IN number ) return number is begin return mod( rand, n ) + 1; end rand_max; -- procedure get_rand_max( r OUT number, n IN number ) is begin r := rand_max( n ); end get_rand_max;
-- begin select userenv( 'SESSIONID' ) into seed from dual; end random; / Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA -- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Mon Apr 26 1999 - 18:53:42 CEST