Stored functions are similar to stored procedures in that both allow you to save predefined blocks of code for reuse. As we’ve already noted, stored functions differ from stored procedures in that stored functions actually return a value. For this reason, stored functions have only input parameters (if any parameters at all), so the IN , OUT , and INOUT keywords aren’t used. Stored functions have no output parameters; instead, you use a RETURN statement to return a value whose type is determined by the RETURNS type statement, which precedes the body of the function. Here’s an example of a relatively simple function named get_volume that takes the radius of a sphere as input, and returns its volume:

To invoke the function, you don’t use CALL as you would with a stored procedure; instead, you merely use the name of the procedure along with any parameters that are required by it. This can be done as part of a SELECT statement, as shown here:

You can also refer to functions directly in expressions, and you’re not limited to working with numbers, as you’ll see in the next example.

Stored functions and stored procedures can invoke other stored functions and stored procedures. In this example, we create a new function named get_volume_in_words that makes use of the get_volume function from the previous example:

Rather than just spitting out a number without any context, this function tells us exactly what information get_volume is providing, in a plain English sentence. Let’s try it out:

These two functions may be trivial in and of themselves, but they serve to illustrate the potential for managing and organizing complex tasks by breaking them down into smaller ones. Remember that you’re not limited to calling only functions from other functions—you can use stored procedures from within stored functions, and the reverse is true as well.

Flow Control in Stored Procedures

Stored procedure and function syntax in MySQL 5.0 and above includes a number of flow-control constructs that may look familiar to you, particularly if you’ve read Chapter 4. However, the syntax for some of these differs when used in a stored procedure or stored function, so you’ll want to look at the descriptions and examples that follow, in order to be aware of these. In addition, MySQL 5.0 and above support some looping constructs that aren’t available outside stored procedures and functions—for information on these and some examples, see the sections on LOOP , REPEAT , and WHILE later in this chapter.

This construct is much like the if... elseif... else... found in such programming languages as PHP and Perl, and should not be confused with the IF() , IFNULL() , and NULLIF() functions we introduced earlier in this book (see Chapter 4). Its syntax is

IF condition THEN statement-bloc k
[ELSEIF condition THEN statement-block] [ELSE statement-block]
END IF;

The condition following IF may be any expression that evaluates to Boolean TRUE or FALSE. Should this condition evaluate as TRUE, then the statement or statements immediately following THEN are executed; otherwise, control passes to the next ELSEIF (if there is one) and its condition is evaluated—if that expression is true, the statement block following the next THEN executes. There can be any number of ELSEIF clauses in an IF block. If there is an ELSE clause and no condi tion has tested as TRUE up to that point, then the statement (or block of statements) following ELSE is executed.

This example creates a stored function that compares two integer values. If the first value is greater than the second, a 1 is returned; if the second value is larger, a –1 is returned; and if the two values are the same, the function returns a 0:

Here you can see the compare function being used:

Note that a complete IF block must be terminated by an END IF and a semicolon.

Here’s an example of a stored procedure that also uses IF ... THEN ... ELSE logic. This procedure, named get_running_total, also makes use of two INOUT parameters:

As we mentioned earlier, an INOUT parameter is actually modified by the procedure it’s used in, similar to passing a parameter by reference in some programming languages. In this procedure, we use two INOUT parameters: one to store a cumulative total of the prices of the products identified by the id parameter each time the procedure is called, and one to store a comma-delimited list of the names of those products. A couple of calls to this procedure with intervening SELECT queries to read the values of the variables passed should help to demonstrate just how these work: