Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 311

Suppose that you want to create a stored function that calculates and returns the average
size in bytes of mail messages listed in the mail table. The function can be defined like
this, where the body consists of a single SQL statement:
CREATE FUNCTION avg_mail_size ()
RETURNS FLOAT READS SQL DATA
RETURN ( SELECT AVG ( size ) FROM mail );
The RETURNS FLOAT clause indicates the type of the function's return value, and READS
SQL DATA indicates that the function reads but does not modify data. The function body
follows those clauses: a single RETURN statement that executes a subquery and returns
the resulting value to the caller. (Every stored function must have at least one RETURN
statement.)
In mysql , you can enter that statement as shown and there is no problem. The definition
requires just the single terminator at the end and none internally, so no ambiguity arises.
But suppose instead that you want the function to take an argument naming a user that
it interprets as follows:
• If the argument is NULL , the function returns the average size for all messages (as
before).
• If the argument is non- NULL , the function returns the average size for messages sent
by that user.
To accomplish this, the function has a more complex body that uses a BEGIN … END
block:
CREATE FUNCTION avg_mail_size ( user VARCHAR ( 8 ))
RETURNS FLOAT READS SQL DATA
BEGIN
DECLARE avg FLOAT ;
IF user IS NULL
THEN # average message size over all users
SET avg = ( SELECT AVG ( size ) FROM mail );
ELSE # average message size for given user
SET avg = ( SELECT AVG ( size ) FROM mail WHERE srcuser = user );
END IF ;
RETURN avg ;
END ;
If you try to define the function within mysql by entering that definition as just shown,
mysql improperly interprets the first semicolon in the function body as ending the def‐
inition. Instead, use the delimiter command to change the mysql delimiter, then restore
the delimiter to its default value:
mysql> delimiter $$
mysql> CREATE FUNCTION avg_mail_size(user VARCHAR(8))
-> RETURNS FLOAT READS SQL DATA
-> BEGIN