SQL scalar functions

Start of change An SQL scalar function is a user-defined function written in SQL and it returns a single value each time it is invoked. SQL scalar functions contain the source code for the user-defined function in the user-defined function definition. There are two kinds of SQL scalar functions, inlined and compiled. End of change

All SQL scalar functions that were created prior to DB2® 10 are inlined SQL scalar functions. Beginning with DB2 10, SQL scalar functions may be created as either inlined or compiled.

DB2 determines whether an SQL scalar function is inlined or compiled according to whether or not the CREATE FUNCTION statement that defines the function makes use of enhanced features. See CREATE FUNCTION (inlined SQL scalar) and CREATE FUNCTION (compiled SQL scalar) for more information.

An inlined SQL scalar function has a body with a single RETURN statement. The RETURN statement can return either a NULL value or a simple expression that does not reference a scalar fullselect. No package will be generated for an inlined SQL scalar function. During the preparation of an SQL statement that references the function (when the function is invoked), the expression specified in the RETURN statement of the function is simply inlined into that SQL statement.

A compiled SQL scalar function can have a body with logic written in SQL PL language. It can make use of any of the enhanced features for the CREATE FUNCTION statement including the support for TABLE LOCATOR data type for parameters, various options, and an enhanced RETURN statement that allows reference to a scalar fullselect. A package is created for a compiled SQL scalar function.

Compiled SQL scalar functions include support for versioning and source code management. You can use compiled SQL scalar functions for the following tasks:

Define multiple versions of an SQL scalar function, where one version is considered the "active" version
Activate a particular version of an SQL scalar function
Alter the routine options that are associated with a version of an SQL scalar function
Define a new version of an SQL scalar function by specifying the same function signature as the current version, and different routine options and function body
Replace the definition of an existing version by specifying the same function signature as the current version, and different routine options and function body
Drop a version of an SQL scalar function.
Fall back to a previous version without requiring an explicit rebind or recompile

You can deploy compiled SQL scalar functions to multiple servers to allow a wider community to use functions that have been thoroughly tested, without the risk of changing the logic in the routine body. Use the Unified Debugger to remotely debug compiled SQL scalar functions that execute on DB2 for z/OS® servers.

To prepare an SQL scalar function for execution, you execute the CREATE FUNCTION statement, either statically or dynamically.

Example: Definition for a compiled SQL scalar user-defined function: The following example defines a scalar function that returns the text of an input string, in reverse order. The example also explains how to determine why various SQL statements are allowed in a compiled SQL scalar function.

A compiled SQL scalar CREATE FUNCTION statement contains an SQL-routine-body, as defined in CREATE FUNCTION (compiled SQL scalar). The syntax diagram for SQL-routine-body defines the function body as a single SQL control statement. The syntax diagram for SQL-control-statement in SQL control statements for SQL routines identifies the control statements that can be specified, including a RETURN statement.

An SQL function can contain multiple SQL statements if the outermost SQL statement is an SQL-control-statement that includes other SQL statements. These statements are defined as SQL procedure statements. The syntax diagram in SQL-procedure-statement identifies the SQL statements that can be specified within a control statement. The syntax notes for SQL-procedure-statement clarify the SQL statements that are allowed in an SQL function.

    CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
      RETURNS VARCHAR(4000)
      DETERMINISTIC NO EXTERNAL ACTION
      CONTAINS SQL
      BEGIN  A 
      DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';  B 
      DECLARE LEN INT;  B 
      IF INSTR IS NULL THEN  C 
      RETURN NULL;  D 
      END IF;
      SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));  E 
      WHILE LEN > 0 DO  F 
      SET (REVSTR, RESTSTR, LEN)  E 
        = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
        SUBSTR(RESTSTR, 2, LEN - 1),
        LEN - 1);
     END WHILE;
     RETURN REVSTR;  D 
   END#  A

The SQL function has the following keywords and statements:

The BEGIN and END keywords ( A ) indicate the beginning and the end of a compound statement.
The DECLARE statements ( B ) are components of a compound statement, and define SQL variables within the compound statement. For more information on compound statements, see compound-statement.
The IF statement ( C ), the RETURN statements ( D ), and the WHILE statement ( F ) are SQL control statements.
The SET assignment statements ( E ) are SQL control statements that assign values to SQL variables.

SQL variables can be referenced anywhere in the compound statement in which they are declared, including any SQL statement that is directly or indirectly nested within that compound statement. See References to SQL parameters and SQL variables for more information.