Using subtypes to work with string-indexed collections more easily

Question:
I have started working with multi-level and string-indexed collections. I very much like the way these features can simply the code I need to write to manipulate complex structures. But sometimes I get really confused trying to remember which data is used in which index. For example, in one program, I needed to query and keep track of some key employee information by department, using the names instead of numbers for indexes (in our company, last names of employees are unique!). So I wrote the code below, but got really confused when it was time to assign the record to the right place in my complex collection. (see "?????"). I finally figured it out, but I wonder if there is something I could change in my code to avoid the confusion. —Alejandra
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE );

l_emp_rec key_info_rt;

TYPE employees_t IS TABLE OF key_info_rt
INDEX BY VARCHAR2 (100);

TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY VARCHAR2 (100);

l_emp_info emps_in_department_t;
BEGIN
FOR temp_rec IN (SELECT d.department_name, e.last_name, e.hire_date
, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id)
LOOP
l_emp_rec.hire_date := temp_rec.hire_date;
l_emp_rec.salary := temp_rec.salary;
/*
Now I need to put the record into the right location in my
two-level collection. But which name do I put where?
*/
?????
END LOOP;
END;
Answer:
I am glad to see you are taking advantage of these fascinating collection features. I, too, have found many ways to apply multi-level and string-indexed collections. And I, too, have sometimes gotten very confused about which string should be used in which level of the collection.
A hint to the solution, however, is right there in your code, in the user-defined record you created: use %TYPE instead of hard-coded declarations, VARCHAR2(100).
To demonstrate, I have rewritten the two TYPE statements for the collections to remove the VARCHAR2(100) usages (in bold) and put in their place a %TYPE against the appropriate column.
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE );

l_emp_rec key_info_rt;

TYPE employees_t IS TABLE OF key_info_rt
INDEX BY employees.last_name%TYPE;

TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;

l_emp_info emps_in_department_t;
Notice that this code now "self-declares" exactly what kind of data is being used for the index. You can then use this declaration as a guide to writing the correct code.
When you are first working with multi-level collections, it is best to take things a step at a time. The first step was to declare the index type very clearly.
The next step in assigning the record to the right location in the collection is to figure out the correct order in which to supply the names.
You should work from left to right. Start by writing down the name of the variable:
One problem with this code is that I am handling a specific, anticipated exception within WHEN OTHERS. This is generally a bad idea. If you know an exception might be raised, you should have a separate WHEN section for that error.
l_emp_info
It is a collection (never mind for the moment) that it is a collection of other collections. This means that you need to provide an index value to specify a particular element in the collection. Looking back at the declaration of this collection:
TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;
it is now clear that you need to provide a department name. So I then have:
l_emp_info (temp_rec.department_name)
That single element is actually another collection - each element of which, in turn, is a record containing hire date and salary. The index for this inner collection is the employee last name, so I add that information:
l_emp_info (temp_rec.department_name) (temp_rec.last_name)
and then I can assign the record to this element:
l_emp_info (temp_rec.department_name) (temp_rec.last_name)
:= l_emp_rec;
And that, Alejandra, is the assignment you need to write:
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE
);

TYPE employees_t IS TABLE OF key_info_rt
INDEX BY employees.last_name%TYPE;

TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;

l_emp_info emps_in_department_t;
BEGIN
FOR emp_rec IN (SELECT d.department_name, e.last_name, e.hire_date
, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id)
LOOP
l_emp_info (emp_rec.department_name) (emp_rec.last_name)
.hire_date := emp_rec.hire_date;
l_emp_info (emp_rec.department_name) (emp_rec.last_name)
.salary := emp_rec.salary;
END LOOP;
END;
I hope you will agree that by using the %TYPE attribute in the INDEX BY clause of the collection declarations, I was much more easily able to sort out which string to use where.
Often when working with multi-level collections, you will want to iterate through all the values, for example to display the contents. In case you are wondering how you might achieve this, here is the code I would write to display each of the employees in each of the departments (a loop within a loop):
DECLARE
.... same declaration section as above
BEGIN
.... same code populating the l_emp_info collection ....

DECLARE
l_dept_name departments.department_name%TYPE;
l_emp_name employees.last_name%TYPE;
BEGIN
l_dept_name := l_emp_info.FIRST ();

WHILE (l_dept_name IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (CHR (10) || l_dept_name);
l_emp_name := l_emp_info (l_dept_name).FIRST ();

WHILE (l_emp_name IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line
( ' '
|| RPAD (l_emp_name, 15)
|| TO_CHAR (l_emp_info (l_dept_name)
(l_emp_name).hire_date
, 'dd-Mon-yyyy'
)
|| ' '
|| l_emp_info (l_dept_name) (l_emp_name).salary
);
l_emp_name := l_emp_info (l_dept_name).NEXT (l_emp_name);
END LOOP;

l_dept_name := l_emp_info.NEXT (l_dept_name);
END LOOP;
END;
END;
A final point regarding declarations of strings:
I generally work hard to avoid ever using hard-coded VARCHAR2(N) declarations in my application code. Instead, I rely on %TYPE whenever possible, and if that is not possible (I don't have a column to reference), then I use a SUBTYPE declaration to create my own application-specific type. I then reference that subtype whenever I need to declare a variable of that type.
For example, when I want to declare a string to be "the largest size possible in PL/SQL," I will not do this:
l_big_one VARCHAR2(32767);
I don't want to hard-code that length, because I anticipate the day when Oracle will extend the maximize size of a string.
Instead, I create a subtype in a central, generic package:
PACKAGE plsql_limits
IS
/* Maximum size for VARCHAR2 in PL/SQL */
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);

/* Maximum size for VARCHAR2 in database */
SUBTYPE dbmaxvarchar2_t IS VARCHAR2 (4000);
END plsql_limits;
Now my variable can be declare simply as:
l_big_one plsql_limits.maxvarchar2_t;
Much better!