10-5. Creating and Accessing Hash Array Collections
Problem
You want to use a single cursor to query employee data and sum the salaries across departments.
Solution
You can use two cursors—one to select all employees and the other to sum the salary grouping by department. However, you can more easily and efficiently accomplish your task by using one cursor and a hashed collection. Define your cursor to select employee data, joined with the department table. Use a hash array collection to total by department by using the INDEX BY option to index your collection based on the department name rather than an integer. The following code example illustrates this more efficient approach:
DECLARE
CURSOR driver IS
SELECT ee.employee_id, ee.first_name, ee.last_name, ee.salary, d.department_name
FROM departments d,
employees ee
WHERE d.department_id = ee.department_id;
TYPE total_type IS TABLE OF number INDEX BY departments.department_name%TYPE;
totals total_type;
dept departments.department_name%TYPE;
BEGIN
FOR rec IN driver LOOP
-- process paycheck
if NOT totals.EXISTS(rec.department_name) then -- create element in the array
totals(rec.department_name) := 0; -- initialize to zero
end if;
totals(rec.department_name) := totals(rec.department_name) + nvl (rec.salary, 0);
END LOOP;
dept := totals.FIRST;
LOOP
EXIT WHEN dept IS NULL;
DBMS_OUTPUT.PUT_LINE (to_char (totals(dept), '999,999.00') || ' ' || dept);
dept := totals.NEXT(dept);
END LOOP;
END;
When you execute this block of code, you will see the following results:
20,310.00 Accounting
58,720.00 Executive
51,600.00 Finance
6,500.00 Human Resources
19,000.00 Marketing
2,345.34 Payroll
10,000.00 Public Relations
304,500.00 Sales
156,400.00 Shipping
35,295.00 Web Developments
How It Works
The TOTAL_TYPES PL/SQL type is defined as a collection of numbers that is indexed by the department name. Indexing by department name gives the advantage of automatically sorting the results by department name.
As new elements are created, using the EXISTS method, the index keys are automatically sorted by PL/SQL. The totals are accumulated by department name as opposed to a numerical index, such as department ID, which may not be sequential. This approach has the added advantage of not requiring a separate collection for the department names.
Once the employee paychecks are processed, the dept variable is initialized with the first department name from the totals array using the FIRST method. In this example, the first department is accounting. A loop is required to process the remaining records. The NEXT method is used to find the next department name—in alphabetical order—and this process repeats until all departments are displayed.