Oracle and PL/SQL Recipes: A Problem-Solution Approach

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, ...