Calling a user-defined function

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

Prérequis

You must have EXECUTE privilege on the function.

Task

In Interactive SQL, connect to the database.
Execute a SELECT statement using the user-defined function.

Résultat

The function is called and executed.

Exemple

Example 1: Call a user-defined function The following function concatenates a firstname string and a lastname string.

CREATE FUNCTION fullname( firstname CHAR(30), lastname CHAR(30) ) RETURNS CHAR(61) BEGIN DECLARE name CHAR(61); SET name = firstname || ' ' || lastname; RETURN (name); END;

Execute the following statement in Interactive SQL 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
...

Execute the following statement in Interactive SQL to use 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

Example 2: Local declarations of variables The following user-defined function illustrates local declarations of variables.

Note

While this function is useful for illustration, it may perform 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 100000 rows, of which 10000 are returned, the function is called 10000 times. If you use it in the WHERE clause of the same query, it would be called 100000 times.

The Customers table includes Canadian and American customers. The user-defined function Nationality forms a three-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 named nation_string to hold the nationality string, uses a SET statement to set a value for the variable, and returns the value of nation_string to the calling environment.

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

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