Having given the matter some thought, I’ve concluded that there are two ways to fame and fortune.
The first of these is talent. For the benefit of my Colombian readership ( hello German) :
I can’t play football like Faustinio Asprilla; I can’t drive as fast as Juan Pablo Montoya; and as for Carlos Valdarama’s hair…well mine deserted me some time ago. I do have something in common with Shakira – my hips don’t lie. Unfortunately, what they say is “this waistline is the result of too many nights in the pub”.
The second way is winning the lottery. OK, so the fame thing is a bit tenuous, but from the outside looking in, I’d say it was overrated. So, never mind the fame, quiero solo mucho dinero ( I just want loads of cash) !
All of which brings me to the point. I was knocking the rust off my Java the other day, by writing a program to generate lottery numbers. Then it struck me, I’d never written a lottery program in PL/SQL. Should be fairly straightforward – generate 6 random numbers between 1 and 49, make sure that you don’t have any duplicates, then print them out in order.
It’s that last bit that’s a little problematic. After all, PL/SQL is primarily designed to write to a table and so ordering the result set is usually done by simply querying the table with an ORDER BY clause.
That’s OK most of the time, but I don’t really need a table for this program, I just need an array.
So how do you get an array of numbers to display in ascending order when you haven’t got something like the Arrays.sort method available in Java ?
The simplest way is to use one of those incredibly useful associative arrays.
set serveroutput on size 1000000
DECLARE
TYPE typ_balls IS TABLE of PLS_INTEGER INDEX BY PLS_INTEGER;
tbl_balls typ_balls;
l_idx PLS_INTEGER;
l_ball PLS_INTEGER;
l_duplicate BOOLEAN;
l_count PLS_INTEGER;
BEGIN
--
-- We need 6 numbers so...
--
FOR i IN 1..6 LOOP
l_duplicate := TRUE;
WHILE l_duplicate LOOP
--
-- Get a number between 1 and 49
--
l_ball := FLOOR( DBMS_RANDOM.VALUE(1, 50));
IF tbl_balls.COUNT > 0 THEN
--
-- Make sure this number is not already chosen
--
l_idx := tbl_balls.FIRST;
LOOP
IF l_ball = tbl_balls(l_idx) THEN
--
-- Need to pick another number
-- exit out to the WHILE loop
--
l_duplicate := TRUE;
EXIT;
ELSE
l_duplicate := FALSE;
--
-- Use the number itself as the array
-- index rather than the loop variable
--
l_idx := tbl_balls.NEXT(l_idx);
END IF;
EXIT WHEN l_idx IS NULL;
END LOOP;
ELSE
l_duplicate := FALSE;
END IF;
END LOOP;
tbl_balls(l_ball) := l_ball;
END LOOP;
DBMS_OUTPUT.PUT_LINE('This week your numbers are :');
l_idx := tbl_balls.FIRST;
l_count := 1;
LOOP
--
-- As the array index is the number, we'll get the results
-- in ascending order
--
DBMS_OUTPUT.PUT(tbl_balls(l_idx));
IF l_count < 5 THEN
DBMS_OUTPUT.PUT(', ');
ELSIF l_count = 5 THEN
DBMS_OUTPUT.PUT(' and ');
END IF;
l_idx := tbl_balls.NEXT(l_idx);
l_count := l_count + 1;
EXIT WHEN l_idx IS NULL;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
/
Because the array index is the same as the value of the array element, we’re able to get PL/SQL to loop through the array in ascending order. This makes it so much easier to fill in the lottery form.
Now all I need to do is sit back and work out which tropical island I’m going to buy with the winnings. A foolproof and cunning plan. I ask you, what could possibly go wrong ?
Share this:
The Anti-Kyte 
jajajajajaja
well…let me to prepare my answer…
LikeLike
Hi,
thanks for your elegant code, can you please help me to get the outputs from your code getting inserted into a table which has 6 columns to hold the results?
Thx
LikeLike
Mansky,
There’s a few ways to do this. I haven’t got a 12c database handy at the moment and there are more elegant ways of handling PL/SQL arrays in that version. However, given a results table like this :
create table lotto_results ( draw_date date primary key, -- PK column for the table to make it a bit more real-world ball1 integer, ball2 integer, ball3 integer, ball4 integer, ball5 integer, ball6 integer ) /…even in 11g, we can adjust the code so…
set serveroutput on size 1000000 declare type typ_balls is table of pls_integer index by pls_integer; tbl_balls typ_balls; l_idx pls_integer; l_ball pls_integer; l_duplicate boolean; l_count pls_integer; begin -- -- We need 6 numbers so... -- for i in 1..6 loop l_duplicate := true; while l_duplicate loop -- -- Get a number between 1 and 49 -- l_ball := floor( dbms_random.value(1, 50)); if tbl_balls.count > 0 then -- -- Make sure this number is not already chosen -- l_idx := tbl_balls.first; loop if l_ball = tbl_balls(l_idx) then -- -- Need to pick another number -- exit out to the WHILE loop -- l_duplicate := true; exit; else l_duplicate := false; -- -- Use the number itself as the array -- index rather than the loop variable -- l_idx := tbl_balls.next(l_idx); end if; exit when l_idx is null; end loop; else l_duplicate := false; end if; end loop; tbl_balls(l_ball) := l_ball; end loop; merge into lotto using dual on ( draw_date = trunc(sysdate)) when not matched then insert(draw_date) values(trunc(sysdate)); dbms_output.put_line('This week your numbers are :'); l_idx := tbl_balls.first; l_count := 1; loop -- -- As the array index is the number, we'll get the results -- in ascending order -- dbms_output.put(tbl_balls(l_idx)); execute immediate 'update lotto set ball'||l_count||' = '||tbl_balls(l_idx); if l_count < 5 then dbms_output.put(', '); elsif l_count = 5 then dbms_output.put(' and '); end if; l_idx := tbl_balls.next(l_idx); l_count := l_count + 1; exit when l_idx is null; end loop; dbms_output.new_line; end; /If you then select from the table…
HTH,
Mike
LikeLike
Excellent Solution.
Thank you, Mike!
LikeLike