Using a Stored Procedure to “Return” Multiple Values
Problem
You want to perform an operation that produces two or more values, but a stored function can return only a single value.
Solution
Use a stored procedure that has
OUT
or INOUT
parameters, and pass user-defined
variables for those parameters when you invoke the procedure. A
procedure does not “return” a value the way a function
does, but it can assign values to those parameters, which will be the
values of the variables when the procedure returns.
Discussion
Unlike stored function parameters, which are input values only, a stored procedure parameter can be any of three types:
An
IN
parameter is for input only. This is the default parameter type if you specify no type.An
INOUT
parameter is used to pass a value in, and it can also be used to pass a value back out.An
OUT
parameter is used to pass a value out.
This means that if you need to produce multiple values from an
operation, you can use INOUT
or
OUT
parameters. The following
example illustrates this, using an IN
parameter for input, and passing back
three values via OUT
parameters.
Creating Compound-Statement Objects
showed an avg_mail_size()
function that returns the average mail message size for a given sender. The function returns a single value. If you want additional information, such as the number of messages and total message size, a function will not work. You could write three separate functions, but it’s also possible to use a single procedure that retrieves multiple ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.