Oracle PL/SQL for DBAs

Cursors, Pipelining, and Nesting

So far, table functions may strike you as a performance booster that might help you out occasionally when you’re in a tight spot. The features described in this section, however—the use of cursors, pipelining, and nesting with table functions—are powerful enough that they might persuade you to actually tailor your code to make use of them.

Cursors

The omnipresent cursor makes an appearance in table functions as both a parameter datatype and a SQL function to allow SELECT statements to be passed right into a table function to be processed.

Pipelining

This feature allows a table function to send its results back one by one, rather than having to assemble a full result set. The effect is to allow downstream processing to begin much faster. Going back to the cancer research example I mentioned earlier, what if the function had to parse 100 results that each took three seconds? That means that any downstream processing would have to wait five minutes before it could begin. With pipelined functions, the downstream work would begin after only three seconds.

Nesting

Table functions can be nested to perform multiple tasks on data. This is especially powerful for Data Warehouse ETL work.

I think the best way to demonstrate all of these capabilities is with an example from a Data Warehouse ETL process that extracts information about work orders. The specific function I’ll show extracts components of the creation, assignment, and close date of the orders. ...