ASA SQL User's Guide
Using Procedures, Triggers, and Batches
Introduction to 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.
The following statement in Interactive SQL returns a full name from two columns containing a first and last name:
SELECT fullname (emp_fname, emp_lname) FROM employee;
fullname (emp_fname, emp_lname) |
---|
Fran Whitney |
Matthew Cobb |
Philip Chin |
... |
The following statement in Interactive SQL returns a full name from a supplied first and last name:
SELECT fullname ('Jane', 'Smith');
fullname ('Jane','Smith') |
---|
Jane Smith |
Any user who has been granted EXECUTE permissions for the function can use the fullname function.
The following user-defined function illustrates local declarations of variables.
The customer table includes some Canadian customers sprinkled among those from the USA, but there is no country column. The user-defined function nationality uses the fact that the US zip code is numeric while the Canadian postal code begins with a letter to distinguish Canadian and US customers.
CREATE FUNCTION nationality( cust_id INT ) RETURNS CHAR( 20 ) BEGIN DECLARE natl CHAR(20); IF cust_id IN ( SELECT id FROM customer WHERE LEFT(zip,1) > '9') THEN SET natl = 'CDN'; ELSE SET natl = 'USA'; END IF; RETURN ( natl ); END
This example declares a variable natl to hold the nationality string, uses a SET statement to set a value for the variable, and returns the value of the natl string to the calling environment.
The following query lists all Canadian customers in the customer table:
SELECT * FROM customer WHERE nationality(id) = 'CDN'
Declarations of cursors and exceptions are discussed in later sections.
The same query restated without the function would perform better, especially if an index on zip existed. For example,
Select * FROM customer WHERE zip > '99999'
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 SELECT 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.