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 ?

Advertisements

4 thoughts on “The PL/SQL Associative Array – the path to untold riches

  1. 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

  2. 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…

    select *
    from lotto
    where draw_date = trunc(sysdate)
    /
    
    DRAW_DATE      BALL1      BALL2      BALL3      BALL4      BALL5      BALL6
    --------- ---------- ---------- ---------- ---------- ---------- ----------
    04-FEB-17          2          6         12         42         48         49
    
    

    HTH,

    Mike

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