Database Reference
In-Depth Information
To create a scheduled event, you must have the
EVENT
privilege for the database in which
the event is created.
For information about granting privileges, see
Recipe 23.2
.
9.1. Creating Compound-Statement Objects
Problem
You want to define a stored program, but its body contains instances of the
;
statement
terminator. The
mysql
client program uses the same terminator by default, so
mysql
misinterprets the definition and produces an error.
Solution
Redefine the
mysql
statement terminator with the
delimiter
command.
Discussion
Each stored program is an object with a body that must be a single SQL statement.
However, these objects often perform complex operations that require several state‐
ments. To handle this, 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 mul‐
tiple statements, each terminated by a
;
character. The
BEGIN
…
END
block can contain
statements such as
SELECT
or
INSERT
, but compound statements also permit conditional
statements such as
IF
or
CASE
, looping constructs such as
WHILE
or
REPEAT
, or other
BEGIN
…
END
blocks.
Compound-statement syntax provides flexibility, but if you define compound-
statement objects within the
mysql
client, you quickly encounter a problem: each state‐
ment within a compound statement must be terminated by a
;
character, but
mysql
itself
interprets
;
to figure out where statements end 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. To handle this, tell
mysql
to recognize
a different statement delimiter so that it ignores
;
characters within the object body.
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.
The following example uses a stored function to illustrate how to change the delimiter,
but the principles apply to defining any type of stored program.
