Table Functions and Cursor Expressionsby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn
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.)
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.
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
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
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
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.
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.
|16||Use of the Oracle 9i, pre-defined weak |
|26||Our fetch statement fetches into the cursor variable. This means that it now points to cursor, which in turn identifies a result set of its own.|
|31||This cursor (|
|Use a numeric |