Table Functions and Cursor Expressions by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn 01/22/2003

Editor's note: In their last article, Steven Feuerstein and Bryn Llewellyn covered native compilation of PL/SQL, CASE statements and CASE expressions, and bulk binding in native dynamic SQL. In this article, the pair introduces cursor expressions and then shows how those expressions can be utilized with table functions. (The material in this article is based on an Oracle Corporation white paper originally presented by Bryn Llewellyn at Oracle OpenWorld 2001 in San Francisco.)

Introduction

Cursor expressions (sometimes known as cursor subqueries) are an element of the SQL language. In pre-Oracle 9i they were supported in SQL and by certain programming environments but not by PL/SQL. Oracle 9i introduces PL/SQL support for cursor expressions. For example, a cursor expression can now 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.

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Table functions were also supported (in rudimentary form) in pre-Oracle 9i, but a number of major enhancements have been made at Oracle 9i. A table function can now be written to deliver rows in pipeline fashion as soon as they're computed, dramatically improving response time in a "first-rows" scenario. A function 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 practical to implement the ETL (Extraction, Transformation, Load) phase of data warehouse applications in PL/SQL. (A pre-Oracle 9i table function caused serialization.)

Let's take a look first at cursor expressions.

Manipulating Cursor Expressions in PL/SQL

Consider the following task: List the department names, and, for each department, list the names of the employees in that department. It can be simply implemented by a classical sequential programming approach, as shown in Example 1.

Example 1. Use of nested FOR loops.

begin
   for department in (select department_id, department_name
                          from departments
                      order by department_name)
   loop
      show (department.department_name);

      for employee in (select employee_id, last_name
                           from employees
                          where department_id = department.department_id
                       order by last_name)
      loop
         show (employee.last_name);
      end loop;
   end loop;
end;

A note on the code: Whenever two authors collaborate, they determine how best to share the work and present their contributions. We have two different formatting styles: Steven prefers to capitalize key and reserved words, while lowercasing everything else; Bryn likes to present everything in lowercase. As Bryn wrote the vast majority of the code for this article, we'll use his preferred style.

The CURSOR expression, introduced in Oracle 8i but not available within PL/SQL, allows you to more concisely express the desired result set in a single query, such as:

select
  department_name,
  cursor (
     select last_name
     from employees e
     where e.department_id = d.department_id
     order by last_name
  ) the_employees
  from departments d
  order by department_name;

Now with Oracle 9i, you can take advantage of the CURSOR expression in your PL/SQL; Example 2 shows a rewrite of the original nested FOR loop using these expressions. Though this approach requires more lines of code, and is arguably less easy to proofread than the sequentially programmed implementation, it has this advantage: There's only one SQL statement, and so it can be optimized more effectively than (what the SQL engine sees as) two unconnected SQL statements. Table 1 below presents an explanation of the more interesting code lines in Example 2.

Example 2. Use of CURSOR expression to retrieve all data in a single query.

 1  declare
 2      cursor the_departments is
 3        select
 4            department_name,
 5            cursor (
 6                     select last_name
 7                       from employees e
 8                       where e.department_id = d.department_id
 9                       order by last_name
10                   )
11          from departments d
12          where department_name in ( 'executive', 'marketing' )
13          order by department_name;
14
15     v_department_name       departments.department_name%type;
16     the_employees           sys_refcursor;
17
18     type employee_last_names_t is table of employees.last_name%type
19        index by binary_integer;
20
21     v_employee_last_names   employee_last_names_t;
22  begin
23     open the_departments;
24
25     loop
26        fetch the_departments into v_department_name, the_employees;
27        exit when the_departments%notfound;
28
29        show (v_department_name);
30
31        fetch the_employees bulk collect into v_employee_last_names;
32
33        for indx in v_employee_last_names.first .. V_employee_last_names.last
34        loop
35           show (v_employee_last_names (indx));
36        end loop;
37     end loop;
38
39     close the_departments;
40     end;

Table 1. Explanation of code in Example 2.