Table Functions and Cursor Expressions
Pages: 1, 2, 3, 4, 5

Pipelined Table Functions: New in Oracle 9i

The preceding functionality is available pre-Oracle 9i. However, it has the limitation that the function must run to completion, storing all the rows it computes in the PL/SQL table before even the first row can be delivered. (There are other limitations, discussed shortly.) Oracle 9i introduces the pipelined construct that allows the procedure to be rewritten as shown in Example 5.

Example 5. A pipelined function.

create or replace function lookups_fn return lookups_tab
  pipelined
is
  v_row lookup_row;
begin
  for j in 1..10
  loop
    v_row := 
      case j
        when 1 then lookup_row ( 1, 'one' )
        ...
        when 7 then lookup_row ( 7, 'seven' )
        else        lookup_row ( j, 'other' )
      end;
    pipe row ( v_row );
  end loop;
  return;
end lookups_fn;

Thus each row is delivered as soon as it's ready, so that the response time characteristics of a table function are symmetrical with those of a row source based on a table scan or an index scan. (For performance, the PL/SQL runtime system delivers the rows from a pipelined table function in batches.)

Note: The procedure body now mentions only rows (that is, not the table), and the table is just implied by the return type. (For elegance, the IF construct has been replaced with the new CASE formulation.) The same syntax as shown earlier can be used to select from the table function, but it can now be simplified thus:

select * from table ( Lookups_Fn );

The invocation will be written Lookups_Fn() in the following to emphasize its status as a function.

Oracle 9i also introduces the possibility to create a table function that returns a PL/SQL type (that is, one that's defined in a PL/SQL block rather than at schema level), as shown in Example 6.

Example 6. A table function that returns a PL/SQL type.

create or replace package My_Types is
  type lookup_row is record ( idx number, text varchar2(20) );
  type lookups_tab is table of lookup_row;
end My_Types;

create or replace function Lookups_Fn return My_Types.lookups_tab
  pipelined
is
  v_row My_Types.lookup_row;
begin
  for j in 1..10
  loop
    case j
      when 1 then v_row.idx := 1; v_row.text := 'one';
      ...
      when 7 then v_row.idx := 7; v_row.text := 'seven';
      else        v_row.idx := j; v_row.text := 'other';
    end case;
    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;

In the limit, a PL/SQL type may be defined in the declare section of an anonymous block and hence have no persistence. However, to be useful in connection with table functions, the PL/SQL types must be declared in a package, and so when discussing table functions, they're usually referred to as package-level types (in contrast to schema-level types).

Note: A table function that returns a package-level type must be pipelined. Moreover, the simpler SELECT syntax (without the CAST) must be used.

Piping Data from One Table Function to the Next

Also new to Oracle 9i, a table function may now be defined with an input parameter of type ref cursor and invoked with a cursor expression as the actual parameter. Consider the code shown in Example 7.

Example 7. A transformative pipelined function.

create or replace function Mappings_Fn ( p_input_rows in sys_refcursor )
  return My_Types.lookups_tab
  pipelined
is
  v_in_row  My_Types.lookup_row;
  v_out_row My_Types.lookup_row;
begin
/*
  The following causes...
  PLS-00361: IN cursor 'P_INPUT_ROWS' cannot be OPEN'ed
  (The system opens the cursor on invoking the function.)
*/
--open p_input_rows;
  loop
    fetch p_input_rows into v_in_row;
    exit when p_input_rows%notfound;

    case v_in_row.idx
      when 1 then v_out_row.idx :=  1*2; v_out_row.text := 'was one';
      when 2 then v_out_row.idx :=  2*3; v_out_row.text := 'was TWO';
      when 3 then v_out_row.idx :=  3*4; v_out_row.text := 'was three';
      when 4 then v_out_row.idx :=  4*5; v_out_row.text := 'was FOUR';
      when 5 then v_out_row.idx :=  5*6; v_out_row.text := 'was five';
      when 6 then v_out_row.idx :=  6*7; v_out_row.text := 'was SIX';
      when 7 then v_out_row.idx :=  7*8; v_out_row.text := 'was seven';
      else        v_out_row.idx :=
                        v_in_row.idx*10; v_out_row.text := 'was other';
    end case;
    pipe row ( v_out_row );
  end loop;
  close p_input_rows;
  return;
end Mappings_Fn;

Suppose t is a table that supports a select list compatible with My_Types.lookup_row. We can now invoke the table function thus:

select * 
  from table ( 
     mappings_fn ( 
        cursor ( 
         select idx, text 
         from t ) ) );

Of course, t might have been a view defined thus:

create or replace view t 
as
  select * from table ( lookups_fn() );

which implies the more compact syntax shown in Example 8.

Example 8. Passing a query as an argument.

create or replace view v as
  select *
    from table ( Mappings_Fn ( cursor ( select * from table ( Lookups_Fn() ) ) ) );

Data can be piped from one to the next of an arbitrary number of table functions daisy- chained in succession. And, due to the pipelining feature, storage of intermediate results is avoided. Table functions can thus be used to implement the Extraction, Transformation, and Load (a.k.a. ETL) operation for building a data warehouse from OLTP data. In the limit, the extraction table function would access a foreign data source as discussed earlier.

The "Young Managers" Scenario Revisited: Table Function Approach

We can now use yet another approach! The complete solution can be implemented in a table function. This has the usability advantage of keeping all the logic in one place, and the performance advantage of invoking the function only once rather than once per row in the table (see Example 9). This was derived "mechanically" simply by creating an appropriate PL/SQL table type and by creating the block as a pipelined function to return that type, substituting pipe row ( manager_employee_id ) for insert into young_managers values ( manager_employee_id ).

Example 9. Using a table function.

create or replace package My_Types is
  type employee_ids_tab is table of employees.employee_id%type;
end My_Types;

create or replace function Young_Managers_Fn
  return My_Types.employee_ids_tab
  pipelined
is
  cursor managers is
    select
      employee_id, hire_date,
      cursor (
              select hire_date
                from employees reports
                where reports.manager_id = managers.employee_id
             )
      from employees managers;

  manager_employee_id       employees.employee_id%type;
  manager_hire_date         employees.hire_date%type;
  reports                   sys_refcursor;
  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; after integer;
begin
  open managers;
  loop
    before:=0; after:=0;
    fetch managers into manager_employee_id, manager_hire_date, reports;
    exit when managers%notfound;
    fetch reports 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;
    if before > after then
      pipe row ( manager_employee_id ); end if;
  end loop;
  close managers;
  return;
end Young_Managers_Fn;

create or replace view young_managers as
  select column_value manager_employee_id from table ( Young_Managers_Fn() );

The function can be made more general by giving it a ref cursor input parameter and by passing in the cursor expression as the actual parameter (see Example 10). This would allow it to be "pointed at" any table that expressed a hierarchy where both parent and child have a date.

Example 10. Using a table function with a ref cursor Input parameter.

create or replace function Young_Managers_Fn ( managers in sys_refcursor )
  return My_Types.employee_ids_tab
  pipelined
is
  manager_employee_id       employees.employee_id%type;
  manager_hire_date         employees.hire_date%type;
  reports                   sys_refcursor;
  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; after integer;
begin
  loop
    before:=0; after:=0;
    fetch managers into manager_employee_id, manager_hire_date, reports;
    exit when managers%notfound;
    fetch reports 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;
    if before > after then
      pipe row ( manager_employee_id ); end if;
  end loop;
  close managers;
  return;
end Young_Managers_Fn;

select column_value manager_employee_id from table
 (
   Young_Managers_Fn
     (
       cursor
         (
            select
              employee_id, hire_date,
              cursor (
                        select hire_date
                          from employees reports
                          where reports.manager_id = 
                            managers.employee_id
                     )
              from employees managers
         )
     )
 );

Note: An attempt to create a view as this SELECT statement currently fails with "ORA-22902: CURSOR expression not allowed," where the exception is raised because the SELECT statement that's the argument of the CURSOR formal parameter to the table function itself has a cursor expression (a.k.a. cursor subquery). A view can be created when the SELECT statement doesn't have a cursor subquery (see the earlier Mappings_Fn example).

Pages: 1, 2, 3, 4, 5

Next Page