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;
GBR UNITED KINGDOM
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.