Press "Enter" to skip to content

Generating Random Data Problem

Generating Random Data Problem

You need to generate random numbers to simulate real-world events that do not follow a discernible
pattern.

Solution

Use the Oracle built-in PL/SQL package DBMS_RANDOM. The RANDOM function returns an integer in the range
[-231,231) (-231 can be returned, but 231 will not), and the VALUE function returns a decimal number in the
range [0,1) with 38 digits of precision.
For example, the merchandising department wants to lower pricing below list price on catalog items
on a daily basis to potentially stimulate sales from customers who perceive a bargain when the price is at
least 10 percent below list price. However, the merchandising analysts want to vary the discount from 10
percent to 20 percent on a random basis. To do this, first create a DAILY_PRICE column in the
PRODUCT_INFORMATION table as follows:
alter table product_information add (daily_price number);
Next, use the DBMS_RANDOM.V
ALUE function to adjust the DAILY_PRICE to a value between 10 percent
and 20 percent below the list price:
update product_information
set daily_price =
round(list_price*(0.9-(dbms_random.value*0.1)))
;
288 rows updated.

Here is the query to retrieve the calculated daily price:

select product_id, list_price, daily_price
from product_information
;
PRODUCT_ID LIST_PRICE DAILY_PRICE
———————- ———————- ———————-
1772 456 380
2414 454 379
2415 359 293
2395 123 99
1755 121 104
2406 223 200
2404 221 182
. . .
Running the UPDATE statement a second time will adjust the prices randomly again, with a different
discount for each product, but still ranging from 10 to 20 percent off:
update product_information
set daily_price =
round(list_price*(0.9-(dbms_random.value*0.1)));
select product_id, list_price, daily_price
from product_information;
PRODUCT_ID LIST_PRICE DAILY_PRICE
———————- ———————- ———————-
1772 456 383
2414 454 378
2415 359 319
2395 123 111
1755 121 107
2406 223 193
2404 221 184
. . .
The random number value returned is multiplied by 0.1 (10 percent), subtracted from 0.9 (90
percent of the list price is 10 percent off), resulting in a discount of between 10 and 20 percent. The final

result is rounded to the nearest dollar.