Section 7.1. Generating Random Numbers


7.1. Generating Random Numbers

One of the most common applications of random value generators is to generate random numbers. Numbers may be of many typespositive or negative, whole numbers or decimal values, and so on. Let's start with the simplest type: positive numbers .

7.1.1. Generating Positive Numbers

I need to generate a variety of account balances for our test data, all of which are numbers. My representative balance could be a whole number or a floating-point number with two places after the decimal, such as 12,345.98. For simplicity's sake, let's assume the bank does not allow overdraftsthat is, the balance cannot be negative. The DBMS_RANDOM package's VALUE function returns a positive number greater or equal to zero and less than 1 with 38 spaces after the decimal point. Here is how I can use that function to get a number.

     CREATE OR REPLACE FUNCTION get_num        RETURN NUMBER     IS        l_ret   NUMBER;     BEGIN        l_ret := DBMS_RANDOM.VALUE;        RETURN l_ret;     END; 

When this function is called, it returns a number such as the following.

     SQL> COLUMN get_num FORMAT 0.99999999999999999999999999999999999999     SQL> SELECT get_num FROM dual;                                    GET_NUM     --------------------------------------     .4617512131361177187595598121854637555 

Because the function has a purity level of WNDS (write no database state), we can also call this function directly in a SELECT statement or use it inside an INSERT.

For the moment, let's take a detour into a different kind of application. Assume that I am building a scientific application where temperature readings from a thermometer have to be captured for later analysis. I can generate a list of numbers via the following code:

     BEGIN        FOR ctr IN 1 .. 10        LOOP           DBMS_OUTPUT.put_line ('Temperature = ' || get_num || ' Celsius');        END LOOP;     END; 

The output is:

     Temperature = .72457630554564072922992665224928198851 Celsius     Temperature = .45153898247979596526993387440338586133 Celsius     Temperature = .59553753239053135052219502299103821094 Celsius     Temperature = .04579937433263277972474686173668834983 Celsius     Temperature = .16737654763541403045022512399535308709 Celsius     Temperature = .41418437394466751017150096580834486541 Celsius     Temperature = .66938830783835508007657316426304773073 Celsius     Temperature = .54439158666311298674126067789688453747 Celsius     Temperature = .01701471024660024158283343878008518868 Celsius     Temperature = .78426530713204463490011654216600338328 Celsius 

Note how the temperatures generated are clearly random. Now let's assume that we need to store these values for future analysis. I'll use the following code to store them in a table called TEMPERATURES, which looks like this:

     SQL> DESC temperatures      Name                 Null?    Type      -------------------- -------- -------      READING_ID                    NUMBER      TEMPERATURE                   NUMBER 

The program to insert random values in this table is:

     BEGIN        FOR ctr IN 1 .. 10        LOOP           INSERT INTO temperatures                VALUES (ctr, get_num);        END LOOP;     END; 

Selecting from the table later, I can see the values.

     SQL> COLUMN temperature FORMAT 0.99999999999999999999999999999999999999     SQL> SELECT *     2> FROM temperatures;     READING_ID                            TEMPERATURE     ---------- --------------------------------------              1 .6673386022472819545264095426306932156              2 .3545099922588278378720559000751088159              3 .0892425708151723467851128678015560474              4 .1599979186466772745230125089473483032              5 .4291239792186697808450265739765455686              6 .0123918319908949643217249063123839775              7 .1926386098317794990636943654441247679              8 .9631383452020016061543340586324782869              9 .7678601985171201180626445882473258794             10 .0944934248056594112683214476334211263 

This example can be modified to cover a much larger number of readings; extended to multiple sources of readings, such as different thermometers; and so on.

7.1.1.1. Controlling the precision

In the preceding example, the number generated was a positive floating-point number with 38 spaces after the decimal. A number like this may not be hugely beneficial in applications involving bank account numbers that must be whole numbers and generally of a specific number of digits. No problem! I can increase the function's usefulness by adding some modifiers. For instance, I can modify the function as follows to return a random number between 1 and 100.

     CREATE OR REPLACE FUNCTION get_num        RETURN PLS_INTEGER     IS        l_ret   PLS_INTEGER;     BEGIN        l_ret := ROUND (100 * DBMS_RANDOM.VALUE);        RETURN l_ret;     END; 

Using larger multipliers inside the function, I can also generate random numbers of higher precision.

The same principle can be extended to the generation of floating-point numbers. In the United States, account balances are generally stored in units of dollars and cents. The latter is one hundredth of a dollar, and therefore occupies two digits after the decimal point. The currency systems of many countries follow this pattern. To create an appropriate random balance in this case, you have to generate a number with two places after the decimal point. The simplest approach is to modify the function to supply a scale to the ROUND function . However, let's create a more generic utility by modifying the function to accept parameters:

     CREATE OR REPLACE FUNCTION get_num (        p_precision   IN   PLS_INTEGER,        p_scale       IN   PLS_INTEGER := 0     )        RETURN NUMBER     IS        l_ret   NUMBER;     BEGIN        l_ret := ROUND (10 * p_precision * DBMS_RANDOM.VALUE, p_scale);        RETURN l_ret;     END; 

With these arguments, I can easily obtain random numbers that fit my specific requirements.

7.1.1.2. Controlling the range

Suppose that I need to generate account balances that fall within a particular range. For example, the bank may have a minimum balance requirement, so the randomized balance would have to be above that range and likely below a certain perceived maximum number. To handle this requirement, the VALUE function is overloaded in the DBMS_RANDOM package to accept two parameters, low and high, and return a number between those values. Here is an example of using this overloading to obtain random numbers between 1,000 and 9,999:

     l_ret := DBMS_RANDOM.VALUE(1000,9999) 

The large number of digits after the decimal point in the random number generated may be rather intimidating, but I can get rid of them using the ROUND function . I can now modify our account-balance-generation function to generate within a range as shown here.

     /* File on web: get_num_1.sql */     CREATE OR REPLACE FUNCTION get_num (        p_highval   NUMBER,        p_lowval    NUMBER := 0,        p_scale     PLS_INTEGER := 0     )        RETURN NUMBER     IS        l_ret   NUMBER;     BEGIN        l_ret := ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval), p_scale);        RETURN l_ret;     END; 

Notice that this function is generic enough to handle a wide variety of random-number-generation requirements, including:

  • Generate bank account numbers, which are 10-digit whole numbers. These numbers are strictly 10-digitthat is, they can't have leading zeros.

         SQL> EXEC DBMS_OUTPUT.put_line(get_num(1000000000,9999999999))     2374929832 

  • Use the same function for account balances, which carry two places after the decimal and are between 1,000 (the bank's minimum-balance requirement) and 1,000,000 (the perceived maximum of an account balance). Because I need to generate two places after the decimal, I must specify a p_scale parameter of 2.

         SQL> EXEC DBMS_OUTPUT.put_line(get_num(1000,1000000,2))     178861.81 

  • Use the function for scientific data, such as readings from a highly sensitive thermometer used in nuclear facilities, where numbers have three places before and 10 places after the decimal point; that is, a number may be between 100.0000000000 and 999.9999999999:

         BEGIN        DBMS_OUTPUT.put_line (           get_num(100,999.9999999999,10));     END;     /     607.1872599141 

7.1.2. Generating Negative Numbers

So far I have generated only positive numbers, not negative ones. But suppose that my banking application allows overdraftsthat is, borrowing money from the accounts temporarily. In this case, the account balance can be negativea condition known as "being overdrawn." In the test data, I may want to throw in some of those accounts, too. The DBMS_RANDOM.RANDOM function allows you to do this from within PL/SQL.

7.1.2.1. The RANDOM function

The DBMS_RANDOM package provides a function that can be used to generate random whole numbers, called RANDOM, which accepts no parameters and returns a binary integer in the range -231 through 231that is, a whole number up to 10 digits long. The usage is straightforward:

     DECLARE        l_ret   NUMBER;     BEGIN        l_ret := DBMS_RANDOM.random;        DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);        l_ret := DBMS_RANDOM.random;        DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);        l_ret := DBMS_RANDOM.random;        DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);     END;     / 

The output is:

     The number generated = 865225855     The number generated = 1019041205     The number generated = -1410740185 

Note how the numbers in this particular execution contain a mixture of negative and positive integers up to 10 digits long. There is no guarantee when calling this program that you will get a negative random number. If you need to be sure that your possibly negative random number conforms to specific formats, you will want to use a combination of both the RANDOM and the VALUE functions, as shown below.

I can modify the function get_num to accept a parameter to allow for the generation of negative numbers as follows.

     /* File on web: get_num_2.sql */     CREATE OR REPLACE FUNCTION get_num (        p_highval             NUMBER,        p_lowval              NUMBER := 0,        p_negatives_allowed   BOOLEAN := FALSE,        p_scale               PLS_INTEGER := 0     )        RETURN NUMBER     IS        l_ret   NUMBER;        l_sign  NUMBER := 1;     BEGIN        IF (p_negatives_allowed)        THEN           l_sign := SIGN (DBMS_RANDOM.random);        END IF;        l_ret := l_sign *                 ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval)                      , p_scale);        RETURN l_ret;     END; 

Here I have preserved the functionality provided by my original get_num function (which accepts high and low values and the scale of the number), but have extended it to allow for the possibility of generating negative as well as positive random numbers.

7.1.3. Seeding Random Number Generation

What makes random numbers truly random? To visualize the mechanism, here is an exercise: quickly think of a number, any number. What number came to mind? That number is very likely significant to you in some way: your age, a part of your telephone number, your street address numbersomething that you are either familiar with or just happened to see at that time. That number, in other words, was not randomly selected and is, therefore, likely to be repeated or predicted.

Fine, you might say. You want a random number? I will close my eyes and punch at keys randomly on my calculator. Surely that will result in a random number.

Probably not. If you examine the number closely, you will notice that some digits are repeated next to each other. That is natural: if you hit a keysay, 9it's likely that you will hit 9 again next because your finger was hovering in the general vicinity of the 9 key.

I hope you can see, after these simple thought-exercises, that it is quite difficult for a human being to come up with a truly random number. Similarly, a machine can't just pull a number from thin air. There are several methods to achieve true randomness based on complex mathematical formulas that are beyond the scope of this book. The most common component of any random number generator is some randomizer found elsewhere, such as system time, which is guaranteed to be different between two points in time. This randomizer component, which is conceptually similar to the general area where your finger was hovering over the calculator keypad to produce a random number, is called a seed. If the seed chosen is sufficiently random, the generated number will be truly random. If the seed is constant, the random number generated may not be truly random. The pattern might be repeated and might be easy to guess. Therefore, a seed is very important in random number generation.

The DBMS_RANDOM package contains an INITIALIZE procedure that provides an initial seed that will then be used by its randomizer function. Here is an example of a call to this program.

     BEGIN        DBMS_RANDOM.initialize (10956782);     END; 

This procedure accepts a BINARY_INTEGER argument and sets that value as the seed. The larger the number of digits in this seed, the higher the degree of randomness for the generated number. You would generally want to use a number greater than five digits to achieve an acceptable degree of randomness.

Explicitly calling the INITIALIZE procedure is purely optional and is not required when you are using the DBMS_RANDOM package. By default, Oracle will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.


For true randomness, though, the number should not be a constant. A good variable for this seed is the system time, which is guaranteed to be different every time it is used within a 24-hour period; for example:

     BEGIN        DBMS_RANDOM.initialize (           TO_NUMBER (TO_CHAR (SYSDATE, 'MMDDHHMISS')));     END; 

Here I have fed the month, day, hour, minute, and second as the seed. This seed is used initially; however, if the same seed is used throughout the session, the randomness might be compromised. Hence, you will need to supply a new seed at regular intervals. This can be accomplished via the SEED procedure, which, like INITIALIZE, accepts a BINARY_INTEGER as a parameter.

     BEGIN        DBMS_RANDOM.seed (TO_CHAR (SYSDATE, 'mmddhhmiss'));     END; 

When the session does not need any further generation of new seeds, you can issue the TERMINATE procedure as follows, to stop generating new random numbers (and stop wasting CPU cycles).

     BEGIN        DBMS_RANDOM.terminate;     END; 




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net