OUT parameters and statement-local variables (SLVs)

You use OUT parameters to pass values from the called function to the caller. The SPL, C, or Java™ called function sets the value of this parameter and returns a new value through the parameter. Any or all arguments of a UDR can be an OUT parameter. You cannot use OUT parameters to pass values to the called function; OUT parameters are passed as NULL to the UDR.

The syntax for creating a UDR with OUT parameters is:

CREATE FUNCTION udr ([IN/OUT] arg0 datatype0, ...,
                     [IN/OUT] argN datatypeN)
                     RETURNING returntype;
...
END FUNCTION;

By default, a parameter is considered an IN parameter unless you define it as an OUT parameter by specifying the OUT keyword.

For example, the following CREATE FUNCTION statement specifies one IN parameter, x, and two OUT parameters, y and z.

CREATE FUNCTION my_func(x INT, OUT y INT, OUT z INT) 
RETURNING INT
EXTERNAL NAME '/usr/lib/local_site.so'
LANGUAGE C

A statement-local variable (SLV) is an OUT parameter used in the WHERE clause of a SELECT statement. See Statement-local variables (SLVs) for more information.

Restriction: You cannot execute UDRs with OUT parameters in Data Manipulation Language (DML) SQL statements, except by using an SLV. The statements SELECT, UPDATE, INSERT and DELETE are DML statements.

Restriction: You cannot use the EXECUTE FUNCTION statement to invoke a user-defined function that contains an OUT parameter, unless you are using JDBC.

Restriction: You cannot execute remote UDRs that contain OUT parameters.