Example: SQL table UDFs

This non-pipelined table function returns data based on a date. The RETURN statement must contain a query.

CREATE FUNCTION PROJFUNC(indate DATE) 
  RETURNS TABLE (PROJNO CHAR(6), ACTNO SMALLINT, ACSTAFF DECIMAL(5,2), 
           ACSTDATE DATE, ACENDATE DATE) 
  BEGIN 
   RETURN SELECT * FROM PROJACT 
     WHERE ACSTDATE<=indate; 
  END

The function can be invoked as:

SELECT * FROM TABLE(PROJFUNC(:datehv)) X

Non-pipelined SQL table functions are required to have one and only one RETURN statement.

This pipelined table function returns data based on a date. In addition to the column values returned by the query, it also returns an indication of whether the project has been carried over from a prior year.

CREATE FUNCTION PROJFUNC(indate DATE) 
  RETURNS TABLE (PROJNO CHAR(6), ACTNO SMALLINT, ACSTAFF DECIMAL(5,2), 
           ACSTDATE DATE, ACENDATE DATE, CARRYOVER CHAR(1)) 
  BEGIN 
    FOR C1 CURSOR FOR SELECT * FROM PROJACT 
       WHERE ACSTDATE<=indate DO 
      IF YEAR(ACSTDATE) < YEAR(indate) THEN 
        PIPE (PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE, 'Y');
      ELSE
        PIPE (PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE, 'N');
      END IF;
    END FOR;
    RETURN;
  END

The function can be invoked the same way as a non-pipelined function:

SELECT * FROM TABLE(PROJFUNC(:datehv)) X

Pipelined SQL table functions can contain any number of PIPE statements. Each PIPE statement must return a value for every result column. A RETURN statement must be executed at the end of processing. The body of a pipelined function is not limited to querying tables. It could call another program, get information from an API, query data from some other system, and then combine the results and use one or more PIPE statements to determine what row values to return as the result table.