Database Reference
In-Depth Information
In this topic, the term “stored routine” refers collectively to stored
functions and procedures, and “stored program” refers collectively to
stored routines, triggers, and events.
Stored programs are database objects that 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 object also has the property that it is defined in
terms of other SQL statements to be executed when the object is invoked. The object
body is a single SQL statement, but that statement can use compound-statement syntax
(a
BEGIN
…
END
block) that contains multiple statements. Thus, the body can range from
very simple to extremely complex. The following stored procedure is a trivial routine
that does nothing but display the current MySQL version, using a body that consists of
a single
SELECT
statement:
CREATE
PROCEDURE
show_version
()
SELECT
VERSION
()
AS
'MySQL Version'
;
More complex operations use a
BEGIN
…
END
compound statement:
CREATE
PROCEDURE
show_part_of_day
()
BEGIN
DECLARE
cur_time
,
day_part
TEXT
;
SET
cur_time
=
CURTIME
();
IF
cur_time
<
'12:00:00'
THEN
SET
day_part
=
'morning'
;
ELSEIF
cur_time
=
'12:00:00'
THEN
SET
day_part
=
'noon'
;
ELSE
SET
day_part
=
'afternoon or night'
;
END
IF
;
SELECT
cur_time
,
day_part
;
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 vari‐
ables and to use conditional logic and looping constructs. These capabilities provide
considerably more flexibility for algorithmic expression than when you write inline
expressions in noncompound statements such as
SELECT
or
UPDATE
.
Each statement within a compound statement must 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 bound‐
aries. The solution is to redefine
mysql
's statement delimiter while you define a
compound-statement object.
Recipe 9.1
covers how to do this; be sure to read that recipe
before proceeding to those that follow it.
