Oracle PL/SQL Associative Arrays Example

I used this example in a presentation the other day, so I figured I’d post it up for anyone who is interested. This uses features that are new to Oracle 11g (such as Indexing by a VARCHAR2 key).

Sample Code showing use of Associative Arrays

declare
-- create an associative array data type
    type account_map
      is table of varchar2(40 char)
      index by varchar2(5 char);

-- the associative array we will be working with
    asset_accounts account_map; --no need to initialize

-- create a basic VARRAY with the keys we will be using
    type account_keys is varray(4) of varchar2(5 char);
    asset_account_keys account_keys := account_keys('CASH','AR','INV','PPE');

    current_key varchar2(5 char);
begin
--  add some values, notice we don't need to extend the associative array
    asset_accounts('CASH') := 'Cash or cash equivalents';
    asset_accounts('AR') := 'Accounts receivable';
    asset_accounts('INV') := 'Inventory';
    asset_accounts('PPE') := 'Property, plant and equipment';

-- If we know the keys, we can display the values:
    dbms_output.put_line('--- by direct value ---');
    dbms_output.put_line('CASH: ' || asset_accounts('CASH'));
    dbms_output.put_line('AR: '  || asset_accounts('AR'));
    dbms_output.put_line('INV: '  || asset_accounts('INV'));
    dbms_output.put_line('PPE: '  || asset_accounts('PPE'));

-- if we have a nice varray or nested table that knows the keys, we can use it
    dbms_output.put_line('--- by table of lookup keys ---');
    for i in 1..asset_account_keys.count loop
        dbms_output.put(asset_account_keys(i) || ': ');
        dbms_output.put_line(asset_accounts(asset_account_keys(i)));
    end loop;

-- if we don't know what is in there, we can discover the keys.
    dbms_output.put_line('--- by first / next discovery ---');
    current_key := asset_accounts.first;  -- 'AR' is alphabetically first

    -- we loop until we run out of keys
    while not current_key is null loop
        -- display the element for the current key
        dbms_output.put(current_key || ': ');
        dbms_output.put_line(asset_accounts(current_key));

        -- get the next key
        current_key := asset_accounts.next(current_key);
    end loop;

-- we can read them backwards too
    dbms_output.put_line('--- by last / prior discovery ---');
    current_key := asset_accounts.last;  -- 'PPE' is alphabetically last

    -- we loop until we run out of keys
    while not current_key is null loop
        -- display the element for the current key
        dbms_output.put(current_key || ': ');
        dbms_output.put_line(asset_accounts(current_key));

        -- get the previous key
        current_key := asset_accounts.prior(current_key);
    end loop;

end;
/

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)