I need to write a procedure that generates n unique numbers in range from x to y? No duplicates allowed. I know random numbers can be generated with dbms_random.value(x, y) but values are repeated.
1
-
You need to provide an upper bound on n. If n is too large, the problem may have no solution (the number of numbers that can be represented in a machine is always finite, so if n is larger than that number there is no solution). If n is still very large but not "too large" in the first sense, the problem may be solvable but with a time estimate of 9,000 years. Then: Why do you need to write a PROCEDURE? This can be done in plain SQL. Is "procedure" meant literally - is this for a class in PL/SQL, or writing procedures? Or is a plain SQL solution enough?user5683823– user56838232016-11-12 15:29:47 +00:00Commented Nov 12, 2016 at 15:29
Add a comment
|
3 Answers
Well, first off - if the numbers must be generated without duplicates then they're not truly random.
As this sounds like a class assignment I'm not going to write code for you. However, here's an approach you might try:
Create a table UNIQUE_NUMBERS with one column named UNIQUE_NUMBER of type NUMBER which is constrained to be unique. In your function, generate the number however you like then insert it into UNIQUE_NUMBERS. If it inserts properly all is well and good, the value is unique, and your function can COMMIT the insert and return that value. If there's an exception it means the value is already there, it didn't get inserted, and you need to loop back and generate random another number.
Best of luck.
answered Nov 12, 2016 at 13:11
Bob Jarvis - Слава Україні
50.2k10 gold badges81 silver badges119 bronze badges
Comments
You can generate the numbers from x to y using a CTE. Then you can sort them randomly:
with n as (
select level as n
from dual
connect by level <= ("x" - "y" + 1)
),
rand_n as (
select n + "x" - 1
from n
order by dbms_random.random
)
select *
from rand_n;
This, of course, assumes that the difference between "x" and "y" is not really huge.
1 Comment
One approach is to generate more than n numbers (for example, 10% more). The odds of having less than n distinct numbers within 1.1 * n numbers are probably so low, the expected time before running into such a situation is longer than the life of the Solar system (if n is reasonably small).
Then, with 1.1 * n random numbers, of which you fully expect that at least n will be distinct, you can select distinct and filter by rownum.