18.4. Defining Stored Routines

Stored routine definitions can use compound statements. That is, a definition can be given as a BEGIN/END block that contains multiple statements. Each statement within a block must be terminated by a semicolon character (';').

If you are defining a stored routine from within a programming interface that does not use the semicolon as a statement terminator, semicolons within stored routine definitions do not present any special issues. However, if you are using the mysql client program, semicolons are ambiguous within routine definitions because mysql itself treats semicolon as a statement terminator. To handle this issue, mysql supports a delimiter command that enables you to change its statement terminator temporarily while you define a stored routine. By using delimiter, you can cause mysql to pass semicolons to the server along with the rest of the routine definition. The following example shows how to define a procedure named world_record_count() within mysql by redefining the terminator as the // sequence:

 mysql> delimiter // mysql> CREATE PROCEDURE world_record_count ()     -> BEGIN     ->   SELECT 'Country', COUNT(*) FROM Country;     ->   SELECT 'City', COUNT(*) FROM City;     ->   SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage;     -> END;     -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; 

This study guide generally uses // in examples that redefine the statement delimiter, but the choice of delimiter is up to you. Be sure to choose a delimiter that does not contain characters that occur within the definition of the stored routine that you are creating. For example, ')' is a poor choice because it occurs at least once within every stored procedure or function definition.

The delimiter command is also useful for creating triggers. (See Chapter 19, "Triggers.") A trigger definition contains a trigger action statement, and the statement can be a compound-statement block that includes multiple statements of its own.