Using an SPL Function to Update Column Values (XPS)

When you use an SPL function to update column values, the returned values of the function must have a one-to-one correspondence with the listed columns. That is, each value that the SPL function returns must be of the data type expected by the corresponding column in the column list.

If the called SPL routine contains certain SQL statements, a runtime error occurs. For information on which SQL statements cannot be used in an SPL routine that is called in a data-manipulation statement, see Restrictions on SPL Routines in Data-Manipulation Statements. In the next example, the SPL function p2( ) updates the i2 and c2 columns of the t2 table:

CREATE PROCEDURE p2() RETURNING int, char(20);
   RETURN 3, 'three';
END PROCEDURE;
UPDATE t2 SET (i2, c2) = (p2()) WHERE i2 = 2;

In Extended Parallel Server, you create an SPL function with the CREATE PROCEDURE statement. The CREATE FUNCTION statement is not available.