We can now use yet another approach! The complete solution can be implemented in a table function. This has the usability advantage of keeping all the logic in one place, and the performance advantage of invoking the function only once rather than once per row in the table. See code sample-5 . This was derived “mechanically” from code sample-1 simply by creating an appropriate PL/SQL table type and by creating the block as a pipelined function to return that type, substituting pipe row ( manager_employee_id ) for insert into young_managers values ( manager_employee_id ).
The function can be made more general by giving it a ref cursor input parameter and by passing in the cursor expression as the actual parameter. See code sample-6. This would allow it to be “pointed at” any table which expressed a hierarchy where both parent and child have a date.