Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page