MySQL Stored Procedure Programming

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.

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 ...