Creating Stored Functions

We provided an overview of the CREATE FUNCTION statement in Chapter 7, but we will recap here. You create a stored function using the following syntax:

 CREATE FUNCTION function_name (parameter[,...])
 RETURNS datatype
 [LANGUAGE SQL]
 [ [NOT] DETERMINISTIC ]
 [ {CONTAINS SQL | NO SQL | MODIFIES SQL DATA | READS SQL DATA} ]
 [ SQL SECURITY {DEFINER|INVOKER} ]
 [ COMMENT comment_string ]
 function_statements

Most of the options for the CREATE FUNCTION statement also apply to CREATE PROCEDURE and are documented in Chapter 7. However, the following are unique to stored functions:

• The RETURNS clause is mandatory and defines the data type that the function will return.
• You cannot specify the IN, OUT, or INOUT modifiers to parameters. All parameters are implicitly IN parameters.
• The function body must contain one or more RETURN statements, which terminate function execution and return the specified result to the calling program, as described in the following section.

10.1.1. The RETURN Statement

The RETURN statement terminates stored function execution and returns the specified value to the calling program. You can have as many RETURN statements in your stored function as makes sense. Example 10-1 shows an example of a stored function that has multiple RETURN statements.

Example 10-1. Simple stored function with multiple RETURN statements

CREATE FUNCTION cust_status(in_status CHAR(1))
 RETURNS VARCHAR(20)
BEGIN
 IF in_status = 'O' THEN
 RETURN('Overdue');
 ELSEIF in_status = 'U' THEN
 RETURN('Up to date');
 ELSEIF in_status = 'N' THEN
 RETURN('New');
 END IF;
END;

However, it is usually regarded as good practice to include only a single RETURN statement ("one way in and one way out"), and to use variable assignments within conditional statements to change the return value. Aside from arguably resulting in more comprehensible program flow, using a single RETURN statement can avoid the situation in which none of the RETURN statements get executed. "Falling out" of a function, rather than exiting cleanly via a RETURN statement, will cause a runtime error, as shown in Example 10-2.

Example 10-2. "Falling out" of a function without executing a RETURN statement

mysql> SELECT cust_status('X');
ERROR 1321 (2F005): FUNCTION cust_status ended without RETURN

Example 10-3 shows our previous example recoded to include only a single RETURN statement.

Example 10-3. Simple stored function with single RETURN statement

CREATE FUNCTION cust_status(in_status CHAR(1))
 RETURNS VARCHAR(20)
BEGIN
 DECLARE long_status VARCHAR(20);

 IF in_status = 'O' THEN
 SET long_status='Overdue';
 ELSEIF in_status = 'U' THEN
 SET long_status='Up to date';
 ELSEIF in_status = 'N' THEN
 SET long_status='New';
 END IF;

 RETURN(long_status);
END;

It is good practice to include only a single RETURN statementas the last line of executable codein your stored functions . Avoid any flow control that could allow the stored function to terminate without calling a RETURN statement.

 

10.1.2. Parameters to Stored Functions

Stored functions can include multiple parameters, but these may only be IN parameters. That is, you can specify neither the OUT nor INOUT clause (nor even the IN clause) when defining your parameters (see Chapter 7 for a more detailed description of OUT and INOUT parameters). So, for instance, the function defined in Example 10-4 will not compile.

Example 10-4. Function will not compile due to the INOUT clause

CREATE FUNCTION f_inout(INOUT x INT) RETURNS INT
BEGIN
 SET x=1;
 RETURN(1);
END;

Stored functions cannot include OUT or INOUT parameters; if you need to return multiple variables from your stored program, then a procedure is possibly more appropriate than a function.

 

10.1.3. The DETERMINISTIC and SQL Clauses

When binary logging is enabled, MySQL needs to know if a stored function that modifies SQL is deterministicthat is, if it always performs the same actions and returns the same results when provided with the same inputs. Since the default for stored programs is NOT DETERMINISTIC CONTAINS SQL, you need to explicitly set the appropriate keywords in order for the function to compile when binary logging is enabled. This requirement relates to the need to ensure that changes made in the stored function can be correctly replicated to another server. If the actions performed by the function are nondeterministic, then correct replication cannot be assured.

A nondeterministic routine is one that can produce different outputs when provided with the same inputs. In this context, "outputs" include not just the return values of the stored program, but also any modifications that may be made to data within the MySQL databases. Currently, MySQL only cares about the determinism of a function or a procedure in the context of replication. In the future, however, the DETERMINISTIC keyword may also be used to perform certain optimizations (such as caching function return values) or to allow a function to be used in an index or partition definition.

If you declare a stored function without one of the SQL mode clauses NO SQL or READS SQL, and if you have not specified the DETERMINISTIC clause, and if the binary log is enabled, you may receive the following error:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in
its declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

To avoid this error, you must do one of the following:

• Specify one or more of the DETERMINISTIC, NO SQL, and/or READS SQL DATA keywords in your stored function definition.
• Set the value of log_bin_trust_routine_creators to 1 (SET GLOBAL log_bin_trust_routine_creators = 1)

Of course, you should not specify that a stored function is DETERMINISTIC if it is not, and you should avoid setting log_bin_trust_routine_creators to 1 unless you are unconcerned about the correctness of data recovery or replication. Therefore, as a general rule, you should avoid creating nondeterministic stored functions that modify data.

The use of the NOW function or any similar time-based functions does not necessarily cause a stored function to become nondeterministic (at least from a replication perspective), since MySQL logs the timestamp in the binary log, resulting in NOW( ) being calculated correctly during replication or recovery. Likewise, a single random number will also not cause the routine to become nondeterministic, since the seed to the random number generator will be identical on the slave and during data recovery. However, multiple calls to RAND( ) will cause a routine to become nondeterministic.

This restriction on nondeterministic routines applied to both stored functions and stored procedures in the initial production release of MySQL 5.0, but from 5.0.16 on it applies only to stored functions.

If your function is nondeterministic, and it reads but does not modify the database, then you may use the clauses NOT DETERMINISTIC READS SQL DATA to allow the function to be created. If the function is nondeterministic and performs no database access at all, then we recommend using NOT DETERMINISTIC NO SQL.

The relevant ANSI standard intended that the NO SQL clause should pertain only to "external" stored programs written in nondatabase languages such as (for instance) Java or PHP. Therefore, the use of NO SQL may not be strictly correct from a standards perspective. However, we think that the alternativesto specify READS SQL DATA for a function that performs no database access at all or to declare a nondeterministic function as DETERMINISTICare clearly unacceptable. Therefore, we recommend that you use NO SQL when required to denote that a stored function performs no database operations.

Issues relating to replication and nondeterministic functions are expected to be resolved in MySQL 5.1 with the introduction of row-level binary logging.