Table Functions and Cursor Expressions
Pages: 1, 2, 3, 4, 5
Using a Cursor Expression as an Actual Parameter to a PL/SQL Function
A cursor variable (that is, a variable of type ref cursor) points to an actual cursor, and may be used as a formal parameter to a PL/SQL procedure or function. A cursor expression
defines an actual cursor. (Both these statements are true pre-Oracle 9i.) So we'd expect that
it would be possible to invoke a PL/SQL procedure or function that has a formal
parameter of type ref cursor with a cursor expression as its actual parameter, thus:
My_Function ( cursor ( select my_col from my_tab ) )In fact, this wasn't allowed under any circumstances pre-Oracle 9i (attempts would result in the ORA-22902 exception). New in Oracle 9i, it is now allowed under certain circumstances--namely, when the function (it can't be a procedure) is invoked in a top- level SQL statement.
Given a function that can be invoked as follows:
declare
the_cursor sys_refcursor;
n number;
begin
open the_cursor for
select my_col from my_tab;
n := My_function (the_cursor);
close the_cursor;
end;it can now be called directly within the query, like this:
select 'my_function' my_function
from dual
where
my_function (
cursor (
select my_col
from my_tab ) )
= 1;or:
select 'my_function' my_function
from dual
order by
my_function (
cursor (
select my_col
from my_tab ) ); Most significantly, this syntax is now allowed in the invocation of a table function in
the FROM list of a SELECT statement, which we'll explore later in the article.
The "Young Managers" Scenario
Consider the requirement to find those managers in the employees table, the majority of
whose direct reports were hired before the manager. The algorithm depends on finding the
direct reports for each manager and comparing the number who were hired before him
with the number who were hired after him. This can be programmed straightforwardly in
PL/SQL using classical techniques. (Note that, seeking to use enhanced Oracle 9i functionality, this is implemented using a single SQL SELECT that has
a cursor subquery for the reports of a given manager.) This approach allows the
production of a report, or as is illustrated, populating a table with the results.
But suppose the requirement is more subtle: to create a VIEW to represent managers as specified, so that it can be leveraged in ad hoc queries representing the current state of the underlying data. In fact, the requirement in this scenario can be implemented in pure SQL using only SQL functions such as SUM and DECODE. There are some rules that are too complex to implement by DECODE, in which case the user could write his own function.
But this approach, though it works, feels back-to-front! Unlike the classical approach described previously, it doesn't model the simple statement of the algorithm, and is therefore hard to write and to proofread. A more comfortable approach is to define a view as follows:
create view young_managers as
select ...
from employees managers
where most_reports_before_manager(
< stuff for this manager > ) = 1;We can do this classically like so:
create view young_managers as
select ...
from employees managers
where most_reports_before_manager
(
managers.employee_id, managers.hire_date
) = 1;or by passing a cursor expression as the actual parameter to a function whose formal
parameter is of type REF CURSOR (see Example 3), giving us something more like this:
create view young_managers as
select ...
from employees managers
where most_reports_before_manager
(
cursor ( < select statement > ),
managers.hire_date
) = 1;Example 3. Using a function with a ref cursor parameter in a WHERE clause.
create or replace function Most_Reports_Before_Manager (
report_hire_dates_cur in sys_refcursor,
manager_hire_date in date )
return number
is
type report_hire_date_t is table of employees.hire_date%type
index by binary_integer;
report_hire_dates report_hire_date_t;
before integer:=0; after integer:=0;
begin
fetch report_hire_dates_cur bulk collect into report_hire_dates;
if report_hire_dates.count > 0
then
for j in report_hire_dates.first..report_hire_dates.last
loop
case report_hire_dates(j) < manager_hire_date
when true then before:=before+1;
else after:=after+1;
end case;
end loop;
end if;
case before > after
when true then return 1;
else return 0;
end case;
end Most_Reports_Before_Manager;
create or replace view young_managers as
select managers.employee_id manager_employee_id
from employees managers
where Most_Reports_Before_Manager
(
cursor ( select reports.hire_date from employees reports
where reports.manager_id = managers.employee_id
),
managers.hire_date
) = 1; The Example 3 approach isn't possible before Oracle 9i. Its advantage over the approach of using a classical function in a WHERE clause is marginal
rather than dramatic: It offers greater potential for reuse in that its logic is expressed in
terms of, and depends only on, the select list for an arbitrary SELECT, whereas the
classical approach hard-codes the SELECT. And, since there's only one SQL statement,
this can be optimized more effectively than (what the SQL engine sees as) two
unconnected SQL statements (as discussed earlier).
The dramatic benefit of the new Oracle 9i feature allowing a cursor expression as an actual parameter to a PL/SQL function comes in connection with table functions, which we'll now explore.
Table Functions: Recap
Suppose we have two schema-level types, a tuple analogous to a table row and a table of these, defined as follows:
create type lookup_row as
object ( idx number, text varchar2(20) );
create type lookups_tab as
table of lookup_row;We can then write a PL/SQL function that returns an instance of the table as shown in Example 4. While the example doesn't reflect a "real-world" scenario, it's intended to emphasize the fact that you can model arbitrarily complex logic within the function, something that can be quite the challenge in pure SQL.
Example 4. A function that returns a collection of information.
create or replace function lookups_fn return lookups_tab
is
v_table lookups_tab;
begin
/*
to extend a nested table, you must use the built-in
procedure extend, but to extend an index-by table,
you just specify larger subscripts.
*/
v_table := lookups_tab (lookup_row (1, 'one'));
for j in 2 .. 9
loop
v_table.extend;
if j = 2
then
v_table (j) := lookup_row (2, 'two');
elsif j = 3
then
v_table (j) := lookup_row (3, 'three');
elsif j = 4
then
v_table (j) := lookup_row (4, 'four');
elsif j = 5
then
v_table (j) := lookup_row (5, 'five');
elsif j = 6
then
v_table (j) := lookup_row (6, 'six');
elsif j = 7
then
v_table (j) := lookup_row (7, 'seven');
else
v_table (j) := lookup_row (j, 'other');
end if;
end loop;
return v_table;
end lookups_fn; We can then invoke it in the FROM list of a SELECT statement thus:
select *
from table (
cast ( lookups_fn()
as lookups_tab ) ); This allows a table to be synthesized by computation. For example, the function might
call Utl_File procedures (to parse data that can't be handled by the SQL*Loader utility or
by the external table feature), or it might call C routines (via the callout framework) that
access arbitrary external data sources. Or it might access database tables and perform
transformations that can't be expressed with pure SQL and SQL functions. The SELECT
statement can be used to define a view, and/or combined with other tables in the FROM
list in an arbitrarily complex SQL statement.
A table function, then, is a PL/SQL function that can be invoked in the FROM clause
of a SQL SELECT clause. We'll see later that a table function that exploits new Oracle 9i
functionality, which we expect all table functions to do, can only be invoked in the FROM
clause of a SQL SELECT clause.
