Return values from an SPL function

SPL functions can return one or more values. To have your SPL function return values, you need to include the following two parts:
  1. Write a RETURNING clause in the CREATE PROCEDURE or CREATE FUNCTION statement that specifies the number of values to be returned and their data types.
  2. In the body of the function, enter a RETURN statement that explicitly returns the values.
Tip: You can define a routine with the CREATE PROCEDURE statement that returns values, but in that case, the routine is actually a function. It is recommended that you use the CREATE FUNCTION statement when the routine returns values.

After you define a return clause (with a RETURNING statement), the SPL function can return values that match those specified in number and data type, or no values at all. If you specify a return clause, and the SPL routine returns no actual values, it is still considered a function. In that case, the routine returns a NULL value for each value defined in the return clause.

An SPL function can return variables, expressions, or the result of another function call. If the SPL function returns a variable, the function must first assign the variable a value by one of the following methods:
  • A LET statement
  • A default value
  • A SELECT statement
  • Another function that passes a value into the variable
Each value an SPL function returns can be up to 32 kilobytes long.
Important: The return value for an SPL function must be a specific data type. You cannot specify a generic row or generic collection data type as a return type.