O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required