Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Generating Random Values with NULLs

Remember from the discussion earlier that the requirement for the column FOLIO_ID is a little different. It needs only 50% of the values populated; the rest should be NULL. How can I achieve that result?

I will use the same probability approach with a twist; I will simply use a determination of “yes” or “no.” Generating a random number between 1 and 100 will ensure 1% probability of each number. Hence, a number below 51 will have exactly 50% probability. I can use this in a CASE statement to get the value:

    SQL> SELECT CASE
      2            WHEN DBMS_RANDOM.VALUE (1, 100) < 51
      3               THEN NULL
      4            ELSE FLOOR (DBMS_RANDOM.VALUE (1, 100))
      5         END
      6* FROM DUAL;

On line 2, I check if the number generated is less than 51. If so, I return NULL. Because the probability of a number under 51 is 50%, I have NULLs occurring 50% of the time, as well. In the other 50%, I have generated a random number between 1 and 100 to be used as a FOLIO_ID.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page