Stored Functions

Section 0: Module Objectives or Competencies

Section 1: Definition

Learning MySQL - Stored Functions

A stored function is a special kind stored program that returns a single value.

• Typically, you use stored functions to encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.
• Different from a stored procedure, you can use a stored function in SQL statements wherever an expression is used. This helps improve the readability and maintainability of the procedural code.

Outputs:

• A stored function can return a single value such as a number.

Section 2: Creation

Declaration

Stored functions are created with CREATE FUNCTION:

CREATE FUNCTION routine name ([parameter_name type [,...]])
RETURNS type [[NOT] DETERMINISTIC]
routine_body

As with stored procedures, the parameter list is required and an empty list of () should be used when creating stored functions that do not require any parameters.

• Unlike procedures, however, functions only accept one type of parameter (IN).
• The IN is implied and explicit use of the IN keyword in the parameter list will result in an error.

Example: Comparison Function

This function compares two integers and returns a string indicating how they are related.

delimiter //
CREATE FUNCTION IntCompare (var1 INT, var2 INT)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE outputString VARCHAR(50);
IF var1 = var2 THEN SET outputString = 'equals';
ELSE
IF var1 > var2 THEN SET outputString = 'greater';
ELSE SET outputString = 'less';
END IF;
SET outputString = CONCAT('is ', outputString, ' than');
END IF;
SET outputString = CONCAT(var1, ' ', outputString, ' ', var2, '.');
RETURN outputString;
END//
delimiter ;

Invocation

Stored functions can be invoked from a stored procedure, stored function, or trigger:

• From within a stored procedure, a stored function can be invoked both within a SET clause and within a variety of SQL statements.

The following query invokes the stored function IntCompare on the pubs database to (ridiculously) compare each publisher's ID number to the average publisher ID number.

SELECT pub_name, IntCompare(pub_id, (SELECT AVG(pub_id) FROM publishers)) AS compare FROM publishers;

		    +-----------------------+----------------------------+
		    | pub_name              | compare                    |
		    +-----------------------+----------------------------+
		    | New Moon Books        | 736 is less than 4529.     |
		    | Binnet & Hardley      | 877 is less than 4529.     |
		    | Algodata Infosystems  | 1389 is less than 4529.    |
		    | Scootney Books        | 9952 is greater than 4529. |
		    | Five Lakes Publishing | 1622 is less than 4529.    |
		    | Ramona Publishers     | 1756 is less than 4529.    |
		    | GGG&G                 | 9901 is greater than 4529. |
		    | Lucerne Publishing    | 9999 is greater than 4529. |
		    +-----------------------+----------------------------+
		    8 rows in set (0.01 sec)
		    

Stored functions can also be called from a simple query:

The following query invokes the stored function IntCompare to compare publisher ID '0877' to the minimum publisher ID number.

SET @result = IntCompare('0877', (SELECT MIN(pub_id) FROM publishers));
SELECT @result;

@result
877 is greater than 736.

Body

Stored functions are generally formed of regular SQL statements, although there are some differences.

• Flow control constructs – specifically, IF, CASE, ITERATE, LEAVE, LOOP, WHILE, and REPEAT – may appear within stored functions to control the order of statement execution.
• There are restrictions on which SQL statements may appear in stored functions.
• Stored functions may consist of multiple statements by using a BEGIN ... END compound statement block.

Returning Values

A stored function is capable of returning a single value to the calling routine through the function name.

The return value is returned to the calling routine through the function name by using the RETURN statement, as in the following example.

RETURN comparisonResult

Variables

Stored functions may also declare and use their own set of local variables. DECLARE creates local variables within a stored function.

• SET assigns values to variables, and SELECT ... INTO assigns the results of a query that returns a single row to variables.
• All local variables must be declared at the beginning of the stored function.

BEGIN ... END Compound Statement

BEGIN ... END syntax is used for writing compound statements.

• A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords.
• Each of the statements in a compound statement is terminated by a semicolon
• See this link for details.

Function Determinism

MySQL treats stored functions as NOT DETERMINISTIC by default.

• Non-deterministic functions might give different answers on each invocation, even if the arguments are always the same.
• Functions like RAND() and ROW_COUNT() are examples of non-deterministic functions.
• Functions that always generate the same output given the same input should be explicitly defined as DETERMINISTIC to optimize performance.

MySQL does not check this declaration for accuracy. Misdeclaring a routine may have serious adverse consequences (incorrect results or poor performance).

Section 3: Limitations

Note the following limitations:

• Functions cannot start, commit, or rollback transactions
• Functions cannot return result sets, although they may return a value.
• Functions cannot be used recursively.
• Functions cannot modify a table that is already being used by the statement that invoked the function.
• Functions cannot use prepared statements.
• Functions may invoke stored procedures that adhere to the restrictions for stored functions.

Section 4: Managing Stored Functions

To list existing stored functions, use SHOW FUNCTION STATUS.

Recall the code of a particular stored function with SHOW CREATE FUNCTION.

To remove stored functions from the database, use DROP FUNCTION.

• To drop a stored function if it exists (before creating a new version) use the IF EXISTS clause, as in DROP FUNCTION IF EXISTS <stored_function_name>;

To make changes to any type of stored program (procedure, function, or trigger), drop and re-create the routine.

Section 5: Examples

Example: Hello, World! Function

This example defines a single-line string function:

CREATE FUNCTION hello (name CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',name,'!');

As with pre-installed MySQL functions, stored functions can be used within other SQL statements, such as this example using the pubs database.

SELECT hello(au_fname) AS greeting FROM authors ORDER BY RAND() LIMIT 0, 5;

		    +---------------------+
		    | greeting            |
		    +---------------------+
		    | Hello, Sheryl!      |
		    | Hello, Morningstar! |
		    | Hello, Akiko!       |
		    | Hello, Johnson!     |
		    | Hello, Albert!      |
		    +---------------------+
		    5 rows in set (0.00 sec)
		    

Example: Character Filtering Function

This function removes non-numeric characters from a string.

delimiter //
CREATE FUNCTION NumbersOnly(stringIn VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE lcv INT DEFAULT 0;
DECLARE stringOut VARCHAR(255) DEFAULT '';
DECLARE stringTemp CHAR(1);

WHILE (lcv <= LENGTH(stringIn)) DO
SET stringTemp = SUBSTRING(stringIn, lcv, 1);
IF ASCII(stringTemp) BETWEEN 48 AND 57 THEN
SET stringOut = CONCAT(stringOut, stringTemp);
END IF
SET lcv = lcv + 1;
END WHILE;
RETURN stringOut;
END//
delimiter ;

This function can be used to remove characters, spaces, dashes, periods, and other unwanted characters from data such as phone numbers or credit card numbers.

The following query uses the function on author phone numbers in the pubs database.

SELECT au_fname, au_lname, NumbersOnly(phone) AS phone FROM authors LIMIT 0, 5;

		    +----------+----------+------------+
		    | au_fname | au_lname | phone      |
		    +----------+----------+------------+
		    | Johnson  | White    | 4084967223 |
		    | Marjorie | Green    | 4159867020 |
		    | Cheryl   | Carson   | 4155487723 |
		    | Michael  | O'Leary  | 4082862428 |
		    | Dean     | Straight | 4158342919 |
		    +----------+----------+------------+
		    5 rows in set (0.00 sec)
		    

Section 6: Additional Resources

• MariaDB Stored Functions
• MySQL Stored Functions
• MySQL | Creating stored function