Random number generation in Oracle
In C# I can generate random number from a string like this:
public string CreateRandomNumber(string normal)
{
string abnormal = (new Random(normal.GetHashCode())).Next().ToString();
return abnormal;
}
But I want to 开发者_Python百科do the same (kind of) operation inside the oracle database. So, how can I generate a random number from a string ?
The DBMS_RANDOM package provides a built-in random number generator.
Using the
SEED Procedures, you can pass a VARCHAR2
as the seed
, so you can skip the hash generation step.
SEED Procedures
This procedure resets the seed.
Syntax
DBMS_RANDOM.SEED (
seed IN BINARY_INTEGER);
DBMS_RANDOM.SEED (
seed IN VARCHAR2);
Take a look here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:831827028200
where he uses a combination of sys_guid
and to_number
.
There is a package in Oracle called DBMS_RANDOM, but it only takes numbers as seed. To seed it with a string, you need some way to generate a numeric hash code for the string. Something along the lines of
FUNCTION simpleHash(st varchar2) RETURN NUMBER is
n NUMBER;
s NUMBER;
BEGIN
n:=1;
s:=0;
FOR i IN 1..LENGTH(st) LOOP
s:=s+n*ASCII(SUBSTR(st,i,1));
n:=mod(n*75, 65537);
END LOOP;
END;
/
should do the trick.
精彩评论