The PL/SQL Associative Array – the path to untold riches

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 ?

About these ads

One thought on “The PL/SQL Associative Array – the path to untold riches

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s