Table Functions and Cursor Expressions

Overview

Cursor expressions (sometimes known as cursor subqueries) are an element of the SQL language and pre-Oracle9i were supported in SQL and by certain programming environments but not by PL/SQL. Oracle9i introduces PL/SQL support for cursor expressions. For example, a cursor expression can be used in the SELECT statement used to open a PL/SQL cursor, and manipulated appropriately thereafter. It can also be used as an actual parameter to a PL/SQL procedure or function, which has great significance in connection with table functions.

Table functions were also supported (in rudimentary form) in pre-Oracle9i, but a number of major enhancements have been made at Oracle9i. A table function can now be written to deliver rows pipeline fashion as soon as they are computed, dramatically improving response time in a “first rows” scenario. It can now be written to accept a SELECT statement as input, allowing an indefinite number of transformations to be daisy-chained, avoiding the need for storage of intermediate results. And it can now be written so that its computation can be parallelized to leverage Oracle’s parallel query mechanism.

The enabling of parallel execution of a table function means that it’s now possible to leverage the power of PL/SQL in the Extract,Transform and Load (aka ETL) phase of data warehouse applications without serialization.

• Cursor Variables - Recap
• Oracle9i enhancement for bulk fetch from cursor variable assigned by native dynamic SQL
• Manipulating Cursor Expressions in PL/SQL
• Using a Cursor Expression as an actual parameter to a PL/SQL function
• "YOUNG MANAGERS" Scenario
• Table Functions - Recap
• Pipelined Table Functions - New in Oracle9i
• Piping data from one Table Function to the next - New in Oracle9i
• The "Young Managers" Scenario revisited - Table Function approach
• Fanout: Using Table Functions with side effects
• Parallelizing Table Function execution - New in Oracle9i

Syntax for Table Function based on Schema-Level Type

When a table function is written to return a schema-level type, the syntax required to invoke it is somewhat verbose. For completeness it is illustrated in code sample.

Business benefits of Table Functions and Cursor Expressions

• Cursor expressions allow encapsulation of logic for re-use in compatible query situations, giving increased developer productivity and application reliability.
• Table functions give increased functionality by allowing sets of tuples from arbitrary external data sources and sets of tuples synthesized from arbitrary computations to be invoked (as if they were a table) in the FROM list of a SELECT clause. For convenience they can be used to define a VIEW, giving new functionality.
• Table functions can be used to deliver the rows from an arbitrarily complex PL/SQL transformation sourced from Oracle tables (including therefore other table functions) as a VIEW, without storage of the calculated rows. This gives increased speed and scalability. And increased developer productivity and application reliability.
• Taking the VIEW metaphor a step further, the input parameters to the table function allow the VIEW to be parameterizable, increasing code re-usability and therefore increasing developer productivity and application reliability.
• A table function with a ref cursor input parameter can be invoked with another table function as the data source. Thus table functions can be daisy-chained allowing modular program design and hence increased ease of programming, re-use and application robustness.
• Table function execution can be parallelized giving improved speed and scalability. This, combined with the daisy-chaining feature, makes table functions particularly suitable in datawarehouse applications for implementing Extraction, Transformation and Load operations.
• Fanout (DML from an autonomous transaction in the table function) adds functionality of particular interest in datawarehouse applications.
• A table function allows data stored in nested tables to be queried as if it were stored relationally, and data stored relationally to be queried as if it were stored as nested tables. (This is illustrated in the Runner's Training Logs example scenario presented in the code sample for Multilevel Collections.) This allows genuine independence between the format for the persistent storage of data and the design of the applications which access it. (A VIEW can be defined on a table function, and INSTEAD OF triggers can be created on the VIEW to complete the picture.)