Table Functions and Cursor Expressions

Listen Print

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

Order By Versus Cluster By

This alternative syntax is also allowed:

create or replace function my_fn ( 
      p_input_rows in my_types.cur_t )
  return my_types.items_tab
  pipelined
  order p_input_rows by (c1)
  parallel_enable
    ( partition p_input_rows by range (c1) )
is...

This means that those rows that are delivered to a particular slave as directed by partition... by will be locally sorted by that slave, thus parallelizing the sort. Therefore, there should be no ORDER BY in the SELECT used to invoke the table function. (To have one would subvert the attempt to parallelize the sort.) Thus it's natural to use the range option together with the order by option. This will be slower than cluster by, and so should be used only when the algorithm depends on it.

Note: The cluster... by construct can't be used together with the order... by in the declaration of a table function. This means that an algorithm that depends on clustering on one key, c1, and then on ordering within the set row for a given value of c1 by, say, c2 would have to be parallelized by using the order... by in the declaration in the table function. Here, we'd use:

create or replace function median ( 
     p_input_rows in my_types.cur_t )
  return my_types.items_tab
  pipelined
  order p_input_rows by (c1,c2)
  parallel_enable
    ( partition p_input_rows by range (c1) )
is...

The current restriction preventing using cluster... by together with order... by implies no loss of functionality, but only a missed opportunity to leverage the order N sort.

Caution: It's possible to design an algorithm for a table function that would deliver a different number of rows according to the degree of parallelism. The simplest example is a function that returns a table of NUMBER representing the count of the rows its input cursor delivered. A non-parallelized version would deliver just one row giving count(*) for the input table. A parallelized version would deliver N rows (where N is the degree of parallelism), the sum of whose values would give count(*) for the input table. However, this breaks the parallel query abstraction. Oracle recommends against programming this way.

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's illustrated here.

The Lookups_Fn and Mappings_Fn Example

(This example has been rewritten to return schema-level types)

Since the query syntax for an object table is rather verbose, we recap it here using a table.

create table t of lookup_row;
insert into t values ( lookup_row (  1, 'one'   ) );
insert into t values ( lookup_row (  2, 'TWO'   ) );
insert into t values ( lookup_row (  3, 'three' ) );
insert into t values ( lookup_row (  4, 'FOUR'  ) );
insert into t values ( lookup_row (  5, 'five'  ) );
insert into t values ( lookup_row (  6, 'SIX'   ) );
insert into t values ( lookup_row (  7, 'seven' ) );
insert into t values ( lookup_row (  8, 'other' ) );
insert into t values ( lookup_row (  9, 'other' ) );
insert into t values ( lookup_row ( 10, 'other' ) );
commit;

/* this is how an object query should be written */
select VALUE(a) rec from t a;

/* because it's verbose, it's convenient to define a view */
create or replace view v as
  select value(a) rec from t a;

/* test the view */
select * from v;

Now the example proper:

create type lookup_row as 
  object ( idx number, text varchar2(20) );

create type lookups_tab as table of lookup_row;

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 2 then lookup_row ( 2, 'TWO' )
        when 3 then lookup_row ( 3, 'three' )
        when 4 then lookup_row ( 4, 'FOUR' )
        when 5 then lookup_row ( 5, 'five' )
        when 6 then lookup_row ( 6, 'SIX' )
        when 7 then lookup_row ( 7, 'seven' )
        else        lookup_row ( j, 'other' )
      end;
    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;

Note the syntax of the query. Since the table function returns an object, it follows from the syntax against an object table above. Again, it's convenient to encapsulate it in a view:

create or replace view lookups as
  select value(a) rec
    from table
      (
       cast ( Lookups_Fn() as lookups_tab )
      ) a;

select * from lookups; 

create or replace function Mappings_Fn 
  ( p_input_rows in sys_refcursor )
  return lookups_tab
  pipelined
is
  v_in_row  lookup_row;

  /* always initialize an object type using a 
     type constructor or user defined constructor */
  v_out_row lookup_row := lookup_row( 1, 'x' );
begin
  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;

Note the syntax of the query. It's most compactly expressed using the views v or lookups defined earlier.

select value(b)
  from table
    (
      cast
        (
          Mappings_Fn
            (
              cursor ( select * from lookups )
            )
          as lookups_tab
        )
    ) b;

For completeness, here's how it looks without the view:

select value(b) from table
  (
    cast
      (
        Mappings_Fn
          (
             cursor
                ( select value(a) from table
                    (
                      cast ( Lookups_Fn() as lookups_tab )
                    ) a
                )
           )
         as lookups_tab
      )
  ) b;

We can create a VIEW mapped_lookups with this SELECT statement, and then access it without restriction from PL/SQL. For example:

declare
  cursor table_fn_cur is
    select * from mapped_lookups;
  rec lookup_row;
begin
  open table_fn_cur;
  loop
    fetch table_fn_cur into rec;
    exit when table_fn_cur%notfound;
    Print ( rec.idx, rec.text ); 
  end loop;
  close table_fn_cur;
end;

Note, however, that this simpler syntax doesn't work here:

for rec in ( select * from mapped_lookups ) loop

Business Benefits of Table Functions and Cursor Expressions

Cursor expressions allow encapsulation of logic for reuse 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 reusability 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, reuse, 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 data warehouse applications for implementing Extraction, Transformation, and Load operations.

Also In This Series

Inherit the Database: Oracle9i's Support for Object Type Inheritance

Substituting and Converting Object Types in a Hierarchy

New Datatypes, New Possibilities

Native Compilation, CASE, and Dynamic Bulk Binding

Multi-Level Collections in Oracle 9i

HTTP Communication from Within the Oracle Database

Oracle 9i Release 2 Developments for PL/SQL Collections

Using PL/SQL Records in SQL Statements

Fanout (DML from an autonomous transaction in the table function) adds functionality of particular interest in data warehouse 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 will be illustrated in the code samples for the next article.) This allows genuine independence between the format for the persistent storage of data and the design of the applications that 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.)

Table functions and cursor expressions expand in important new ways our ability as PL/SQL developers both to improve performance and reuse critical business logic. These features are particularly important if you rely on parallelization in your application and want to fully exploit PL/SQL-based functionality.

With Oracle 9i you can now nest collections within collections, also referred to as support for "multi-level collections." In the next article, Steven Feuerstein and Bryn Llewellyn show how to use multi-level collections and demonstrate with some examples.

This article was originally published in the May 2002 issue of Oracle Professional. The material in Feuerstein's articles--and those he cowrote with Bryn Llewellyn--are based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002, and Feuerstein's book, Oracle PL/SQL Programming, 3rd Edition.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on the subject. Steven is a Senior Technology Advisor with Quest Software and has been developing software since 1980.

Bryn Llewellyn is PL/SQL Product Manager, Database and Application Server Technologies Development Group, at Oracle Corporation Headquarters.

O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.