Loads of times (well twice) I've been asked if PL/SQL has a random number function and I always answer "Erm, I dunno. Have you looked in the manuals?". Bit of a crap answer I know, but there you go.
I think Oracle® are more or less justified in not giving us a random number generator 'cos it's not really a 'relational' thing to want to do. So, just to piss them off, here's the PL/SQL code for a pseudo-random number generating package based on the standard C library code, which itself is based on the BASIC random number algorithm (I think).
This is your package header, create it first. D'oh.
 |
||
|
CREATE OR REPLACE PACKAGE random AS -- -- -- Seeds the generator with the given value PROCEDURE seed(p_value IN NUMBER); PRAGMA RESTRICT_REFERENCES(seed,WNDS,RNDS); -- -- Returns a random decimal between 0 and 1 (inclusive) FUNCTION fraction RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(fraction,WNDS,RNDS); -- -- Returns an integer between p_from and p_to (inclusive) FUNCTION value(p_from IN NUMBER,p_to IN NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(value,WNDS,RNDS); -- END random; / |
||
This is the actual code. You might want to create grants, synonyms and all that jazz.
|
||
|
CREATE OR REPLACE PACKAGE BODY random AS -- -- -- A package global to store the current random number g_random NUMBER := 1; -- -- -- Seeds the generator with the given value PROCEDURE seed(p_value IN NUMBER) IS BEGIN g_random := ROUND(p_value); EXCEPTION WHEN OTHERS THEN g_random := 32767; END seed; -- -- -- Returns a random decimal between 0 and 1 (inclusive) FUNCTION fraction RETURN NUMBER IS BEGIN BEGIN g_random := ((g_random * 214013) + 2531011)/65536; g_random := MOD(g_random,32768); EXCEPTION WHEN OTHERS THEN g_random := 32767; END; RETURN g_random/32767; END fraction; -- -- -- Returns an integer between p_from and p_to (inclusive) FUNCTION value(p_from IN NUMBER,p_to IN NUMBER) RETURN NUMBER IS l_from NUMBER := LEAST(p_from,p_to); l_to NUMBER := GREATEST(p_from,p_to); BEGIN RETURN ROUND((fraction * ((l_to - l_from) + 0.95)) + (l_from - 0.5)); END value; -- END random; / |
||
Just like all (most?) computer based random number generators, this will actually return a pre-defined sequence of numbers, as long as it's always seeded with the same value. This can be useful in some circumstances when you need 'predictable randomness', if there is such a thing. To get closer to actual randomness you should seed the generator with a random(ish) number. I usually find the current time's a good 'un. Something like 'ROUND((((hour*3600)+(minute*60)+second)/86400)*10000)' might be a good starting point. Or you could use something based on 'MOD(dbms_utility.get_time,32768)' but that doesn't guarantee not to update the database so then you wouldn't be able to use it in SQL statements (like you'd want to?). Oh well, if I was kind I'd have put something like that into the package for you, but I'm not. So you'll have to do it yourself.
Sorry.
