DB2 Version 9.7 for Linux, UNIX, and Windows

SQL PL functionality has been extended for user-defined functions

In Version 9.7 and in Version 9.7 Fix Pack 1, an enhanced set of SQL PL features can be referenced in SQL functions when functions are created with a compiled compound statement as the function-body.

In previous releases, SQL functions could only contain the subset of SQL PL statements known as inline SQL PL statements.

In Version 9.7, SQL functions can be defined using a compiled compound statement that include or reference the following features:

SQL PL statements, including CASE and REPEAT statements
Support for declaring and referencing variables defined by local user-defined data types including: row data types, array data types and cursor data types
Cursor declarations
Dynamic SQL
Conditions
Condition handlers
OUT and INOUT parameters (available in Fix Pack 1 and later fix packs)
Compiled UDFs which contain assignment to global variables (available in Fix Pack 1 and later fix packs)

In releases prior to Version 9.7, these features are either not available or are only available for use within SQL procedures.

In Version 9.7 Fix Pack 1 and later fix packs, basic support for compiled user defined functions has been added for database partitioning environments.

Examples

The following example demonstrates the difference required in a CREATE FUNCTION statement to create a compiled SQL function instead of an inline SQL function.

Inline SQL function definition Compiled SQL function definition

Table 1. Comparison of SQL syntax required for inline SQL functions and compiled SQL functions
Inline SQL function definition	Compiled SQL function definition
`CREATE FUNCTION TAN (X DOUBLE) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC RETURN SIN(X)/COS(X); END`	`CREATE FUNCTION TAN (X DOUBLE) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN RETURN SIN(X)/COS(X); END`

CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC  
  RETURN SIN(X)/COS(X);
END

CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
  RETURN SIN(X)/COS(X);
END

The following example demonstrates a compiled SQL function definition containing a cursor, condition handler statement, and a REPEAT statement:

CREATE FUNCTION exit_func( a INTEGER)
SPECIFIC udfPSM320
LANGUAGE SQL
RETURNS INTEGER
BEGIN 
  DECLARE val INTEGER DEFAULT 0;

  DECLARE myint INTEGER DEFAULT 0;

  DECLARE cur2 CURSOR FOR
   SELECT c2 FROM udfd1 
   WHERE c1 <= a 
	 ORDER BY c1;

  DECLARE EXIT HANDLER FOR NOT FOUND
  BEGIN
    SIGNAL SQLSTATE '70001' 
     SET MESSAGE_TEXT = 
      'Exit handler for not found fired';
  END;

OPEN cur2;

REPEAT
  FETCH cur2 INTO val;
  SET myint = myint + val;
UNTIL (myint >= a) 
END REPEAT;

CLOSE cur2;

RETURN myint;

END@
DB20000I  The SQL command completed
 successfully.

The compiled SQL function can be invoked by executing the following SQL statement:

VALUES(exit_func(-1));

The output of this invocation, which demonstrates the successful firing of the exit handler, is as follows:

1          
-----------
SQL0438N  Application raised error or warning with 
diagnostic text: "Exit handler for not found fired".  
SQLSTATE=70001