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

10-6. Creating and Accessing Complex Collections

Problem

You need a routine to load managers and their corresponding employees from the database and store them in one data structure. The data must be loaded in a manner such that direct reports are associated with their manager. In addition, the number of direct reports for any given manager varies, so your structure to hold the manager/employee relationships must handle any number of subordinates.

Solution

Combine records and collections to define one data structure capable of storing all the data. PL/SQL allows you to use data structures you create via the type statement as datatypes within other collections. Once your data structures are defined, use dot notation to distinguish attributes within ...