"YOUNG MANAGERS" Scenario

Consider the requirement to find those managers in the employees table, the majority of whose direct reports were hired before the manager. The algorithm depends on finding the direct reports for each manager and comparing the number who were hired before him with the number who were hired after him. This can be programmed straightforwardly in PL/SQL using classical techniques. See code sample-1. (Note that, seeking to use enhanced Oracle9i functionality, this is implemented using a single SQL SELECT which has a cursor subquery for the reports of a given manager.) This approach allows the production of a report, or as is illustrated, populating a table with the results.

But suppose the requirement is more subtle: to create a VIEW to represent managers as specified, so that it can be leveraged in ad hoc queries representing the current state of the underlying data. In fact, the requirement in this scenario can be implemented in pure SQL using only SQL functions such as SUM and DECODE. See code sample-2 . There are some rules that are too complex to implement by DECODE, in which case the user could write his own fucntion.

But the approach in sample-2, thought it works, feels back to front! Unlike sample-1, it does not model the simple statement of the algorithm, and is therefore hard to write and to proof read. A more comfortable approach is to define a view thus…

create view young_managers as
  select ...
    from employees managers
    where Most_Reports_Before_Manager( < stuff for this manager > ) = 1;

We can do this classically (see sample-3) thus…

create view young_managers as
  select ...
    from employees managers
    where Most_Reports_Before_Manager
     (
       managers.employee_id, managers.hire_date
     ) = 1;

…or by passing a cursor expression as the actual parameter to a function whose formal parameter is of type ref cursor (see sample-4) thus…

create view young_managers as
  select ...
    from employees managers
    where Most_Reports_Before_Manager
     (
       cursor ( < select hire date stuff for this manager’s reports >      ),

       managers.hire_date
     ) = 1;

The sample-4 approach is not possible before Oracle9i. Its advantage over the sample-3 approach is marginal rather than dramatic: it offers greater potential for reuse in that its logic is expressed in terms of, and depends only on, the select list for an arbitrary SELECT whereas the sample-3 approach hard-codes the SELECT; and, since there is only one SQL statement, this can be optimized more effectively than (what the SQL engine sees as) two unconnected SQL statements (as discussed above).

The dramatic benefit of the new Oracle9i feature allowing a cursor expression as an actual parameter to a PL/SQL function comes in connection with table functions, shown in sample-6. (You have to understand the sections Table Functions - Recap, Pipelined Table Functions - New in Oracle9i, and Piping data from one Table Function to the next - New in Oracle9i to understand this sample.)