Database Reference
In-Depth Information
Recipe 9.1
shows an
avg_mail_size()
function that returns the average mail message
size for a given sender. The function returns a single value. To produce additional in‐
formation, such as the number of messages and total message size, a function will not
work. You could write three separate functions, but that is cumbersome. Instead, use a
single procedure that retrieves multiple values about a given mail sender. The following
procedure,
mail_sender_stats()
, runs a query on the
mail
table to retrieve mail-
sending statistics about a given username, which is the input value. The procedure
determines how many messages that user sent, and the total and average sizes of the
messages in bytes, which it returns through three
OUT
parameters:
CREATE
PROCEDURE
mail_sender_stats
(
IN
user
VARCHAR
(
8
),
OUT
messages
INT
,
OUT
total_size
INT
,
OUT
avg_size
INT
)
BEGIN
#
Use
IFNULL
()
to
return
0
for
SUM
()
and
AVG
()
in
case
there
are
#
no
rows
for
the
user
(
those
functions
return
NULL
in
that
case
).
SELECT
COUNT
(
*
),
IFNULL
(
SUM
(
size
),
0
),
IFNULL
(
AVG
(
size
),
0
)
INTO
messages
,
total_size
,
avg_size
FROM
mail
WHERE
srcuser
=
user
;
END
;
To use the procedure, pass a string containing the username, and three user-defined
variables to receive the
OUT
values. After the procedure returns, access the variable val‐
ues:
mysql>
CALL mail_sender_stats('barb',@messages,@total_size,@avg_size);
mysql>
SELECT @messages, @total_size, @avg_size;
+-----------+-------------+-----------+
| @messages | @total_size | @avg_size |
+-----------+-------------+-----------+
| 3 | 156696 | 52232 |
+-----------+-------------+-----------+
This routine passes back calculation results. It's also common to use
OUT
parameters for
diagnostic purposes such as status or error indicators.
If you call
mail_sender_stats()
from within a stored program, you can pass variables
to it using routine parameters or program local variables, not just user-defined variables.
9.4. Using Triggers to Implement Dynamic Default Column
Values
Problem
A table contains a column for which the initial value is not constant, but in most cases,
MySQL permits only constant default values.
