MySQL - Introduction To Stored Procedures And Functions Part 2

This is part 2 on "Introduction To Stored Procedures and Functions". In part 1, I discussed what stored routines were with a few stored procedure examples. In this part, I will explain what stored functions are and provide a few examples.

If you haven't already done so, I would suggest reading Introduction To Stored Procedures and Functions Part 1, for a better understanding.

Part 2

There are two types of functions that MySQL allows you to create. A stored function and a User Defined Function (UDF). Although the two are created using the CREATE FUNCTION statement and share the same namespace, they are both implemented differently. The focus of this article is to explain store functions.

A function is much like a procedure, in that it is a routine stored on the server and like a procedure, it too can accept parameters. However functions can't return more than one value nor can they return resultsets. They are used to return a single value when used in an SQL statement. Stored functions only have an IN parameter mode, the function body is responsible for returning a value. Unlike stored procedures which need to be invoked using the CALL statement, functions are invoked within SQL statements like built in functions. The example code below creates a stored function that returns an uppercase string. The function simply uses the built-in UPPER function do demonstrate this.

1. DELIMITER $$   
2.   
3. CREATE FUNCTION toupper(str LONGTEXT)   
4.     RETURNS LONGTEXT   
5.     RETURN UPPER(str);   
6.   

1. SELECT TOUPPER('hello world')    
2.   
3. # Outputs HELLO WORLD  

Looking at the CREATE FUNCTION statement, you will notice, that there are two statements RETURNS and RETURN. The RETURNS statement simply specifies the return data type while the RETURN statement returns a value. Also notice that the BEING AND END statements have been ommited. This is because the function body is contained within a single line.
Now, let's take a look at a few useful stored functions that are easy to create. The next code example demonstrates how to create a function that can be used to change the first character in a string to uppercase. This function is equivalent to PHP's ucfirst().

1. CREATE FUNCTION uc_first(str LONGTEXT)   
2.     RETURNS LONGTEXT   
3.     BEGIN   
4.     SET @firstChar = SUBSTRING(str, 1,1);   
5.     SET @remainingChars = SUBSTRING(str, 2);   
6.     RETURN CONCAT(UPPER(@firstChar), @remainingChars);   
7.     END  

The code above uses three of MySQL's built-in string functions. SUBSTRING, CONCAT and UPPER. As the name suggests, the SUBSTRING function returns a substring from a larger string. In this case the first character is returned and placed into a variable @firstChar, while the remaining string are stored in the @remainingChars variable. The two variables are then concatenated but @firstChar is turned into uppercase before the final string is returned.

This next function converts the first character in a word sentence to uppercase. While at first glance the function may seem complicated, a deeper inspection of the code will reveal its simplicity.

1. CREATE FUNCTION uc_words(str LONGTEXT)   
2.     RETURNS LONGTEXT   
3.     BEGIN   
4.         DECLARE returnStr LONGTEXT DEFAULT '';   
5.         DECLARE len INT DEFAULT 0;   
6.         DECLARE i INT DEFAULT 1;   
7.         DECLARE ch CHAR(1);   
8.         DECLARE m bool DEFAULT true;   
9.            
10.         SET len = LENGTH(str) + 1;   
11.     
12.         WHILE (i < len) DO   
13.             BEGIN   
14.                 SET ch = SUBSTRING(str, i, 1);   
15.                 IF m = true THEN   
16.                     BEGIN   
17.                         SET returnStr = CONCAT(returnStr, UCASE(ch));   
18.                         SET m = FALSE;   
19.                     END;   
20.                 ELSEIF ch = ' ' THEN   
21.                     BEGIN   
22.                         SET returnStr = CONCAT(returnStr, ' ');   
23.                         SET m = TRUE;   
24.                     END;   
25.   
26.                 ELSE   
27.                     BEGIN   
28.                         SET returnStr = CONCAT(returnStr, ch);   
29.                     END;   
30.                 END IF;   
31.   
32.                 SET i = i+1;   
33.             END;   
34.         END WHILE;   
35.         RETURN returnStr;   
36.     END  

1. SELECT uc_words('the quick brown fox jumps over the lazy dog')   

The function takes a sentence and stores it's length into a variable len. A while loop is then used to iterate through each character in the sentence. The SUBSTRING function returns the current character and checks if it is a space ' '. On determining that the current character is a space ' ', the character is concatenated to the variable returnStr and the variable m is set to true. Setting the variable m to true signifies that the next character is the start of a new word. On each iteration the variable m is checked, if set to true, the current character is turned into uppercase and the status of m is set to false. All other characters are dealt with by the ELSE block, which simply concatenates the current character to the variable returnStr. The result is a string stored in the variable returnStr with the first character in all words turned to uppercase.

The final function in this article, although not very accurate will return the number of years elapsed between a given date and the current date. The function is named age as it can be used to determine how old a person is or will be at the current year defined by the NOW() function.

1. CREATE FUNCTION age(dob DATETIME)   
2.     RETURNS INT   
3.     BEGIN   
4.         RETURN DATEDIFF(NOW(), dob) / 365;   
5.     END  

1. SELECT age('1970-05-22')   

Hopefully the examples in this article have given you an idea as to what stored functions are and how they are used.