A user-defined function can be used, subject to permissions, in any place you would use a built-in non-aggregate function.

The following statement in Interactive SQL returns a full name from two columns containing a first and last name:

SELECT FullName(GivenName, Surname) 
 AS "Full Name"
 FROM Employees;

The following statement in Interactive SQL returns a full name from a supplied first and last name:

SELECT FullName('Jane', 'Smith') 
 AS "Full Name";

Any user who has been granted EXECUTE permissions for the function can use the FullName function.

Example

The following user-defined function illustrates local declarations of variables.

The Customers table includes some Canadian customers as well as customers from the USA. The user-defined function Nationality forms a 3-letter country code based on the Country column.

CREATE FUNCTION Nationality( CustomerID INT )
RETURNS CHAR( 3 )
BEGIN
    DECLARE nation_string CHAR(3);
    DECLARE nation country_t;
    SELECT DISTINCT Country INTO nation 
    FROM Customers 
    WHERE ID = CustomerID;
    IF nation = 'Canada' THEN
            SET nation_string = 'CDN';
    ELSE IF nation = 'USA' OR nation = ' ' THEN
            SET nation_string = 'USA';
        ELSE
            SET nation_string = 'OTH';
        END IF;
    END IF;
RETURN ( nation_string );
END;

This example declares a variable nation_string to hold the nationality string, uses a SET statement to set a value for the variable, and returns the value of the nation_string string to the calling environment.

The following query lists all Canadian customers in the Customers table:

SELECT *
FROM Customers
WHERE Nationality(ID) = 'CDN';

Declarations of cursors and exceptions are discussed in later sections.

Notes

While this function is useful for illustration, it may perform very poorly if used in a SELECT involving many rows. For example, if you used the function in the SELECT list of a query on a table containing 100,000 rows, of which 10,000 are returned, the function will be called 10,000 times. If you use it in the WHERE clause of the same query, it would be called 100,000 times.