A user-defined function can be used in any place you would use a built-in non-aggregate function.
You must have EXECUTE privilege on the function.
In Interactive SQL, connect to the database.
Execute a SELECT statement using the user-defined function.
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;
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";
Example 2: Local declarations of variables The following user-defined function illustrates local declarations of variables.
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';
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|