Working with MySQL stored functions

The series so far:

In the previous three articles in this series, I focused on creating basic database objects that you can use to get started with MySQL. You learned how to build an initial database and then add tables, views, and stored procedures. In this article, I cover one more important type of object, the stored function, a routine that is stored in a database and can be invoked on-demand, similar to a user-defined scalar function in SQL Server or other database systems.

Stored functions work much like MySQL built-in functions. You can call either type of function in an expression, such as those in a query’s SELECT, WHERE, or ORDER BY clause. For example, you might use the CAST built-in function in a SELECT clause to convert a column to a different data type, as in CAST(plane_id AS CHAR). The expression converts the plane_id column (an integer) to a character data type. In the same way, you can use a stored function in your expression, applying your own logic to the plane_id column or any other column.

Before I go any further with stored functions, it’s important to note that there are three different types of MySQL functions that you can add at the database or server level:

• Stored functions. Functions that you create as database objects by using the CREATE FUNCTION statement.
• Loadable functions. Functions that are compiled as library files and then loaded to the server dynamically by running a CREATE FUNCTION statement.
• Native functions. Functions that are added to the server by modifying the MySQL source code and compiling it into mysqld.

This article focuses on creating stored functions, which share many of the same characteristics as MySQL stored procedures. In fact, I had considered writing about both of them in the previous article, but I think there are enough differences between them to warrant a separate article. It also provides a more consistent way of introducing each of the primary MySQL object types, even if it does mean repeating some of the information. With this in mind, let’s get started with the stored function.

Preparing your MySQL environment

As with the previous few articles, the examples in this article are based on the travel database. If you already have it installed, you can skip this section. If not, you can start by running the following SQL script to create the database and its tables:

The airplanes table includes a foreign key that references the manufacturers table, so be sure to create the tables in the order shown here. After you create the tables, you can add sample data to them so you’ll be able to test your function. To populate the tables, run the following INSERT statements:

As with the CREATE TABLE statements, you should run the INSERT statements in the order specified here so you don’t violate the foreign key defined on the airplanes table. Once you have the tables set up and populated, you can start creating stored functions.

Creating a stored function in MySQL

To add a stored function to a MySQL database, you can use the CREATE FUNCTION statement. The statement is similar to a CREATE PROCEDURE statement in several respects. In both cases, you must provide a name for the object and you must define a routine. You also have the option to include a DEFINER clause, one or more characteristics, and one or more parameters.

Despite these similarities, the CREATE FUNCTION statement differs in several important ways:

• A stored function can return only one value, unlike a stored procedure, which can return multiple values or an entire result set.
• A stored function supports input parameters only. A stored procedure supports IN, OUT, and INOUT parameters in any combination.
• A stored function must include a RETURNS clause in its definition before the routine. The clause specifies the data type for the function’s returned value. Stored procedures do not support this clause.
• A stored function’s routine must include a RETURN statement that specifies the function’s returned value. The routine does not have to include any other statements, only the RETURN statement. If it does include other statements, only the RETURN statement can return a value.

With these guidelines in mind, let’s look at a simple example of a CREATE FUNCTION statement, which defines a stored function that converts pounds to kilograms:

The function is named lbs_to_kg and includes one input parameter named lbs. You do not have to include a parameter when defining a function, but typically you’ll want at least one. If you add more than one, you need to separate them with commas.

The parameter definition is enclosed in parentheses and includes the parameter’s data type, MEDIUMINT UNSIGNED. I chose this data type because I ultimately want to use the function for the max_weight column in the airplanes table, which is also defined with that data type.

In addition, I used the MEDIUMINT UNSIGNED data type for the RETURNS clause. The clause specifies that the function’s returned value should be an integer in the range permitted by this data type. I figured we were safe with this data type because one pound is equivalent to 0.45359237 kilograms, so the returned value would never exceed the maximum value in the max_weight column.

If you want to support a greater range of values, you can instead use the INT or BIGINT data type for the lbs parameter and the RETURNS clause. This would provide you with more flexibility should you want to use the function to convert values that exceed those in the max_weight column.

The RETURNS clause is followed by the DETERMINISTIC characteristic. A characteristic is one of several options that can be added to a function definition, each one affecting the function in a different way. For example, you can add a characteristic to indicate the routine language or to define the routine’s nature. These are the same characteristics available to stored procedures.

The DETERMINISTIC characteristic indicates that the function will return the same results for the same input parameter each time the function runs. By default, a function is considered nondeterministic unless specified otherwise. Using the DETERMINISTIC characteristic can help the optimizer make better execution plan choices. However, assigning the characteristic to a nondeterministic function could cause the optimizer to make incorrect choices.

The function’s routine comes after the listed characteristics. For this routine, I’ve used the BEGIN…END syntax to set up a compound statement, even though there is only one RETURN statement. Often your routine will include a compound statement—a block of one or more SQL statements—and I wanted to be sure you understood how to include them in your function definition. As with stored procedures, it’s not uncommon for developers to use a compound statement, even if it includes only a single SQL statement.

The RETURN statement defines a simple mathematic expression that multiples the lbs input parameter value by 0.45359237 to arrive at the number of kilograms for the specified weight. The result from this calculation is what is returned by the function when you run it.

The preceding example also includes two DELIMITER statements that surround the function definition. The first DELIMITER statement changes the delimiter to double forward slashes (//), and the second DELIMITER statement changes the delimiter back to a semi-colon (the default). As you saw in the previous article, this provides a way to pass the entire function definition to the server as a single statement.

Verifying a newly created stored function

After you run the CREATE FUNCTION statement, you can verify that it’s been added to the travel database by viewing it in Navigator, as shown in Figure 1. (You might need to refresh Navigator to see the new function.)

Figure 1. Viewing the function in Navigator

From Navigator, you can open the function definition in the Routine tab by clicking the wrench icon next to the function name. Figure 2 shows the function definition on the Routine tab. The CREATE FUNCTION statement is nearly identical to what you created, except that it now includes the DEFINER clause after the CREATE keyword.

Figure 2. Viewing the function definition on the Routine tab

As you saw with views and stored procedures, the DEFINER clause specifies which account has been designated as the object creator. I ran the CREATE FUNCTION statement when I was signed in under the root account on my local MySQL instance, so that’s the username added to the definition. By default, MySQL uses the account of the user who runs the CREATE PROCEDURE statement, but you can specify a different account as long as it’s been granted adequate permissions.

You might have noticed that the function definition on the Routine tab does not include the DELIMITER statements or custom delimiter. However, if you were to update the definition and click Apply, Workbench would add those elements for you. (It would also add a DROP PROCEDURE statement that needs to run before the CREATE FUNCTION statement.)

Another way you can verify that the function has been created is to query the routines view in the INFORMATION_SCHEMA database:

The results should include the lbs_to_kg function, along with any other stored functions or stored procedures that have been created in the database. If you’re using the setup from the last article, your database might include the get_plane_info stored procedure.

In the preceding example, I included a WHERE clause that limits the results to the travel database. However, you can further limit the results by also specifying the function name in the WHERE clause and by specifying which column or columns to return. For example, the following SELECT statement limits the results to the routine_definition column and to the lbs_to_kg function in the travel database:

The statement should now return only a single value, although it might be difficult to read. As you saw with stored procedures, you can view a value in its entirety in a separate window. Right-click the value directly in the results and click Open Value in Viewer. MySQL launches a window that displays the value, as shown in Figure 3. (Select the Text tab if it’s not already selected.)

Figure 3. Examining the function’s routine body in Viewer

As you can see, the window displays only the function’s routine body, which in this case, is a compound statement that includes a RETURN statement.

Using a stored function in a MySQL query

After you verify that your function has been created, you should check that it works as expected. One way to do this is to create a simple SELECT statement that does nothing but call the lbs_to_kg function. For example, the following SELECT statement includes only a SELECT clause, and that clause contains only one expression:

The expression calls the lbs_to_kg function, passing in 132 as the parameter value. The expression also provides a name for the output column (max_kg). The statement should return a value of 60.

In many cases, you’ll want to use your stored function for more than just running it in a simple SELECT statement. For example, you can use a stored function to transform or augment a column’s values when retrieving data, which is what I’ve done in the following SELECT statement:

The statement joins the airplanes and manufacturers tables based on the manufacturer_id column in each table. The statement’s SELECT clause includes an expression that uses the lbs_to_kg function to convert the max_weight column to kilograms and return a column named max_kg. The statement returns the results shown in Figure 4.

Figure 4. Using the stored function in your query

The results include the original weight (in pounds) in the max_lbs column and the weight in kilograms in the max_kg column after converting the max_weight values. By including both weights, you can quickly compare them to get a general sense of whether the function seems to be returning the expected results.

Updating a stored function in MySQL

As pointed out earlier, MySQL stored functions are similar to stored procedures in several ways. For example, they both support characteristics, input parameters, and the DEFINER clause. They’re also similar in another important way. You can alter only the characteristics. You cannot change the routine or any other statement elements. Instead, you must first drop the function and then re-create it, incorporating any new elements.

To drop a stored function, you can use the DROP FUNCTION statement, as shown in the following example:

The IF EXISTS clause is optional, but it’s a handy way to avoid generating errors when you try to drop a function. The clause can be particularly useful when you’re developing your database schema and you’re regularly updating the objects.

After you’ve dropped the function, you can modify the definition to meet your new requirements. For example, the following CREATE FUNCTION statement re-creates the lbs_to_kg function but this time adds a DECLARE statement and an IF construction to the compound statement:

The DECLARE statement declares the msg local variable and assigns it the VARCHAR data type. Notice that the RETURNS clause has also been updated to the VARCHAR data type to match the msg variable. The variable can then be used in the final RETURN statement to provide the function’s output value.

The compound statement also includes an IF statement. The statement starts with an initial conditional clause, which is followed by an ELSEIF clause and then an ELSE clause. Each clause implements the same logic based on the value of the lbs input parameter. If the lbs value falls within the specified range, the msg variable is set to a predefined value based on that range. (We’ll be covering conditional statements in more detail later in the series.)

The msg value is determined first by converting the lbs value to kilograms and then concatenating the results with a string (the message body). For example, if the lbs value is greater than 99999, the msg variable is set to the number of kilograms plus the message ‘ kg exceeds the airport weight limits.’

To help carry out this logic, each conditional clause also includes two built-in functions: ROUND and CONCAT. The ROUND function rounds the calculated kilograms to a whole number, and the CONCAT function concatenates the rounded kilograms with the specified text. For example, if the weight in pounds is 120,000, the IF statement will set the msg variable to ‘54431 kg exceeds runway weight limits.’ You can see this for yourself by running the following SELECT statement:

The statement should return the results shown in Figure 5.

Figure 5. Viewing the results returned by the updated stored function

You can also use the lbs_to_kg function in a more elaborate SELECT statement, just like you did earlier:

Now each returned row includes one of the three messages in the max_kg column. The message is based on the number of pounds in the max_weight column, which is passed to the function through its parameter. Figure 6 shows the results returned by the SELECT statement.

Figure 6. Using a stored function in your query expressions

Your function can, of course, include a much more complex routine than what I’ve done here, but these examples should be enough to give you a sense of what you can do with stored functions and how valuable they can be when building your queries.

Altering a stored function in MySQL

As I mentioned earlier, the only elements of a stored function definition that you can alter are the characteristics. For this, you can use an ALTER FUNCTION statement. For example, the following statement adds a COMMENT characteristic and SQL SECURITY characteristic:

The COMMENT characteristic simply adds a comment that describes the function’s purpose. The SQL SECURITY characteristic instructs MySQL to run the routine under the security context of the user account that invokes the function rather than using the definer account (the default behavior).

After you run the ALTER FUNCTION statement, you can verify that the characteristics have been added by viewing the function definition on the Routine tab, as shown in Figure 7.

Figure 7. Viewing the stored function definition on the Routine tab

The CREATE PROCEDURE statement now includes three characteristics—the one you added originally and the two you added when you ran the ALTER FUNCTION statement.

Working with stored functions in MySQL

Although stored functions are similar to stored procedures, they serve a distinctly different purpose: to return a value that can be used by an expression during its evaluation. For this reason, stored functions can be extremely useful and are well worth adding to your arsenal of tools, especially since they’re so easy to create and execute. However, they can also impact performance if not carefully implemented. For example, if your query returns thousands of rows of data, an overly complex function could bring your system to its knees as MySQL tries to apply the logic to each row. When used properly, however, stored functions can be incredibly beneficial, especially as you become more adept at building SQL queries.