Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, February 20, 2013

Overview of Table Functions


table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example:
SELECT * FROM TABLE(table_function_name(parameter_list))
(For more information about the TABLE clause of the SELECT statement, see Oracle Database SQL Language Reference.)
A table function can take a collection of rows as input (that is, it can have an input parameter that is a nested table, varray, or cursor variable). Therefore, output from table function tf1 can be input to table function tf2, and output from tf2 can be input to table function tf3, and so on. For more information, see"Chaining Pipelined Table Functions".
To improve the performance of a table function, you can:
  • Enable the function for parallel execution, with the PARALLEL_ENABLE option.
    Functions enabled for parallel execution can run concurrently.
  • Stream the function results directly to the next process, with Oracle Streams.
    Streaming eliminates intermediate staging between processes. For information about Oracle Streams, see Oracle Streams Concepts and Administration.
  • Pipeline the function results, with the PIPELINED option.

    pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)

    Example: Creating and Invoking Pipelined Table Function
    CREATE OR REPLACE PACKAGE pkg1 AS
      TYPE numset_t IS TABLE OF NUMBER;
      FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
    END pkg1;
    /
    
    CREATE PACKAGE BODY pkg1 AS
      -- FUNCTION f1 returns a collection of elements (1,2,3,... x)
      FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
      BEGIN
        FOR i IN 1..x LOOP
          PIPE ROW(i);
        END LOOP;
        RETURN;
      END f1;
    END pkg1;
    /
    
    SELECT * FROM TABLE(pkg1.f1(5));
    
    Result:
    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
     
    5 rows selected.

No comments:

Post a Comment