Calling user-defined functions

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

Executing the following statement in Interactive SQL uses the FullName user-defined function to return a full name from two columns containing a first and last name:

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

Full Name
Fran Whitney
Matthew Cobb
Philip Chin
...

Executing the following statement in Interactive SQL uses the FullName user-defined function to return a full name from a supplied first and last name:

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

Full Name
Jane Smith

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 Canadian and US customers. 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.

Calling user-defined functions

Example

Notes

See also