Sociable PL/SQL – associative arrays indexed by VARCHAR2

PL/SQL tables are a really good way of array processing in PL/SQL. In 99% of cases, using a table indexed by PLS_INTEGER works a treat, but what about those times when life would be that bit easier if you could index by VARCHAR2 ?

I was recently presented with one such instance, courtesy of some unconventional table design by now long-departed wise-men.
These gurus had decided to use a synthetic key on a reference table…except the natural key was still in the table….but the actual primary key was half of the natural key plus the synthetic key.

Oh how I would’ve loved to meet these ancients and received the benefit of their wisdom, with the aid of a spot-light, some gaffer tape…and a very sharp stick.

This is a theme I will return to in more detail in a future post ( yep, the 10 things that really wind me up post, coming after the quick ‘n’ dirty tuning post I promised a couple of months ago).

Anyway, back to the PL/SQL table madness.

In this example, we have a table called COUNTRIES – not very original, but hey, the pub’s open so we haven’t got time to hang around.

CREATE TABLE countries(
code VARCHAR2(3),
country VARCHAR2(50))
/

The COUNTRY table has the following records :-


SQL> select * from countries;
CODE COUNTRY
---- -----------------------
AUS AUSTRALIA
BEL BELGIUM
FRA FRANCE
GBR UNITED KINGDOM
GER GERMANY
JPN JAPAN
ITA ITALY
USA UNITED STATES
NZL NEW ZEALAND


9 rows selected.

Now, we have a situation where we want to use the country code for three of those countries, we know which three we want to get. But we then want to use them programatically elsewhere without having to faff about working out which order they are in in our array….

--
-- associate.sql
-- Script to demonstrate a PL/SQL associative array 
-- indexed by something other  than a number.
--
set serveroutput on
DECLARE
   TYPE typ_country_codes IS TABLE OF countries.code%TYPE 
      INDEX BY countries.country%TYPE;
   tbl_country_codes typ_country_codes;
   --
   -- In this instance, we want all the English speaking 
   -- countries... except for the USA
   -- To paraphrase the colonel in Apocalypse Now - 
   -- "Yankee don't play cricket !"
   --
   CURSOR c_countries IS
      SELECT code, country
      FROM countries
      WHERE country in ('AUSTRALIA', 'NEW ZEALAND', 
         'UNITED KINGDOM');
BEGIN
   FOR r_countries IN c_countries LOOP
      -- 
      -- assign the code to the table using the value of 
      -- the country column as the index
      --
      tbl_country_codes(r_countries.country) := 
         r_countries.code;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE( 'Country code for Australia is '
      ||tbl_country_codes('AUSTRALIA'));
   DBMS_OUTPUT.PUT_LINE( 'Country code for New Zealand is '
      ||tbl_country_codes('NEW ZEALAND'));
   DBMS_OUTPUT.PUT_LINE( 'Country code for United Kingdom is '
      ||tbl_country_codes('UNITED KINGDOM'));
END;
 /

And that’s stumps.
Please let me know if you find a less tortuous circumstance in which you find this useful.

About these ads

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