Recipe 16.1. Creating Compound-Statement Objects

Problem

You want to define a stored routine, a trigger, or an event, but its body contains instances of the ; statement terminator. This is the same terminator that mysql uses by default, so mysql misinterprets the definition and produces an error.

Solution

Redefine the mysql statement terminator with the delimiter command.

Discussion

Each stored routine, trigger, or event is an object with a body that must be a single SQL statement. However, these objects often perform complex operations that require several statements. To handle this, you write the statements within a BEGIN ... END block that forms a compound statement. That is, the block is itself a single statement but can contain multiple statements, each terminated by a ; character. The BEGIN ... END block can contain statements such as SELECT or INSERT, but compound statements also allow for conditional statements such as IF or CASE, looping constructs such as WHILE or REPEAT, or other BEGIN ... END blocks.

Compound-statement syntax provides you with a lot of flexibility, but if you define compound-statement objects within mysql, you'll quickly run into a small problem: statements within a compound statement each must be terminated by a ; character, but mysql itself interprets ; to figure out where each statement ends so that it can send them one at a time to the server to be executed. Consequently, mysql stops reading the compound statement when it sees the first ; character, which is too early. The solution to this problem is to tell mysql to recognize a different statement delimiter. Then mysql will ignore the ; character within the object body. You terminate the object itself with the new delimiter, which mysql recognizes and then sends the entire object definition to the server. You can restore the mysql delimiter to its original value after defining the compound-statement object.

Suppose that you want to define 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 with a body part consisting of a single SQL statement like this:

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 body of the function follows those clauses and consists of the single RETURN statement that executes a subquery and returns the value that it produces 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 to define the function to take an argument naming a user that is interpreted 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 routine needs a more complex body that uses a BEGIN ... END block:

CREATE FUNCTION avg_mail_size(user VARCHAR(8)) RETURNS FLOAT READS SQL DATA BEGIN   IF user IS NULL THEN     # return average message size over all users     RETURN (SELECT AVG(size) FROM mail);   ELSE     # return average message size for given user     RETURN (SELECT AVG(size) FROM mail WHERE srcuser = user);   END IF; END; 

If you try to define the function within mysql by entering that definition as is, mysql will improperly interpret the first semicolon in the function body as ending the definition. To handle this, use the delimiter command to change the mysql delimiter to something else temporarily. The following example shows how to do this and 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     ->   IF user IS NULL THEN     ->     # return average message size over all users     ->     RETURN (SELECT AVG(size) FROM mail);     ->   ELSE     ->     # return average message size for given user     ->     RETURN (SELECT AVG(size) FROM mail WHERE srcuser = user);     ->   END IF;     -> END;     -> $$ Query OK, 0 rows affected (0.02 sec) mysql> delimiter ;             

After defining the stored function, you can invoke it the same way as built-in functions:

mysql> SELECT avg_mail_size(NULL), avg_mail_size('barb'); +---------------------+-----------------------+ | avg_mail_size(NULL) | avg_mail_size('barb') | +---------------------+-----------------------+ |         237386.5625 |                 52232 | +---------------------+-----------------------+ 

The same principles apply to defining other objects that use compound statements (stored procedures, triggers, and events).