Table-valued functions are functions that can be executed within the FROM clauses of SELECT statements. These functions return collections that can be converted into row or column data by using TABLE clauses. Then, the converted data can be processed in SQL statements.

Scenarios

Table-valued functions are suitable for the following scenarios:

  • Merging data tables in sessions, such as using an SQL statement to merge data that is stored in different tables.
  • Constructing datasets by means of programming, and then passing the datasets to clients in a specific format. You can use table-valued functions to generate constructed datasets without constructing relational tables.
  • Improving the performance of parallel queries by using pipelined functions. A table-valued function can serialize the query it belongs to, which can improve the query performance.

Create a table-valued function

  1. The RETURN type of the function must be defined as the collection type. Typically, the collection is a nested table or a variable array. It can also be an associated array.
    Note The type must be defined at the database level by using the CREATE TYPE statement. For pipelined functions, the type can also be defined in the package.
  2. All parameters of the function must be in IN mode and compatible with SQL statements. For example, parameters of the RECORD type are not allowed in the function, because they are not allowed in SQL statements.
  3. The call to the function is embedded in the TABLE clause.

Examples

Create a table-valued function
  1. Create a nested table. 
    CREATE OR REPLACE TYPE polar_strings IS TABLE OF VARCHAR2 (100);
  2. Create a table-valued function that returns the nested table type. 
    CREATE OR REPLACE FUNCTION get_random_str (count_in IN INTEGER)
RETURN polar_strings
IS
item   polar_strings := polar_strings ();
BEGIN
item.EXTEND (count_in);
FOR i IN 1 .. count_in
LOOP
item (i) := DBMS_RANDOM.string ('u', 10);
END LOOP;
RETURN item;
END;
  3. Invoke the table-valued function in PL/SQL to generate strings. 
    DECLARE
item   polar_strings := get_random_str (5);
BEGIN
FOR i IN 1 .. item.COUNT
LOOP
DBMS_OUTPUT.put_line (item (i));
END LOOP;
END;
    The following output is returned: 
    CIDKUKWNMV
GRSNSGJULU
XXCMTMLYUI
YWQDIMNEZA
BHTWWLCGFN
Invoke the table-valued function.
  • Invoke the get_random_str table-valued function. The parameter value is set to 5, which specifies to return five random strings. 
    SELECT rs.COLUMN_VALUE my_string FROM TABLE (get_random_str (5)) rs
    The following output is returned: 
     my_string
------------
 JAFSOSYOUA
 VNWSAAAHNA
 MAEDHVHLIU
 PRWUJLPKZJ
 MWZKQZKQJZ
(5 rows of records)
  • Invoke the get_random_str function that contains the explicitly declared count_in parameter value. It specifies to return five random strings. 
    SELECT COLUMN_VALUE my_string FROM TABLE (get_random_str (count_in => 5))
    The following output is returned: 
    my_string
------------
 TRHYTVPPOU
 DJFDIYAYAF
 BKJOYQFAJR
 YCIIBEFSVT
 OYCUDMUDMX
(5 rows of records)
  • Invoke the get_random_str function that returns five random strings. Then, SUM and AVG functions are invoked to return the total and average lengths of the five random strings. 
    SELECT SUM (LENGTH (COLUMN_VALUE)) total_length,
       AVG (LENGTH (COLUMN_VALUE)) average_length
  FROM TABLE (get_random_str (5))
    The following output is returned: 
     total_length |   average_length
--------------+---------------------
           50 | 10.0000000000000000
(1 row of records)