Recipe 16.3. Using a Stored Procedure to Return Multiple Values


Recipe 16.3. 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.

Section 16.1 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 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 size 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 FLOAT,                                    OUT avg_size FLOAT) 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, check the variable values:

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     | +-----------+-------------+-----------+ 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net