MySQL Cookbook, 2nd Edition

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