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