## 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.

#### Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like: