DBA Tips Archive for Oracle |
Creating Random Numbers in PL/SQL
by Jeff Hunter, Sr. Database Administrator
Overview
The following article presents two methods for creating random numbers using PL/SQL.Method 1 is a PL/SQL implementation of the linear congruential method of generating random numbers. It is in the form of a PL/SQL package, so it should be easy add to existing applications.
Method 2 takes the seconds past midnight and converts it to a number between 0 to 32767. Although this method requires less coding, Method 1 has two advantages:
If even distribution and repeatability are not important in your application, you can use Method 2 with no problems.
- Method 2 does not produce evenly distributed results. If you map 86400 possible numbers to 32768 possible final values, some values become more likely than others
- Method 2 is not repeatable. However, given the same seed, Method 1 produces the same sequence of numbers
Method 1
Linear congruential random number generator CREATE OR REPLACE PACKAGE random IS -- Returns random integer between [0, r-1] FUNCTION rndint(r IN NUMBER) RETURN NUMBER; -- Returns random real between [0, 1] FUNCTION rndflt RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY random IS m CONSTANT NUMBER:=100000000; /* initial conditions */ m1 CONSTANT NUMBER:=10000; /* (for best results) */ b CONSTANT NUMBER:=31415821; /* */ a NUMBER; /* seed */ the_date DATE; /* */ days NUMBER; /* for generating initial seed */ secs NUMBER; /* */ -- ------------------------ -- Private utility FUNCTION -- ------------------------ FUNCTION mult(p IN NUMBER, q IN NUMBER) RETURN NUMBER IS p1 NUMBER; p0 NUMBER; q1 NUMBER; q0 NUMBER; BEGIN p1:=TRUNC(p/m1); p0:=MOD(p,m1); q1:=TRUNC(q/m1); q0:=MOD(q,m1); RETURN(MOD((MOD(p0*q1+p1*q0,m1)*m1+p0*q0),m)); END; -- --------------------------------------- -- Returns random integer between [0, r-1] -- --------------------------------------- FUNCTION rndint (r IN NUMBER) RETURN NUMBER IS BEGIN -- Generate a random NUMBER, and set it to be the new seed a:=MOD(mult(a,b)+1,m); -- Convert it to integer between [0, r-1] and return it RETURN(TRUNC((TRUNC(a/m1)*r)/m1)); END; -- ---------------------------------- -- Returns random real between [0, 1] -- ---------------------------------- FUNCTION rndflt RETURN NUMBER IS BEGIN -- Generate a random NUMBER, and set it to be the new seed a:=MOD(mult(a,b)+1,m); RETURN(a/m); END; BEGIN -- Generate initial seed "a" based on system date the_date:=SYSDATE; days:=TO_NUMBER(TO_CHAR(the_date, 'J')); secs:=TO_NUMBER(TO_CHAR(the_date, 'SSSSS')); a:=days*24*3600+secs; END; /
Method 2
Produce random # between 0 to 32767 using seconds past midnight SELECT TRUNC( (TO_NUMBER(SUBSTR(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'sssss'))/86399),-7,7))/10000000)*32767 ) random FROM dual;
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.