This chapter discusses the following kinds of database objects:
A stored function performs a calculation and returns a value
that can be used in expressions just like a built-in function such
as RAND()
, NOW()
, or LEFT()
. A stored procedure
performs calculations for which no return value is needed.
Procedures are not used in expressions, they are invoked with the
CALL
statement. A procedure
might be executed to update rows in a table or produce a result
set that is sent to the client program. One reason for using a
stored routine is that it encapsulates the code for performing a
calculation. This enables you to perform the calculation easily by
invoking the routine rather than by repeating all its code each
time.
A trigger is an object that is defined to activate when a
table is modified. Triggers are available for INSERT
, UPDATE
, and DELETE
statements. For example, you can
check values before they are inserted into a table, or specify
that any row deleted from a table should be logged to another
table that serves as a journal of data changes. Triggers are
useful for automating these actions so that you don’t need to
remember to do them yourself each time you modify a table.
An event is an object that executes SQL statements at a scheduled time or times. You can think of an event as something like a Unix cron job, but that runs within MySQL. For example, events can help you perform administrative tasks such as deleting old table records periodically or creating nightly summaries.
Stored routines and triggers are supported as of MySQL 5.0. Event support begins with MySQL 5.1.
These different kinds of objects have in common the property that
they are user-defined but stored on the server side for later execution.
This differs from sending an SQL statement from the client to the server
for immediate execution. Each of these objects also has the property
that it is defined in terms of other SQL statements to be executed when
the object is invoked. The object has a body that is a single SQL
statement, but that statement can use compound-statement
syntax (a BEGIN
... END
block) that contains multiple statements.
This means that the body can range from very simple to extremely
complex. The following stored procedure is a trivial routine that does
nothing but set a user-defined variable and for which the body consists
of a single SET
statement:
CREATE PROCEDURE get_time() SET @current_time = CURTIME();
For more complex operations, a compound statement is necessary:
CREATE PROCEDURE part_of_day() BEGIN CALL get_time(); IF @current_time < '12:00:00' THEN SET @day_part = 'morning'; ELSEIF @current_time = '12:00:00' THEN SET @day_part = 'noon'; ELSE SET @day_part = 'afternoon or night'; END IF; END;
Here, the BEGIN
... END
block contains multiple statements, but is
itself considered to constitute a single statement. Compound statements
enable you to declare local variables and to use conditional logic and
looping constructs. Note also that one stored procedure can invoke
another: part_of_day()
calls
get_time()
. These capabilities
provide you with considerably more flexibility for algorithmic
expression than you have when you write inline expressions in
noncompound statements such as SELECT
or UPDATE
.
The statements within a compound statement must each be terminated
by a ;
character. That requirement
causes a problem if you use the mysql
client to define an object that uses compound statements because
mysql itself interprets ;
to determine statement boundaries. The
solution to this problem is to redefine mysql’s statement delimiter while you’re
defining a compound-statement object. Creating Compound-Statement Objects covers how to do
this; make sure that you read that recipe before proceeding to those
that follow it.
Due to space limitations, this chapter illustrates by example but does not otherwise go into much detail about the extensive syntax for stored routines, triggers, and events. For complete syntax descriptions, see the MySQL Reference Manual.
The scripts for the examples shown in this chapter can be found in
the routines, triggers, and events directories of the recipes
distribution. Scripts to create some
of the tables are in the tables
directory.
In addition to the stored routines shown in this chapter, others can be found elsewhere in this book. See, for example, Recipes , , and .
3.16.66.156